Can not add user preference -db error null value in column "created_at"

[MeikelSan]

Tandoor Version

1.0.4.1

Setup

Docker / Docker-Compose

Reverse Proxy

Traefik

Other

No response

Bug description

Hi team,

first of all a big bunch of thanks for your excellent app and for sharing it with us.

I am using Tandoor with Postgres database and Traefik in a home environment on Ubuntu 20.4 server with Docker and wanted to add my household member to the default space today. Unfortunately does the normal way with opening the invitation link, supplying a username, email address and password not work, because I can't find a token and the last part of the invitation link isn't accepted as token :-(

After some research here somebody mentioned the he had to add the user preference manually, because it wasn't created during the user add. So I went into the Django administration and tried to add the user preference manually. After filling out all needed fields and klicking on "Save" the browser returned a server error 500. In the db log Ive got these error messages: db_recipes_1 | 2022-01-23 16:10:37.155 UTC [51] ERROR: null value in column "created_at" violates not-null constraint db_recipes_1 | 2022-01-23 16:10:37.155 UTC [51] DETAIL: Failing row contains (TANDOOR, 3, PRIMARY, g, SEARCH, NEW, t, 2, t, 5, f, t, 1, null, f). db_recipes_1 | 2022-01-23 16:10:37.155 UTC [51] STATEMENT: UPDATE "cookbook_userpreference" SET "theme" = 'TANDOOR', "nav_color" = 'PRIMARY', "default_unit" = 'g', "use_fractions" = false, "use_kj" = false, "default_page" = 'SEARCH', "search_style" = 'NEW', "show_recent" = true, "ingredient_decimals" = 2, "comments" = true, "shopping_auto_sync" = 5, "sticky_navbar" = true, "created_at" = NULL, "space_id" = 1 WHERE "cookbook_userpreference"."user_id" = 3 web_recipes_1 | 172.26.0.4 - - [23/Jan/2022:17:10:37 +0100] "POST /recipes/admin/cookbook/userpreference/add/ HTTP/1.0" 500 145 "http://192.168.127.247/recipes/admin/cookbook/userpreference/add/" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.71 Safari/537.36"

Here's my docker-compose.yml

version: "3" services: db_recipes: restart: always image: postgres:11-alpine volumes:

• ./postgresql:/var/lib/postgresql/data env_file:
• ./.env networks:

• default

  web_recipes: image: vabene1111/recipes restart: always env_file:

• ./.env volumes:
• staticfiles:/opt/recipes/staticfiles
• nginx_config:/opt/recipes/nginx/conf.d
• ./mediafiles:/opt/recipes/mediafiles depends_on:
• db_recipes networks:

• default

  nginx_recipes: image: nginx:mainline-alpine restart: always ports:

• 80:80 env_file:
• ./.env volumes:
• nginx_config:/etc/nginx/conf.d:ro
• staticfiles:/static

• ./mediafiles:/media

  labels: # traefik labels

• "traefik.enable=true"
• "traefik.http.routers.recipes.rule=Host(klara.hesse-lg.net)" # hostname to listen
• "traefik.http.routers.recipes.entrypoints=websecure" # https endpoint
• "traefik.http.routers.recipes.tls=true"

• "traefik.http.middlewares.recipes.redirectscheme.scheme=https"

  depends_on:

• web_recipes networks:
• default
• traefik

networks: default: traefik: # External traefik network external: true

volumes: nginx_config: staticfiles:

and my .env file:

`

DEBUG=0 SQL_DEBUG=0

ALLOWED_HOSTS=*

SECRET_KEY=****

TIMEZONE=Europe/Berlin

DB_ENGINE=django.db.backends.postgresql POSTGRES_HOST=db_recipes POSTGRES_PORT=5432 POSTGRES_USER=djangouser POSTGRES_PASSWORD=*** POSTGRES_DB=djangodb

FRACTION_PREF_DEFAULT=0

COMMENT_PREF_DEFAULT=1

SHOPPING_MIN_AUTOSYNC_INTERVAL=5

GUNICORN_MEDIA=0

REVERSE_PROXY_AUTH=0

ENABLE_SIGNUP=1

`

The error messages with SQL_DEBUG=1 and DEBUG=1 are in the relevant logs section.

Thanks a lot for having a look at my problem!

Relevant logs

IntegrityError at /admin/cookbook/userpreference/add/
null value in column "created_at" violates not-null constraint
DETAIL:  Failing row contains (TANDOOR, 4, PRIMARY, g, SEARCH, NEW, t, 2, t, 5, f, t, 1, null, f).
Request Method: POST
Request URL:    http://192.168.127.247/recipes/admin/cookbook/userpreference/add/
Django Version: 3.2.10
Exception Type: IntegrityError
Exception Value:    
null value in column "created_at" violates not-null constraint
DETAIL:  Failing row contains (TANDOOR, 4, PRIMARY, g, SEARCH, NEW, t, 2, t, 5, f, t, 1, null, f).
Exception Location: /opt/recipes/venv/lib/python3.9/site-packages/django/db/backends/utils.py, line 84, in _execute
Python Executable:  /opt/recipes/venv/bin/python
Python Version: 3.9.5
Python Path:    
['/opt/recipes',
 '/opt/recipes/venv/bin',
 '/usr/local/lib/python39.zip',
 '/usr/local/lib/python3.9',
 '/usr/local/lib/python3.9/lib-dynload',
 '/opt/recipes/venv/lib/python3.9/site-packages']
Server time:    Sun, 23 Jan 2022 18:14:00 +0100

TRACEBACK:
Environment:

Request Method: POST
Request URL: http://192.168.127.247/recipes/admin/cookbook/userpreference/add/

Django Version: 3.2.10
Python Version: 3.9.5
Installed Applications:
['dal',
 'dal_select2',
 'django.contrib.admin',
 'django.contrib.auth',
 'django.contrib.contenttypes',
 'django.contrib.sessions',
 'django.contrib.messages',
 'django.contrib.sites',
 'django.contrib.staticfiles',
 'django.contrib.postgres',
 'django_prometheus',
 'django_tables2',
 'corsheaders',
 'django_filters',
 'crispy_forms',
 'rest_framework',
 'rest_framework.authtoken',
 'django_cleanup.apps.CleanupConfig',
 'webpack_loader',
 'django_js_reverse',
 'hcaptcha',
 'allauth',
 'allauth.account',
 'allauth.socialaccount',
 'cookbook.apps.CookbookConfig',
 'treebeard']
Installed Middleware:
['corsheaders.middleware.CorsMiddleware',
 'django.middleware.security.SecurityMiddleware',
 'whitenoise.middleware.WhiteNoiseMiddleware',
 'django.contrib.sessions.middleware.SessionMiddleware',
 'django.middleware.common.CommonMiddleware',
 'django.middleware.csrf.CsrfViewMiddleware',
 'django.contrib.auth.middleware.AuthenticationMiddleware',
 'django.contrib.messages.middleware.MessageMiddleware',
 'django.middleware.locale.LocaleMiddleware',
 'django.middleware.clickjacking.XFrameOptionsMiddleware',
 'cookbook.helper.scope_middleware.ScopeMiddleware',
 'recipes.middleware.SqlPrintingMiddleware']

Traceback (most recent call last):
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)

