[BUG] - Database migration (unique constraints) fails with foreign key violation about mealie

[hbh7]

I dared haha, and I'm in! Made the admin change, found the actual admin account (whoops lol) and sorted that all out. Now the backup works properly! I can see about sending it over tomorrow if you'd like for a test suite. I guess that means the only issues then are that the create backup result message shouldn't claim success when it definitely wasn't successful, and those pages probably shouldn't let you access them at all if admin isn't true.

Thanks everyone!

[boc-the-git]

If you're comfortable with an API (removed that as you don't really need to have API knowledge to do this), head to baseurl/docs, 'Authorize' in the top right, then about the 10th API down the list, /api/users/self, should be useful.

[michael-genson]

No worries, glad it's otherwise working for you!

[michael-genson]

Can you try restoring a backup on the newest nightly version? We released a bunch of migration-related issues in the newest nightly which might fix the problem

If that doesn't work, can you post your backup?

[hbh7]

Somehow I only discovered the built-in backup recently (also, it does not work in this condition, returning 403 Forbidden on api/admin/backups, although the UI pops up a green message saying backup success) and don't have an export from there, but I do have a copy of the data and db folders from Dec 6th, presumably from the upgrade to RC1(.1?) or RC2.

To test, I renamed the current folders, restored the backed up folders, and started up with v1.0.0-RC1.1. This lead to

mealie    | sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "ingredient_foods" violates foreign key constraint "recipes_ingredients_food_id_fkey" on table "recipes_ingredients"
mealie    | DETAIL:  Key (id)=(71ca6261-91f4-42ab-bc83-f3f00781e3ef) is still referenced from table "recipes_ingredients".
mealie    |
mealie    | [SQL:
mealie    |         DELETE FROM ingredient_foods
mealie    |         WHERE EXISTS (
mealie    |             SELECT 1 FROM ingredient_foods t2
mealie    |             WHERE ingredient_foods.name = t2.name
mealie    |             AND ingredient_foods.group_id = t2.group_id
mealie    |             AND ingredient_foods.CTID > t2.CTID
mealie    |         )
mealie    |         ]

I think that means I must have updated from a version prior to RC1.1 and never noticed it didn't work properly as I probably only quickly tested that the recipes list showed up, which it did and therefore seemed good, oops. Then December was busy and I didn't use it or notice until I tried using it again. I think I also tried updating this data/db copy to RC2 and nightly, but neither helped.

That made me think I must have screwed up the jump to RC1.1. My memory is fuzzy on how my setup was then but I thought I came from v1.0.0-beta5, so I tried rebuilding that using https://web.archive.org/web/20230606094044/https://nightly.mealie.io/documentation/getting-started/installation/postgres/.
Turns out it wasn't that version, that threw alembic.util.exc.CommandError: Can't locate revision identified by **'bcfdad6b7355', which lead me to realizing I was on frontend-nightly and api-nightly based on having those images in my docker image cache still. One more wipe and reset the folders to that image and now we're back up and running, horray.

So I guess that means I must have misinterpreted the upgrade instructions, and assumed that I didn't need a DB export and reimport for the switch to the unified container setup with the ghcr repo since I was kinda already on v1...

So that means I'm on the images that were built on 2023-08-31T17:19:31.385119761Z (api-nightly) and 2023-08-31T17:20:07.576989814Z (frontend-nightly). Is my upgrade path here to backup through the web UI and then restore that backup on a freshly set up RC2? I'm guessing no because the backup page still throws the same 403 forbidden error (dev tools only, no more UI popup), but maybe I need an intermediary version?

Please advise, thanks! :)

[michael-genson]

returning 403 Forbidden on api/admin/backups, although the UI pops up a green message saying backup success

This is normal, the 403 error is saying your old login failed (which makes sense, since it got replaced with the backup restore). I would try logging in after doing that

However your error log isn't normal. Can you try upgrading to nightly and see if it's fixed? Your upgrade path to RC1.1 was correct, there were some bugs we squashed in the latest nightly release

i.e. try image hcr.io/mealie-recipes/mealie:nightly (instead of RC1.1) and see if that fixes your issue

[hbh7]

Thanks for your response. Unfortunately in my setup, the login credentials are the same across all tests, and no amount of logging out and back in, cache/site data clearing, directly connecting without the reverse proxy, or using a different browser seems to matter; the create backup button on the /admin/backups page always leads to a 403 in the dev tools, and no backups created. There's no useful logs I can find related to it either, just a single line indicating the 403 happened. Is there a debug logging mode I can enable?

