Trouble Migrating Database

[daniel....@gmail.com]

Hi Everyone,

I'm trying to migrate a legacy database but I get the following error: 

(paint) daniel:~/aiida_backup$ verdi -p aiida_v1 storage migrate
Warning: Migrating your storage might take a while and is not reversible.
Warning: Before continuing, make sure you have completed the following steps:
Warning:
Warning:  1. Make sure you have no active calculations and workflows.
Warning:  2. If you do, revert the code to the previous version and finish running them first.
Warning:  3. Stop the daemon using `verdi daemon stop`
Warning:  4. Make a backup of your database and repository
Warning:
Warning: If you have completed the steps above and want to migrate profile "aiida_v1", type MIGRATE NOW: MIGRATE NOW
Report: Migrating to the head of the legacy sqlalchemy branch
Report: - bf591f31dd12 -> 0edcdd5a30f0
Report: - 0edcdd5a30f0 -> 7536a82b2cc4
Warning: Detected repository folders that were missing the required subfolder `path` or `raw_input`. The paths of those nodes repository folders have been written to a log file: /home/daniel/aiida_backup/migration-repository-missing-subfolder-4d_jv5r3.json
Warning: Migrated file repository to the new disk object store. The old repository has not been deleted out of safety and can be found at /home/daniel/aiida_backup/repository-aiida_v1.1/repository.
Report: - 7536a82b2cc4 -> 1feaea71bd5a
Report: - 1feaea71bd5a -> 535039300e4a
Report: - 535039300e4a -> 34a831f4286d
Report: - 34a831f4286d -> 1de112340b16
Traceback (most recent call last):
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.ObjectInUse: cannot ALTER TABLE "db_dbnode" because it has pending trigger events


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

Traceback (most recent call last):
  File "/home/daniel/virtualenvs/paint/bin/verdi", line 8, in <module>
    sys.exit(verdi())
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/home/daniel/install/paint/aiida-core/aiida/cmdline/commands/cmd_storage.py", line 78, in storage_migrate
    storage_cls.migrate(profile)
  File "/home/daniel/install/paint/aiida-core/aiida/storage/psql_dos/backend.py", line 92, in migrate
    migrator.migrate()
  File "/home/daniel/install/paint/aiida-core/aiida/storage/psql_dos/migrator.py", line 377, in migrate
    self.migrate_up('sqlalchemy@head')
  File "/home/daniel/install/paint/aiida-core/aiida/storage/psql_dos/migrator.py", line 394, in migrate_up
    upgrade(config, version)
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/alembic/command.py", line 378, in upgrade
    script.run_env()
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/alembic/script/base.py", line 576, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/alembic/util/pyfiles.py", line 94, in load_python_file
    module = load_module_py(module_id, path)
  File "/home/daniel/virtualenvs/paint/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 "/home/daniel/install/paint/aiida-core/aiida/storage/psql_dos/migrations/env.py", line 49, in <module>
    run_migrations_online()
  File "/home/daniel/install/paint/aiida-core/aiida/storage/psql_dos/migrations/env.py", line 42, in run_migrations_online
    context.run_migrations()  # pylint: disable=no-member
  File "<string>", line 8, in run_migrations
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/alembic/runtime/environment.py", line 867, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/alembic/runtime/migration.py", line 624, in run_migrations
    step.migration_fn(**kw)
  File "/home/daniel/install/paint/aiida-core/aiida/storage/psql_dos/migrations/versions/1de112340b17_django_parity_2.py", line 73, in upgrade
    op.alter_column('db_dbnode', 'ctime', existing_type=sa.DateTime(timezone=True), nullable=False)
  File "<string>", line 8, in alter_column
  File "<string>", line 3, in alter_column
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/alembic/operations/ops.py", line 1879, in alter_column
    return operations.invoke(alt)
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/alembic/operations/base.py", line 401, in invoke
    return fn(self, operation)
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/alembic/operations/toimpl.py", line 50, in alter_column
    operations.impl.alter_column(
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/alembic/ddl/postgresql.py", line 178, in alter_column
    super().alter_column(
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/alembic/ddl/impl.py", line 229, in alter_column
    self._exec(
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/alembic/ddl/impl.py", line 193, in _exec
    return conn.execute(  # type: ignore[call-overload]
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/sqlalchemy/future/engine.py", line 280, in execute
    return self._execute_20(
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1705, in _execute_20
    return meth(self, args_10style, kwargs_10style, execution_options)
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/sqlalchemy/sql/ddl.py", line 80, in _execute_on_connection
    return connection._execute_ddl(
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1472, in _execute_ddl
    ret = self._execute_context(
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1943, in _execute_context
    self._handle_dbapi_exception(
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 2124, in _handle_dbapi_exception
    util.raise_(
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/sqlalchemy/util/compat.py", line 211, in raise_
    raise exception
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/sqlalchemy/engine/base.py", line 1900, in _execute_context
    self.dialect.do_execute(
  File "/home/daniel/virtualenvs/paint/lib/python3.10/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.OperationalError: (psycopg2.errors.ObjectInUse) cannot ALTER TABLE "db_dbnode" because it has pending trigger events

[SQL: ALTER TABLE db_dbnode ALTER COLUMN ctime SET NOT NULL]
(Background on this error at: https://sqlalche.me/e/14/e3q8)

This seems very similar to a previous error I once had, and was resolved here: https://github.com/aiidateam/aiida-core/pull/4602. 

I was hoping to use 

$python aiida/backends/sqlalchemy/manage.py upgrade <version>

 as a workaround, but it seems like the 'manage.py' script no longer exists? Still, is there a way to run the migration 'one step at a time' as a workaround? 

Daniel 

[daniel....@gmail.com]

I wanted to share a workaround I found for migrating a legacy database
To address the issue, I performed the following steps:

Downgraded my AiiDA installation to v1.6.9, which did not require a database migration.
Upgraded AiiDA one version at a time (e.g., v1.6.9 to v2.0.0, v2.0.0 to v2.0.1, etc.), migrating the database if necessary.
By only performing one migration at a time, I was able to successfully migrate the database. 

Best regards,
Daniel

[mbe...@gmail.com]

Hi Daniel,

Sorry for the slow response, I'm not much of a database wizz to be honest. Glad to hear you manage to resolve the issue, and thanks for sharing your solution! However, it shouldn't be necessary to do this, so I'll bring it up at the next AiiDA team meeting and see if we can understand what happened.

Best,

Marnik