The above exception (null value in column "created_at" violates not-null constraint
DETAIL:  Failing row contains (TANDOOR, 4, PRIMARY, g, SEARCH, NEW, t, 2, t, 5, f, t, 1, null, f).
) was the direct cause of the following exception:
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/core/handlers/exception.py", line 47, in inner
    response = get_response(request)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/core/handlers/base.py", line 181, in _get_response
    response = wrapped_callback(request, *callback_args, **callback_kwargs)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/contrib/admin/options.py", line 616, in wrapper
    return self.admin_site.admin_view(view)(*args, **kwargs)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/utils/decorators.py", line 130, in _wrapped_view
    response = view_func(request, *args, **kwargs)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/views/decorators/cache.py", line 44, in _wrapped_view_func
    response = view_func(request, *args, **kwargs)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/contrib/admin/sites.py", line 232, in inner
    return view(request, *args, **kwargs)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/contrib/admin/options.py", line 1657, in add_view
    return self.changeform_view(request, None, form_url, extra_context)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/utils/decorators.py", line 43, in _wrapper
    return bound_method(*args, **kwargs)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/utils/decorators.py", line 130, in _wrapped_view
    response = view_func(request, *args, **kwargs)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/contrib/admin/options.py", line 1540, in changeform_view
    return self._changeform_view(request, object_id, form_url, extra_context)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/contrib/admin/options.py", line 1586, in _changeform_view
    self.save_model(request, new_object, form, not add)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/contrib/admin/options.py", line 1099, in save_model
    obj.save()
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/models/base.py", line 739, in save
    self.save_base(using=using, force_insert=force_insert,
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/models/base.py", line 776, in save_base
    updated = self._save_table(
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/models/base.py", line 858, in _save_table
    updated = self._do_update(base_qs, using, pk_val, values, update_fields,
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/models/base.py", line 912, in _do_update
    return filtered._update(values) > 0
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/models/query.py", line 802, in _update
    return query.get_compiler(self.db).execute_sql(CURSOR)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1559, in execute_sql
    cursor = super().execute_sql(result_type)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/models/sql/compiler.py", line 1175, in execute_sql
    cursor.execute(sql, params)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 98, in execute
    return super().execute(sql, params)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 66, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._execute)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 75, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/utils.py", line 90, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/recipes/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 84, in _execute
    return self.cursor.execute(sql, params)

Exception Type: IntegrityError at /admin/cookbook/userpreference/add/
Exception Value: null value in column "created_at" violates not-null constraint
DETAIL:  Failing row contains (TANDOOR, 4, PRIMARY, g, SEARCH, NEW, t, 2, t, 5, f, t, 1, null, f).

[smilerz]

I can't follow what you are doing. Does creating a user in the 'Space' form work, it just doesn't send an invite link?

We should fix whatever problem you are having with the user invite instead of trying to make a workaround function.

[MeikelSan]

Hi smilerz,

I strongly agree ;-) So the only thing missing is the token to allow to connect to the existing space "Default". Where can i get this token?

[smilerz]

I don't understand the question - the link is automatically generated for you.

[MeikelSan]

Hi Smilerz,

exactly that is the problem. I try to describe a little more detailed: klicked on the link and got the user registration form

then this form was presented

Btw. the system is not able to send E-Mails external, so i copied the invitation link from my invitations in the system side

The useraccount had been createt, but no user preference entry. What is wrong with this ?

Thanks Meikel

[smilerz]

Those links look well formatted to me. What happens when you follow the link? (Make sure it’s from a browser that isn’t logged in)

[MeikelSan]

Hi, exactly what i wrote:

• Account creation
• form to supply the token

and I used another browser for the user registration, freshly started and with on former login data in the cache.

[smilerz]

What do you mean 'form to supply the token'? If you follow the link you should see a screen like this:

[MeikelSan]

Hi smilerz,

as stated in my last comment, I've got the registration form you mentioned. But after clicking on "Create User" the system presented me the form, where i have to supply a token to be linked to the Default space.....

sorry for the german text, I can try to supply screenshots in english tomorrow.

/Meikel

[smilerz]

you shouldn’t get that screen. Are you serving the website in a subfolder?

can you share your env?

[MeikelSan]

Nope, no subfolder

Can you specify "share your env" ? The .env file is in the first post. What information do you exactly need to know? I am new to docker, but not to linux.

/Meikel

[smilerz]

Nope, no subfolder

Can you specify "share your env" ? The .env file is in the first post. What information do you exactly need to know? I am new to docker, but not to linux.

/Meikel

I missed the ENV sorry.

Can you turn signup off and try again?

[MeikelSan]

Hi Smilerz,

turned off the signup via ENABLE_SIGNUP=0 in the .env file, but unfortunately exactly the same behavior :-(

I've also tried to create an own space for the new user. Generally this was successful, but no user preference entry is shown in the django admin, even after logging in with the new user and setting some preferences on the website. So there's no way to change the space....

[smilerz]

According to this screen shot you have the app served at a subfolder of your domain (/recipes).

To make it work that way you have to set SCRIPT_NAME in the .env as well as set the X-Script-Name header in your proxy.

[MeikelSan]

Hi,

first of all : I really appreciate your time and perseverance and am grateful for your support.

Is this not the default setting?!?!? I did not change any webserver directories with intention. And the URL i use to connect to Tandoor is the base URL /, e.g. https://host.domain.tld/ which will be automatically redirected to "https://host.domain.tld/recipes/accounts/login/?next=/recipes/search/"

When I enable the SCRIPT_NAME parameter in the .env file and add these lines to the docker-compose.yml

- "traefik.http.middlewares.recipes.redirectscheme.scheme=https" - "traefik.http.middlewares.recipes-header.headers.customrequestheaders.X-Script-Name=/recipes/" - "traefik.http.routers.recipes.middlewares=recipes, recipes-header"

I'll get Error 502 messages when I try to access the login page. Here's the respective output from the nginx log:

2022/01/24 16:52:26 [error] 24#24: *1 connect() failed (111: Connection refused) while connecting to upstream, client: 172.19.0.2, server: localhost, request: "GET / HTTP/1.1", upstream: "http://172.31.0.3:8080/", host: "host.domain.tld" 172.19.0.2 - - [24/Jan/2022:16:52:26 +0000] "GET / HTTP/1.1" 502 157 "-" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/605.1.15 (KHTML, like Gecko) Version/15.2 Safari/605.1.15" "192.168.xxx.yyy"

What am I missing?

Based on your post I decided to make a short test: copied the invitation link, removed the /recipes/ string from the link and pasted it in another browsers window. With this link (https://host.domain.tld/invite/690f....) i've got the "Create Account" page. So far, so good, but after filling out all fields the Marmot page appeared again: "Please paste the invitation token to access a existing space..."

I also tested that with bypassing the proxy - same result :-(

/Meikel

[smilerz]

I'm not familiar with traefik - the default path does not include 'recipes' so I'm just assuming it is happening there. But something is redirecting to a different path.

I'm also not 100% certain how to setup traefik & nginx at a subfolder. This issue seems to have a working configuration.

Finally, and most importantly, there is a known bug with invitations and subfolder configurations. There is a fix and will be pushed at the next release.

edit to add: looks like new release is coming out today.

[MeikelSan]

Hi,

thanks, I'll try the settings from the issue mentioned tomorrow. And a fix is welcome at any time 😄

/Meikel

[smilerz]

can you please try again with 1.0.5.2

[MeikelSan]

Hi,

sorry but the same errors again. Getting the same pages as before. When i set the SCRIPT_NAME in the .env file I get Server 500 Errors. Will do some troubleshooting tomorrow and report back.

[MeikelSan]

Hi smilerz,

sorry for delay, have had an hard day.

I did some debug, please see the attached files, thanks a lot.

HTTP access without SCRIPT_NAME.txt HTTPS access without SCRIPT_NAME.txt HTTPS access with SCRIPT_NAME.txt HTTP access with SCRIPT_NAME.txt

[smilerz]

you will need to either also configure the nginx config to set the script name header or make traefik stop forwarding to a subfolder. I recommend the latter.

[MeikelSan]

Hi Smilerz,

sorry for delay, I had a full blasted week at work.... But luckily today I've found the reason, why the pages were redirected to the recipes/ subfolder 😄

In the nginx config file Recipes.conf I found these entries: ... proxy_set_header X-Script-Name /recipes; proxy_cookie_path / /recipes;

and commented them out. I don't know why they were set and I am really sure that I did not deliberately wrote them into the file.

Anyway, all is functioning now and I am thankful for your dedication and time.