Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[BUG] - PostgreSQL upgrade 2.4.2 -> 2.5.0 failed on creating households FK #5014

Open
5 of 6 tasks
krakonos1602 opened this issue Feb 3, 2025 · 0 comments
Open
5 of 6 tasks

[BUG] - PostgreSQL upgrade 2.4.2 -> 2.5.0 failed on creating households FK #5014

krakonos1602 opened this issue Feb 3, 2025 · 0 comments
Labels
bug Something isn't working triage

Comments

@krakonos1602
Copy link

First Check

  • This is not a feature request.
  • I added a very descriptive title to this issue (title field is above this).
  • I used the GitHub search to find a similar issue and didn't find it.
  • I searched the Mealie documentation, with the integrated search.
  • I already read the docs and didn't find an answer.
  • This issue can be replicated on the demo site (https://demo.mealie.io/).

What is the issue you are experiencing?

When running the latest container (2.5.0 following 2.4.2), the PostgreSQL database schema upgrade fails on: psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "households"

Steps to Reproduce

  1. Starting up 2.5.0 container with 2.4.2 data folder and PGSQL database.

Please provide relevant logs

mealie | Switching to dedicated user
mealie |
mealie | User uid: 1000
mealie | User gid: 1000
mealie |
mealie | INFO 2025-02-03T23:04:05 - Started server process [1]
mealie | INFO 2025-02-03T23:04:05 - Waiting for application startup.
mealie | INFO 2025-02-03T23:04:05 - start: database initialization
mealie | INFO 2025-02-03T23:04:05 - Database connection established.
mealie | INFO 2025-02-03T23:04:05 - Context impl PostgresqlImpl.
mealie | INFO 2025-02-03T23:04:05 - Will assume transactional DDL.
mealie | INFO 2025-02-03T23:04:06 - Migration needed. Performing migration...
mealie | INFO 2025-02-03T23:04:06 - Context impl PostgresqlImpl.
mealie | INFO 2025-02-03T23:04:06 - Will assume transactional DDL.
mealie | INFO 2025-02-03T23:04:06 - Running upgrade b1020f328e98 -> b9e516e2d3b3, add household to recipe last made, household to foods, and household to tools
mealie | ERROR 2025-02-03T23:04:06 - Traceback (most recent call last):
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
mealie | self.dialect.do_execute(
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute
mealie | cursor.execute(statement, parameters)
mealie | psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "households"
mealie |
mealie |
mealie | The above exception was the direct cause of the following exception:
mealie |
mealie | Traceback (most recent call last):
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/starlette/routing.py", line 693, in lifespan
mealie | async with self.lifespan_context(app) as maybe_state:
mealie | ^^^^^^^^^^^^^^^^^^^^^^^^^^
mealie | File "/usr/local/lib/python3.12/contextlib.py", line 210, in aenter
mealie | return await anext(self.gen)
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/fastapi/routing.py", line 133, in merged_lifespan
mealie | async with original_context(app) as maybe_original_state:
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/usr/local/lib/python3.12/contextlib.py", line 210, in aenter
mealie | return await anext(self.gen)
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/fastapi/routing.py", line 133, in merged_lifespan
mealie | async with original_context(app) as maybe_original_state:
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/usr/local/lib/python3.12/contextlib.py", line 210, in aenter
mealie | return await anext(self.gen)
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/fastapi/routing.py", line 133, in merged_lifespan
mealie | async with original_context(app) as maybe_original_state:
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/usr/local/lib/python3.12/contextlib.py", line 210, in aenter
mealie | return await anext(self.gen)
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/app/mealie/app.py", line 56, in lifespan_fn
mealie | init_db.main()
mealie | File "/app/mealie/db/init_db.py", line 114, in main
mealie | command.upgrade(alembic_cfg, "head")
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/command.py", line 406, in upgrade
mealie | script.run_env()
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/script/base.py", line 586, in run_env
mealie | util.load_python_file(self.dir, "env.py")
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
mealie | module = load_module_py(module_id, path)
mealie | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
mealie | spec.loader.exec_module(module) # type: ignore
mealie | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
mealie | File "", line 999, in exec_module
mealie | File "", line 488, in call_with_frames_removed
mealie | File "/app/mealie/alembic/env.py", line 109, in
mealie | run_migrations_online()
mealie | File "/app/mealie/alembic/env.py", line 103, in run_migrations_online
mealie | context.run_migrations()
mealie | File "", line 8, in run_migrations
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
mealie | self.get_context().run_migrations(**kw)
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/runtime/migration.py", line 623, in run_migrations
mealie | step.migration_fn(**kw)
mealie | File "/app/mealie/alembic/versions/2024-11-20-17.30.41_b9e516e2d3b3_add_household_to_recipe_last_made.py", line 178, in upgrade
mealie | op.create_table(
mealie | File "", line 8, in create_table
mealie | File "", line 3, in create_table
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/operations/ops.py", line 1318, in create_table
mealie | return operations.invoke(op)
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/operations/base.py", line 442, in invoke
mealie | return fn(self, operation)
mealie | ^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/operations/toimpl.py", line 143, in create_table
mealie | operations.impl.create_table(table, **kw)
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/ddl/impl.py", line 406, in create_table
mealie | self._exec(schema.CreateTable(table, **kw))
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/ddl/impl.py", line 247, in _exec
mealie | return conn.execute(construct, params)
mealie | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
mealie | return meth(
mealie | ^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
mealie | return connection._execute_ddl(
mealie | ^^^^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1527, in _execute_ddl
mealie | ret = self._execute_context(
mealie | ^^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1843, in _execute_context
mealie | return self._exec_single_context(
mealie | ^^^^^^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
mealie | self._handle_dbapi_exception(
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2352, in _handle_dbapi_exception
mealie | raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
mealie | self.dialect.do_execute(
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute
mealie | cursor.execute(statement, parameters)
mealie | sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "households"
mealie |
mealie | [SQL:
mealie | CREATE TABLE households_to_recipes (
mealie | id UUID NOT NULL,
mealie | household_id UUID NOT NULL,
mealie | recipe_id UUID NOT NULL,
mealie | last_made TIMESTAMP WITHOUT TIME ZONE,
mealie | created_at TIMESTAMP WITHOUT TIME ZONE,
mealie | update_at TIMESTAMP WITHOUT TIME ZONE,
mealie | PRIMARY KEY (id, household_id, recipe_id),
mealie | FOREIGN KEY(household_id) REFERENCES households (id),
mealie | FOREIGN KEY(recipe_id) REFERENCES recipes (id),
mealie | CONSTRAINT household_id_recipe_id_key UNIQUE (household_id, recipe_id)
mealie | )
mealie |
mealie | ]
mealie | (Background on this error at: https://sqlalche.me/e/20/f405)
mealie |
mealie | ERROR 2025-02-03T23:04:06 - Traceback (most recent call last):
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
mealie | self.dialect.do_execute(
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute
mealie | cursor.execute(statement, parameters)
mealie | psycopg2.errors.InvalidForeignKey: there is no unique constraint matching given keys for referenced table "households"
mealie |
mealie |
mealie | The above exception was the direct cause of the following exception:
mealie |
mealie | Traceback (most recent call last):
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/starlette/routing.py", line 693, in lifespan
mealie | async with self.lifespan_context(app) as maybe_state:
mealie | ^^^^^^^^^^^^^^^^^^^^^^^^^^
mealie | File "/usr/local/lib/python3.12/contextlib.py", line 210, in aenter
mealie | return await anext(self.gen)
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/fastapi/routing.py", line 133, in merged_lifespan
mealie | async with original_context(app) as maybe_original_state:
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/usr/local/lib/python3.12/contextlib.py", line 210, in aenter
mealie | return await anext(self.gen)
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/fastapi/routing.py", line 133, in merged_lifespan
mealie | async with original_context(app) as maybe_original_state:
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/usr/local/lib/python3.12/contextlib.py", line 210, in aenter
mealie | return await anext(self.gen)
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/fastapi/routing.py", line 133, in merged_lifespan
mealie | async with original_context(app) as maybe_original_state:
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/usr/local/lib/python3.12/contextlib.py", line 210, in aenter
mealie | return await anext(self.gen)
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/app/mealie/app.py", line 56, in lifespan_fn
mealie | init_db.main()
mealie | File "/app/mealie/db/init_db.py", line 114, in main
mealie | command.upgrade(alembic_cfg, "head")
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/command.py", line 406, in upgrade
mealie | script.run_env()
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/script/base.py", line 586, in run_env
mealie | util.load_python_file(self.dir, "env.py")
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
mealie | module = load_module_py(module_id, path)
mealie | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
mealie | spec.loader.exec_module(module) # type: ignore
mealie | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
mealie | File "", line 999, in exec_module
mealie | File "", line 488, in call_with_frames_removed
mealie | File "/app/mealie/alembic/env.py", line 109, in
mealie | run_migrations_online()
mealie | File "/app/mealie/alembic/env.py", line 103, in run_migrations_online
mealie | context.run_migrations()
mealie | File "", line 8, in run_migrations
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
mealie | self.get_context().run_migrations(**kw)
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/runtime/migration.py", line 623, in run_migrations
mealie | step.migration_fn(**kw)
mealie | File "/app/mealie/alembic/versions/2024-11-20-17.30.41_b9e516e2d3b3_add_household_to_recipe_last_made.py", line 178, in upgrade
mealie | op.create_table(
mealie | File "", line 8, in create_table
mealie | File "", line 3, in create_table
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/operations/ops.py", line 1318, in create_table
mealie | return operations.invoke(op)
mealie | ^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/operations/base.py", line 442, in invoke
mealie | return fn(self, operation)
mealie | ^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/operations/toimpl.py", line 143, in create_table
mealie | operations.impl.create_table(table, **kw)
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/ddl/impl.py", line 406, in create_table
mealie | self._exec(schema.CreateTable(table, **kw))
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/alembic/ddl/impl.py", line 247, in _exec
mealie | return conn.execute(construct, params)
mealie | ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1416, in execute
mealie | return meth(
mealie | ^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/sql/ddl.py", line 180, in _execute_on_connection
mealie | return connection._execute_ddl(
mealie | ^^^^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1527, in _execute_ddl
mealie | ret = self._execute_context(
mealie | ^^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1843, in _execute_context
mealie | return self._exec_single_context(
mealie | ^^^^^^^^^^^^^^^^^^^^^^^^^^
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1983, in _exec_single_context
mealie | self._handle_dbapi_exception(
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2352, in _handle_dbapi_exception
mealie | raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1964, in _exec_single_context
mealie | self.dialect.do_execute(
mealie | File "/opt/pysetup/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 942, in do_execute
mealie | cursor.execute(statement, parameters)
mealie | sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InvalidForeignKey) there is no unique constraint matching given keys for referenced table "households"
mealie |
mealie | [SQL:
mealie | CREATE TABLE households_to_recipes (
mealie | id UUID NOT NULL,
mealie | household_id UUID NOT NULL,
mealie | recipe_id UUID NOT NULL,
mealie | last_made TIMESTAMP WITHOUT TIME ZONE,
mealie | created_at TIMESTAMP WITHOUT TIME ZONE,
mealie | update_at TIMESTAMP WITHOUT TIME ZONE,
mealie | PRIMARY KEY (id, household_id, recipe_id),
mealie | FOREIGN KEY(household_id) REFERENCES households (id),
mealie | FOREIGN KEY(recipe_id) REFERENCES recipes (id),
mealie | CONSTRAINT household_id_recipe_id_key UNIQUE (household_id, recipe_id)
mealie | )
mealie |
mealie | ]
mealie | (Background on this error at: https://sqlalche.me/e/20/f405)
mealie |
mealie | ERROR 2025-02-03T23:04:06 - Application startup failed. Exiting.
mealie | ERROR 2025-02-03T23:04:06 - Application startup failed. Exiting.
mealie exited with code 0

Mealie Version

v2.5.0 Docker + PostgreSQL

Deployment

Docker (Linux)

Additional Deployment Details

The database is running on another container and is accessed over TCP.
@krakonos1602 krakonos1602 added bug Something isn't working triage labels Feb 3, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working triage
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@krakonos1602