search

mealie-recipes / mealie

Mealie is a self hosted recipe manager and meal planner with a RestAPI backend and a reactive frontend application built in Vue for a pleasant user experience for the whole family. Easily add recipes into your database by providing the url and mealie will automatically import the relevant data or add a family recipe with the UI editor
https://docs.mealie.io
GNU Affero General Public License v3.0
7.52k stars 752 forks source link

[v1.0.0b] - mealie api sqlite3.OperationalError #1531

Closed m10x closed 2 years ago

m10x commented 2 years ago

First Check

What is the issue you are experiencing?

Hello, when I restart mealie with docker compose mealie-api always shows the following error. Therefore I need to clean install and restore from backup again.

  File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/ddl/impl.py", line 195, in _exec
    return conn.execute(construct, multiparams)
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1306, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1398, in _execute_ddl
    ret = self._execute_context(
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) duplicate column name: webhook_type
[SQL: ALTER TABLE webhook_urls ADD COLUMN webhook_type VARCHAR]
(Background on this error at: https://sqlalche.me/e/14/e3q8)
Production
usermod: no changes

    User uid:    1000
    User gid:    1000

INFO: 08-Aug-22 11:25:34    Database connection established.
INFO: 08-Aug-22 11:25:34    Context impl SQLiteImpl.
INFO: 08-Aug-22 11:25:34    Will assume non-transactional DDL.
INFO: 08-Aug-22 11:25:34    Migration needed. Performing migration...
INFO: 08-Aug-22 11:25:34    Context impl SQLiteImpl.
INFO: 08-Aug-22 11:25:34    Will assume non-transactional DDL.
INFO: 08-Aug-22 11:25:34    Running upgrade ab0bae02578f -> f30cf048c228, add new webhook fields
Traceback (most recent call last):
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlite3.OperationalError: duplicate column name: webhook_type

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/app/mealie/db/init_db.py", line 108, in <module>
    main()
  File "/app/mealie/db/init_db.py", line 94, in main
    command.upgrade(alembic_cfg, "head")
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/command.py", line 322, in upgrade
    script.run_env()
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/script/base.py", line 569, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 110, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
  File "<frozen importlib._bootstrap_external>", line 883, in exec_module
  File "<frozen importlib._bootstrap>", line 241, in _call_with_frames_removed
  File "/app/alembic/env.py", line 77, in <module>
    run_migrations_online()
  File "/app/alembic/env.py", line 71, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/runtime/environment.py", line 853, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/runtime/migration.py", line 623, in run_migrations
    step.migration_fn(**kw)
  File "/app/alembic/versions/2022-06-15-21.05.34_f30cf048c228_add_new_webhook_fields.py", line 22, in upgrade
    op.add_column("webhook_urls", sa.Column("webhook_type", sa.String(), nullable=True))
  File "<string>", line 8, in add_column
  File "<string>", line 3, in add_column
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/operations/ops.py", line 2047, in add_column
    return operations.invoke(op)
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/operations/base.py", line 394, in invoke
    return fn(self, operation)
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/operations/toimpl.py", line 154, in add_column
    operations.impl.add_column(table_name, column, schema=schema, **kw)
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/ddl/impl.py", line 322, in add_column
    self._exec(base.AddColumn(table_name, column, schema=schema))
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/alembic/ddl/impl.py", line 195, in _exec
    return conn.execute(construct, multiparams)
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1306, in execute
    return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1398, in _execute_ddl
    ret = self._execute_context(
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1862, in _execute_context
    self._handle_dbapi_exception(
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2043, in _handle_dbapi_exception
    util.raise_(
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 208, in raise_
    raise exception
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1819, in _execute_context
    self.dialect.do_execute(
  File "/opt/pysetup/.venv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 732, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) duplicate column name: webhook_type
[SQL: ALTER TABLE webhook_urls ADD COLUMN webhook_type VARCHAR]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

Deployment

Docker (Linux)

Deployment Details

My docker compose:

---
version: "3.7"
services:
  mealie-frontend:
    image: hkotel/mealie:frontend-v1.0.0beta-4
    container_name: mealie-frontend
    environment:
    # Set Frontend ENV Variables Here
      - API_URL=http://mealie-api:9000 # 

    restart: always
    ports:
      - "9925:3000" # 

    volumes:
      - /home/m10x/docker/mealie/data/:/app/data/ # 

  mealie-api:
    image: hkotel/mealie:api-v1.0.0beta-4
    container_name: mealie-api
    volumes:
      - /home/m10x/docker/mealie/data/:/app/data/
    environment:
    # Set Backend ENV Variables Here
      - ALLOW_SIGNUP=false
      - PUID=1000
      - PGID=1000
      - TZ=Europe/Berlin
      - MAX_WORKERS=1
      - WEB_CONCURRENCY=1
      - BASE_URL=https://mealie.redacted.duckdns.org/
    restart: always
hay-kot commented 2 years ago

You somehow got into a weird state with your database where it's trying to do the migration but it looks like it was already done. It could be caused by canceling a migration mid-way through, like if you started the container but quickly canceled it.

Not super sure how you can resolve this. You could try doing a full site export and import. If that doesn't work you could manually remove the column from the SQLite table and try restarting the application.

mrwulf commented 2 years ago

My install is in the same state. I can wipe the db and then restore from backup and it works fine until the pod restarts... and then same error as above. I'm running in kubernetes, but the logs are similar and the error is the same.

hay-kot commented 2 years ago

Can one of you post or send me your backup file? I'm unable to reproduce this issue on my computer so if you could also provide a step-by-step way to reproduce that would be extremely helpful.

hay-kot commented 2 years ago

Actually I'm 99% sure it's the issues described here