Error when updating database

Support
1

Hi all,
I was following the update guide on zou.cgwire.com and ran into an issue when updating the database. I get a UniqueViolation:

psycopg.errors.UniqueViolation: duplicate key value violates unique constraint "ix_task_status_short_name"
DETAIL:  Key (short_name)=(approved) already exists.

I have tried downgrading, upgrading, restarting…etc, but I keep getting the same error. Since getting this error I can no longer access the database through kitsu.

Any help with this would be greatly appreciated.

Here is the full trace:

pcadmin@kitsu-server:~$ DB_PASSWORD=######## /opt/zou/zouenv/bin/zou upgrade-db
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.runtime.migration] Running upgrade 7748d3d22925 -> c49e41f1298b, Add PreviewBackground
INFO  [alembic.runtime.migration] Running upgrade c49e41f1298b -> 05b7dc79a416, Add archived fields to main tables
INFO  [alembic.runtime.migration] Running upgrade 05b7dc79a416 -> a7c43f3fbc76, Add duration column to the preview file
INFO  [alembic.runtime.migration] Running upgrade a7c43f3fbc76 -> 5b9fd9ddfe43, add homepage and contract fields
INFO  [alembic.runtime.migration] Running upgrade 5b9fd9ddfe43 -> feffd3c5b806, Introduce concepts
Traceback (most recent call last):
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.UniqueViolation: duplicate key value violates unique constraint "ix_task_status_short_name"
DETAIL:  Key (short_name)=(approved) already exists.

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

Traceback (most recent call last):
  File "/opt/zou/zouenv/bin/zou", line 8, in <module>
    sys.exit(cli())
  File "/opt/zou/zouenv/lib/python3.10/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/opt/zou/zouenv/lib/python3.10/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/zou/cli.py", line 104, in upgrade_db
    flask_migrate.upgrade(directory=migrations_path)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/flask_migrate/__init__.py", line 111, in wrapped
    f(*args, **kwargs)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/flask_migrate/__init__.py", line 200, in upgrade
    command.upgrade(config, revision, sql=sql, tag=tag)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/alembic/command.py", line 399, in upgrade
    script.run_env()
  File "/opt/zou/zouenv/lib/python3.10/site-packages/alembic/script/base.py", line 578, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/opt/zou/zouenv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
    module = load_module_py(module_id, path)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 109, 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 "/opt/zou/zouenv/lib/python3.10/site-packages/zou/migrations/env.py", line 108, in <module>
    run_migrations_online()
  File "/opt/zou/zouenv/lib/python3.10/site-packages/zou/migrations/env.py", line 85, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/opt/zou/zouenv/lib/python3.10/site-packages/alembic/runtime/environment.py", line 937, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/alembic/runtime/migration.py", line 624, in run_migrations
    step.migration_fn(**kw)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/zou/migrations/versions/feffd3c5b806_introduce_concepts.py", line 131, in upgrade
    session.merge(rejected_status)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3897, in merge
    self._autoflush()
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 3005, in _autoflush
    raise e.with_traceback(sys.exc_info()[2])
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 2994, in _autoflush
    self.flush()
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 4296, in flush
    self._flush(objects)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 4431, in _flush
    with util.safe_reraise():
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/orm/session.py", line 4392, in _flush
    flush_context.execute()
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
    rec.execute(self)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/orm/unitofwork.py", line 642, in execute
    util.preloaded.orm_persistence.save_obj(
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
    _emit_insert_statements(
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/orm/persistence.py", line 1233, in _emit_insert_statements
    result = connection.execute(
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1422, in execute
    return meth(
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/sql/elements.py", line 514, in _execute_on_connection
    return connection._execute_clauseelement(
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1644, in _execute_clauseelement
    ret = self._execute_context(
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1850, in _execute_context
    return self._exec_single_context(
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1990, in _exec_single_context
    self._handle_dbapi_exception(
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2357, in _handle_dbapi_exception
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1971, in _exec_single_context
    self.dialect.do_execute(
  File "/opt/zou/zouenv/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 919, in do_execute
    cursor.execute(statement, parameters)
  File "/opt/zou/zouenv/lib/python3.10/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
sqlalchemy.exc.IntegrityError: (raised as a result of Query-invoked autoflush; consider using a session.no_autoflush block if this flush is occurring prematurely)
(psycopg.errors.UniqueViolation) duplicate key value violates unique constraint "ix_task_status_short_name"
DETAIL:  Key (short_name)=(approved) already exists.
[SQL: INSERT INTO task_status (name, archived, short_name, color, is_done, is_artist_allowed, is_client_allowed, is_retake, is_feedback_request, is_default, shotgun_id, for_concept, id, created_at, updated_at) VALUES (%(name)s::VARCHAR, %(archived)s, %(short_name)s::VARCHAR, %(color)s::VARCHAR, %(is_done)s, %(is_artist_allowed)s, %(is_client_allowed)s, %(is_retake)s, %(is_feedback_request)s, %(is_default)s, %(shotgun_id)s::INTEGER, %(for_concept)s, %(id)s::UUID, %(created_at)s::TIMESTAMP WITHOUT TIME ZONE, %(updated_at)s::TIMESTAMP WITHOUT TIME ZONE)]
[parameters: {'name': 'Approved', 'archived': False, 'short_name': 'approved', 'color': '#66BB6A', 'is_done': False, 'is_artist_allowed': False, 'is_client_allowed': False, 'is_retake': False, 'is_feedback_request': False, 'is_default': False, 'shotgun_id': None, 'for_concept': True, 'id': '004de43e-1eda-49ca-b2e8-9bf75aaec36e', 'created_at': datetime.datetime(2024, 3, 26, 14, 23, 16, 123178), 'updated_at': datetime.datetime(2024, 3, 26, 14, 23, 16, 123181)}]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2

A little more reading in the docs / troubleshooting. I tried smaller incrimental updates which seemed to solve the issue. I now have an issue where when trying to login I get a perpetual “loading data” message. Will keep on trying to solve.
api version:

{
    "api": "Zou",
    "version": "0.17.38"
}

status

{
    "name": "Zou",
    "version": "0.17.38",
    "database-up": true,
    "key-value-store-up": true,
    "event-stream-up": true,
    "job-queue-up": false,
    "indexer-up": false
}
3

Just for anyone that comes around in the future,
The fix for me was incimentally updating zou. I went pretty far back and slowly installed each version and testing to see if anything changed with loading kitsu.
Specifially

{
  "api": "Zou",
  "version": "0.17.49"
}

{
  "name": "Zou",
  "version": "0.17.49",
  "database-up": true,
  "key-value-store-up": true,
  "event-stream-up": true,
  "job-queue-up": false,
  "indexer-up": true
}
1 Like