Regardless of that issue, I tried bringing the working setup's data/db back to the modern setup on the newest nightly via swapping out the compose file again, but unfortunately the error persists.

mealie    | WARNING:  database "mealie" has a collation version mismatch
mealie    | DETAIL:  The database was created using collation version 2.31, but the operating system provides version 2.36.
mealie    | HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE mealie REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
mealie    | INFO: 06-Jan-24 12:20:37    Database connection established.
mealie    | WARNING:  database "mealie" has a collation version mismatch
mealie    | DETAIL:  The database was created using collation version 2.31, but the operating system provides version 2.36.
mealie    | HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE mealie REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
mealie    | INFO: 06-Jan-24 12:20:37    Context impl PostgresqlImpl.
mealie    | INFO: 06-Jan-24 12:20:37    Will assume transactional DDL.
mealie    | INFO: 06-Jan-24 12:20:37    Migration needed. Performing migration...
mealie    | WARNING:  database "mealie" has a collation version mismatch
mealie    | DETAIL:  The database was created using collation version 2.31, but the operating system provides version 2.36.
mealie    | HINT:  Rebuild all objects in this database that use the default collation and run ALTER DATABASE mealie REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
mealie    | INFO: 06-Jan-24 12:20:37    Context impl PostgresqlImpl.
mealie    | INFO: 06-Jan-24 12:20:37    Will assume transactional DDL.
mealie    | INFO: 06-Jan-24 12:20:37    Running upgrade bcfdad6b7355 -> 0341b154f79a, added normalized unit and food names
mealie    | INFO: 06-Jan-24 12:20:38    Running upgrade 0341b154f79a -> dded3119c1fe, added unique constraints
mealie    | Traceback (most recent call last):
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
mealie    |     self.dialect.do_execute(
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
mealie    |     cursor.execute(statement, parameters)
mealie    | psycopg2.errors.ForeignKeyViolation: update or delete on table "ingredient_foods" violates foreign key constraint "recipes_ingredients_food_id_fkey" on table "recipes_ingredients"
mealie    | DETAIL:  Key (id)=(c83baf65-88b4-4ed2-85f1-7a13944d22a2) is still referenced from table "recipes_ingredients".
mealie    |
mealie    |
mealie    | The above exception was the direct cause of the following exception:
mealie    |
mealie    | Traceback (most recent call last):
mealie    |   File "/app/mealie/db/init_db.py", line 113, in <module>
mealie    |     main()
mealie    |   File "/app/mealie/db/init_db.py", line 95, in main
mealie    |     command.upgrade(alembic_cfg, "head")
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/command.py", line 382, in upgrade
mealie    |     script.run_env()
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/script/base.py", line 578, in run_env
mealie    |     util.load_python_file(self.dir, "env.py")
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
mealie    |     module = load_module_py(module_id, path)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 109, in load_module_py
mealie    |     spec.loader.exec_module(module)  # type: ignore
mealie    |   File "<frozen importlib._bootstrap_external>", line 883, in exec_module
mealie    |   File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
mealie    |   File "/app/alembic/env.py", line 77, in <module>
mealie    |     run_migrations_online()
mealie    |   File "/app/alembic/env.py", line 71, in run_migrations_online
mealie    |     context.run_migrations()
mealie    |   File "<string>", line 8, in run_migrations
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/runtime/environment.py", line 922, in run_migrations
mealie    |     self.get_context().run_migrations(**kw)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/runtime/migration.py", line 624, in run_migrations
mealie    |     step.migration_fn(**kw)
mealie    |   File "/app/alembic/versions/2023-10-04-14.29.26_dded3119c1fe_added_unique_constraints.py", line 154, in upgrade
mealie    |     _resolve_duplicate_foods_units_labels(session)
mealie    |   File "/app/alembic/versions/2023-10-04-14.29.26_dded3119c1fe_added_unique_constraints.py", line 120, in _resolve_duplicate_foods_units_labels
mealie    |     resolve_func(session, keep_id, dupe_id)
mealie    |   File "/app/alembic/versions/2023-10-04-14.29.26_dded3119c1fe_added_unique_constraints.py", line 72, in _resolve_duplicate_food
mealie    |     session.execute(
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2246, in execute
mealie    |     return self._execute_internal(
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2150, in _execute_internal
mealie    |     result = conn.execute(
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1412, in execute
mealie    |     return meth(
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 483, in _execute_on_connection
mealie    |     return connection._execute_clauseelement(
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1635, in _execute_clauseelement
mealie    |     ret = self._execute_context(
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1844, in _execute_context
mealie    |     return self._exec_single_context(
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1984, in _exec_single_context
mealie    |     self._handle_dbapi_exception(
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2339, in _handle_dbapi_exception
mealie    |     raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1965, in _exec_single_context
mealie    |     self.dialect.do_execute(
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 921, in do_execute
mealie    |     cursor.execute(statement, parameters)
mealie    | sqlalchemy.exc.IntegrityError: (psycopg2.errors.ForeignKeyViolation) update or delete on table "ingredient_foods" violates foreign key constraint "recipes_ingredients_food_id_fkey" on table "recipes_ingredients"
mealie    | DETAIL:  Key (id)=(c83baf65-88b4-4ed2-85f1-7a13944d22a2) is still referenced from table "recipes_ingredients".
mealie    |
mealie    | [SQL: DELETE FROM ingredient_foods WHERE id=%(id)s::UUID]
mealie    | [parameters: {'id': UUID('c83baf65-88b4-4ed2-85f1-7a13944d22a2')}]
mealie    | (Background on this error at: https://sqlalche.me/e/20/gkpj)
mealie    | INFO:     Uvicorn running on http://0.0.0.0:9000 (Press CTRL+C to quit)
mealie    | INFO:     Started parent process [15]
mealie    | INFO:     Started server process [17]
mealie    | INFO:     Waiting for application startup.
mealie    | INFO: 06-Jan-24 12:20:46    -----SYSTEM STARTUP-----
mealie    |
mealie    | INFO: 06-Jan-24 12:20:46    ------APP SETTINGS------
...

I've been assuming the warning about the collation mismatch is unimportant, would you agree? I did try fixing it by doing the rebuild as it asked (back when I was trying to debug the broken setup before changing anything back to the old versions), but it didn't make a difference.

Any other ideas? I'm guessing that no amount of newer changes is going to help me get past this migration, since presumably any new changes would be in new and subsequent migrations, but I'm not familiar with this migration setup so idk. Maybe I'd have to modify the migration in place to fix it? Maybe it's better to get the backup working? ¯\(ツ)/¯

Thanks!

[michael-genson]

I've been assuming the warning about the collation mismatch is unimportant, would you agree?

It may be an issue, but likely not directly related to this one

Are you able to provide a backup from your currently-working version?

[michael-genson]

PR #2923 might fix your problem, but I'm not sure without testing your data. You're also welcome to try pulling my branch and checking yourself (or just waiting until it's merged into nightly)

[hbh7]

Hey, thanks for the update! I checked out and built the docker image from your PR and tried it out on a fresh folder backup copy, and it started up perfectly and appears to work normally again with no more errors (I'll apply the collation fix again later)! I'll run this until a little bit after the merge and report back if anything breaks.

As for the backup, I'd be happy to provide but I'd want to double check the contents first, just in case (idk if there's anything sensitive). I also still get the 403 error on the latest version, so I wouldn't be able to provide the contents of a backup generated by mealie until that's fixed. I tried the same troubleshooting, nothing different there. Should I open a separate issue and we continue trying to see what's up there?

[michael-genson]

Glad it seems to work! Just to confirm, you're getting that 403 error when you try to create a backup even on my branch? Do you have logs for that?

I'm fine with leaving this issue open if you're still seeing problems, even if not directly related to the fk issue

[hbh7]

Yep, even on your branch. I used the gh cli to check out the PR and built the image using the compose file, then swapped the tag in my regular compose file. Solved the migration issue, and I just fixed the collation one so now the logs are clean of anything indicating any sort of an issue.

This is what's in the log when I open the backup page and click the create backup button:

mealie    | INFO:     10.20.30.91:3943 - "GET /admin/backups/ HTTP/1.1" 304 Not Modified
mealie    | INFO:     10.20.30.91:3943 - "GET /api/users/self HTTP/1.1" 200 OK
mealie    | INFO:     10.20.30.91:3943 - "GET /api/admin/backups HTTP/1.1" 403 Forbidden
mealie    | INFO:     10.20.30.91:3967 - "POST /api/admin/backups HTTP/1.1" 403 Forbidden
mealie    | INFO:     10.20.30.91:3967 - "GET /api/admin/backups HTTP/1.1" 403 Forbidden

Anything else I can do to find out more info?

[michael-genson]

Maybe a dumb question, but does your user account have admin privileges?

[hbh7]

No worries lol. To the best of my knowledge, yes. I suppose it's not impossible I'm not though. Can you toss me a SQL command or something to find out somewhere? The admin users page has the same error 👀
mealie | INFO: 10.20.31.101:49812 - "GET /api/users?page=1&perPage=-1 HTTP/1.1" 403 Forbidden

[hbh7]

Hmm yep, admin false... That'd probably do it lol. What's the easiest way to change that?

[boc-the-git]

Well.. as an admin ... you could do it via https://baseurl/admin/manage/users/
I'm guessing that won't work for you.

The easiest approach is getting someone who is an admin to make you an admin.

Logically I assume you can't as a non-admin make yourself an admin (if you can, please then raise an issue for it haha).. but there might be a way. I'd suggest trying an API call, one of the 'user' ones..

Your last resort might be modifying the database directly. I don't know of the top of my head, but I'd guesstimate there's a field on the users table that indicates if a user is an admin or not.

[michael-genson]

On mobile so I won't dare write the SQL, but there's an admin field on the users table. You just have to set that to true for your user and that should be enough

https://github.com/mealie-recipes/mealie/blob/mealie-next/mealie%2Fdb%2Fmodels%2Fusers%2Fusers.py#L50

[boc-the-git]

I agree with both those point. Would you like to raise them as separate issues @hbh7 ? (ie 2 new issues)

Let us know if not, I can get to it eventually.

[boc-the-git]

Then we can get this closed for good.
I expect Michael would still like a copy of that backup if you can provide it, doesn't hurt to have more in the test suite.

[michael-genson]

Were you able to see the settings page link even before you were an admin? Or were you just navigating directly to the admin page? I can't seem to find a way for a non-admin to see the settings menu (but I can navigate there directly)

I think kicking users out of the admin page if they're not admins makes sense regardless, but if there's a way to see the settings menu as a non-admin I want to fix that, too

[michael-genson]

FYI #2927

[michael-genson]

Going to close this since the main issue is resolved and a new issue has been created for the admin view errors (#2927)

[hbh7]

Apologies for the delays, been a busy week. Here's some more info:

Backup file from the old working version which does not work on RC2/others: mealie_2024.01.10.15.18.35.zip

I was indeed navigating there directly as I saw the URL mentioned in some debugging stuff I was looking at, and I remember wondering where the normal way to find this was haha.

I also tested restoring the backup on a fresh nightly image, which did throw this in the log but otherwise seemed to upload and then restore as expected.

mealie    | INFO:     10.20.30.91:56499 - "POST /api/admin/backups/upload HTTP/1.1" 500 Internal Server Error
mealie    | ERROR:    Exception in ASGI application
mealie    | Traceback (most recent call last):
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/uvicorn/protocols/http/httptools_impl.py", line 436, in run_asgi
mealie    |     result = await app(  # type: ignore[func-returns-value]
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/uvicorn/middleware/proxy_headers.py", line 78, in __call__
mealie    |     return await self.app(scope, receive, send)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/fastapi/applications.py", line 1106, in __call__
mealie    |     await super().__call__(scope, receive, send)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/starlette/applications.py", line 122, in __call__
mealie    |     await self.middleware_stack(scope, receive, send)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/starlette/middleware/errors.py", line 184, in __call__
mealie    |     raise exc
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/starlette/middleware/errors.py", line 162, in __call__
mealie    |     await self.app(scope, receive, _send)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/starlette/middleware/gzip.py", line 24, in __call__
mealie    |     await responder(scope, receive, send)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/starlette/middleware/gzip.py", line 44, in __call__
mealie    |     await self.app(scope, receive, self.send_with_gzip)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 79, in __call__
mealie    |     raise exc
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/starlette/middleware/exceptions.py", line 68, in __call__
mealie    |     await self.app(scope, receive, sender)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 20, in __call__
mealie    |     raise e
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/fastapi/middleware/asyncexitstack.py", line 17, in __call__
mealie    |     await self.app(scope, receive, send)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/starlette/routing.py", line 718, in __call__
mealie    |     await route.handle(scope, receive, send)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/starlette/routing.py", line 276, in handle
mealie    |     await self.app(scope, receive, send)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/starlette/routing.py", line 66, in app
mealie    |     response = await func(request)
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/fastapi/routing.py", line 292, in app
mealie    |     content = await serialize_response(
mealie    |   File "/opt/pysetup/.venv/lib/python3.10/site-packages/fastapi/routing.py", line 155, in serialize_response
mealie    |     raise ResponseValidationError(
mealie    | fastapi.exceptions.ResponseValidationError: 1 validation errors:
mealie    |   {'loc': ('response',), 'msg': 'none is not an allowed value', 'type': 'type_error.none.not_allowed'}
mealie    |

Moving my test instance from your PR up to latest had no issues. I'll probably stay here until the next release.

Thanks for all the help! :)