Open PeteDraper opened 2 months ago
it should be noted that the 2nd report doesn't seem to create the user this time
sqlalchemy.exc.ProgrammingError: (psycopg.errors.UndefinedTable) relation "department" does not exist LINE 2: FROM department
Did you init the database correctly?
yes - followed the instructions to the letter as normal. I've always had an issue with migrating from this primary db. I'll run some more internal tests 2 sandbox vms and report back.
It seems that the tables are not available on the target instance.
finally managed to get round to testing this again. Both instances are same (latest zou 0.19.55) version - seem to be synching the projects but are throwing errors and isn't synching media across.
The source is our main instance. The destination is my local sandbox. Both have init db's .
seems to be failing on tasks plus others.
Attached is the report for a project to be sync'd across. The destination db was reset and updated as per in the Data Migration steps. Final result looks like all assets were synchronised, users created etc but a lot of thrown errors and no media copied over.
I did a test sync between two basic instances previously and it worked ok - I'm wondering if it's my main db on our main instance which has the issue; if so that might be the reason why I'm getting problems with adding new projects (server loads etc).
tried again with just the base data. Seems to be failing on automations?
Start syncing. 2024-09-26 04:34:18,306 - INFO - 1 studios synced. 2024-09-26 04:34:18,402 - INFO - 17 departments synced. 2024-09-26 04:34:18,518 - INFO - 24 task-types synced. 2024-09-26 04:34:18,594 - INFO - 10 task-status synced. 2024-09-26 04:34:18,623 - INFO - 0 custom-actions synced. 2024-09-26 04:34:18,662 - INFO - 1 organisations synced. 2024-09-26 04:34:18,704 - INFO - 2 project-status synced. 2024-09-26 04:34:19,294 - INFO - 40 entity-types synced. 2024-09-26 04:34:20,039 - INFO - 60 persons synced. Traceback (most recent call last): File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context self.dialect.do_execute( File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 941, in do_execute cursor.execute(statement, parameters) File "/opt/zou/zouenv/lib/python3.12/site-packages/psycopg/cursor.py", line 97, in execute raise ex.with_traceback(None) psycopg.errors.ForeignKeyViolation: insert or update on table "project_status_automation_link" violates foreign key constraint "project_status_automation_link_status_automation_id_fkey" DETAIL: Key (status_automation_id)=(0376de4c-8ee8-4727-bfad-f641dd5359bc) is not present in table "status_automation".
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
Noticed something else - doing the --no-projects also pulled a Film project from the source instance - well it's title but no shots (seems to be hanging when viewing that) and no assets (none were present initially). Very odd.
kitsu 0.19.53
Migrating using zou sync-full & --no-projects creates the users, task statuses, task types correctly, but seems to also start creating projects which is incorrect. It also seems to fail at migrating Automations which then crashes when adding them to the project
2nd report: am also getting the same when testing between two fresh instance installations, one with a test project and one which is clean, plus cleaned with the info in the zou docs -
zou clear-db zou reset-migrations zou upgrade-db
(zouenv) testbed@testbed:/$ DB_PASSWORD=dbpassword SYNC_LOGIN="my@login.com" SYNC_PASSWORD="mypassword" zou sync-full --source http://mysourceipaddress/api --no-projects Start syncing. 2024-09-02 10:08:52,993 - INFO - 0 studios synced. 2024-09-02 10:08:53,211 - INFO - 13 departments synced. 2024-09-02 10:08:53,371 - INFO - 38 task-types synced. 2024-09-02 10:08:53,454 - INFO - 13 task-status synced. 2024-09-02 10:08:53,486 - INFO - 0 custom-actions synced. 2024-09-02 10:08:53,527 - INFO - 1 organisations synced. 2024-09-02 10:08:53,570 - INFO - 2 project-status synced. 2024-09-02 10:08:54,090 - INFO - 40 entity-types synced. 2024-09-02 10:08:54,781 - INFO - 60 persons synced. Traceback (most recent call last): File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context self.dialect.do_execute( File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute cursor.execute(statement, parameters) File "/opt/zou/zouenv/lib/python3.12/site-packages/psycopg/cursor.py", line 97, in execute raise ex.with_traceback(None) psycopg.errors.ForeignKeyViolation: insert or update on table "project_status_automation_link" violates foreign key constraint "project_status_automation_link_status_automation_id_fkey" DETAIL: Key (status_automation_id)=(0376de4c-8ee8-4727-bfad-f641dd5359bc) is not present in table "status_automation".
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
sys.exit(cli())
^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/click/core.py", line 1157, in call
return self.main(args, kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/click/core.py", line 1078, in main
rv = self.invoke(ctx)
^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/click/core.py", line 1434, in invoke
return ctx.invoke(self.callback, ctx.params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/click/core.py", line 783, in invoke
return __callback(args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/cli.py", line 304, in sync_full
commands.import_data_from_another_instance(
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/utils/commands.py", line 495, in import_data_from_another_instance
sync_service.run_main_data_sync(project=project)
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/services/sync_service.py", line 291, in run_main_data_sync
sync_entries(path, model, project=project)
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/services/sync_service.py", line 439, in sync_entries
model.create_from_import_list(results["data"])
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/models/base.py", line 173, in create_from_import_list
cls.create_from_import(data)
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/models/project.py", line 258, in create_from_import
previous_project.set_status_automations(status_automation_ids)
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/models/project.py", line 206, in set_status_automations
return self.set_links(
^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/models/base.py", line 273, in set_links
).first()
^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 2728, in first
return self.limit(1)._iter().first() # type: ignore
^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/query.py", line 2827, in _iter
result: Union[ScalarResult[_T], Result[_T]] = self.session.execute(
^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2351, in execute
return self._execute_internal(
^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2215, in _execute_internal
) = compile_state_cls.orm_pre_session_exec(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 549, in orm_pre_session_exec
session._autoflush()
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3050, in _autoflush
raise e.with_traceback(sys.exc_info()[2])
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3039, in _autoflush
self.flush()
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4341, in flush
self._flush(objects)
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4476, in _flush
with util.safe_reraise():
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in exit
raise exc_value.with_traceback(exc_tb)
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 4437, in _flush
flush_context.execute()
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
rec.execute(self)
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 642, in execute
util.preloaded.orm_persistence.save_obj(
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 93, in save_obj
_emit_insert_statements(
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1048, in _emit_insert_statements
result = connection.execute(
^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
return meth(
^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
return self._exec_single_context(
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
self._handle_dbapi_exception(
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
File "/opt/zou/zouenv/lib/python3.12/site-packages/psycopg/cursor.py", line 97, 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.ForeignKeyViolation) insert or update on table "project_status_automation_link" violates foreign key constraint "project_status_automation_link_status_automation_id_fkey"
DETAIL: Key (status_automation_id)=(0376de4c-8ee8-4727-bfad-f641dd5359bc) is not present in table "status_automation".
[SQL: INSERT INTO project_status_automation_link (project_id, status_automation_id) VALUES (%(project_id)s::UUID, %(status_automation_id)s::UUID)]
[parameters: {'project_id': '6a1da576-d740-4c7b-a6f9-e53b91521674', 'status_automation_id': '0376de4c-8ee8-4727-bfad-f641dd5359bc'}]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
2nd error
(zouenv) testbed@testbed:/$ DB_PASSWORD=myinstancedbpassword SYNC_LOGIN="mylogin@me.com" SYNC_PASSWORD="myloginpassword" zou sync-full --source http://192.168.1.71/api --no-projects Start syncing. 2024-09-02 10:27:13,601 - INFO - 0 studios synced. Traceback (most recent call last): File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context self.dialect.do_execute( File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute cursor.execute(statement, parameters) File "/opt/zou/zouenv/lib/python3.12/site-packages/psycopg/cursor.py", line 97, in execute raise ex.with_traceback(None) psycopg.errors.UndefinedTable: relation "department" does not exist LINE 2: FROM department ^
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
sys.exit(cli())
^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/click/core.py", line 1157, in call
return self.main(args, kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/click/core.py", line 1078, in main
rv = self.invoke(ctx)
^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/click/core.py", line 1688, in invoke
return _process_result(sub_ctx.command.invoke(sub_ctx))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/click/core.py", line 1434, in invoke
return ctx.invoke(self.callback, ctx.params)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/click/core.py", line 783, in invoke
return __callback(args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/cli.py", line 304, in sync_full
commands.import_data_from_another_instance(
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/utils/commands.py", line 495, in import_data_from_another_instance
sync_service.run_main_data_sync(project=project)
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/services/sync_service.py", line 291, in run_main_data_sync
sync_entries(path, model, project=project)
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/services/sync_service.py", line 439, in sync_entries
model.create_from_import_list(results["data"])
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/models/base.py", line 173, in create_from_import_list
cls.create_from_import(data)
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/models/base.py", line 157, in create_from_import
previous_data = cls.get(data["id"])
^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/zou/app/models/base.py", line 41, in get
return db.session.get(cls, id)
^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/scoping.py", line 1060, in get
return self._proxied.get(
^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3682, in get
return self._get_impl(
^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3862, in _get_impl
return db_load_fn(
^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/loading.py", line 694, in load_on_pk_identity
session.execute(
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2351, in execute
return self._execute_internal(
^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 2236, in _execute_internal
result: Result[Any] = compile_state_cls.orm_execute_statement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/orm/context.py", line 293, in orm_execute_statement
result = conn.execute(
^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1418, in execute
return meth(
^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 515, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1640, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1846, in _execute_context
return self._exec_single_context(
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1986, in _exec_single_context
self._handle_dbapi_exception(
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2353, in _handle_dbapi_exception
raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1967, in _exec_single_context
self.dialect.do_execute(
File "/opt/zou/zouenv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
cursor.execute(statement, parameters)
File "/opt/zou/zouenv/lib/python3.12/site-packages/psycopg/cursor.py", line 97, in execute
raise ex.with_traceback(None)
sqlalchemy.exc.ProgrammingError: (psycopg.errors.UndefinedTable) relation "department" does not exist
LINE 2: FROM department
^
[SQL: SELECT department.name AS department_name, department.color AS department_color, department.archived AS department_archived, department.id AS department_id, department.created_at AS department_created_at, department.updated_at AS department_updated_at
FROM department
WHERE department.id = %(pk_1)s::UUID]
[parameters: {'pk_1': '9e366fa1-90f6-4a64-9fdc-8717b818c61d'}]
(Background on this error at: https://sqlalche.me/e/20/f405)