Problem: Migrations Fail when Upgrading

misilot commented 1 month ago

Expected behaviour Migrations to succeeed

Current behaviour Migrations fail on the step

TASK [artefactual.archivematica-src : Run migrations] ****************************************************************************************************************************************************************
fatal: [arc22]: FAILED! => {"changed": false, "cmd": ["./manage.py", "migrate", "--noinput", "--pythonpath=/usr/lib/archivematica/archivematicaCommon"], "msg": "stdout: Operations to perform:\n  Apply all migrations: administration, auth, contenttypes, fpr, main, sessions, tastypie\nRunning migrations:\n  Applying fpr.0001_initial... OK\n  Applying main.0001_initial...\n:stderr: System check identified some issues:\n\nWARNINGS:\ntastypie.ApiAccess: (models.W042) Auto-created primary key used when not defining a primary key type, by default 'django.db.models.AutoField'.\n\tHINT: Configure the DEFAULT_AUTO_FIELD setting or the AppConfig.default_auto_field attribute to point to a subclass of AutoField, e.g. 'django.db.models.BigAutoField'.\ntastypie.ApiKey: (models.W042) Auto-created primary key used when not defining a primary key type, by default 'django.db.models.AutoField'.\n\tHINT: Configure the DEFAULT_AUTO_FIELD setting or the AppConfig.default_auto_field attribute to point to a subclass of AutoField, e.g. 'django.db.models.BigAutoField'.\nTraceback (most recent call last):\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/utils.py\", line 87, in _execute\n    return self.cursor.execute(sql)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/mysql/base.py\", line 75, in execute\n    return self.cursor.execute(query, args)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/MySQLdb/cursors.py\", line 179, in execute\n    res = self._query(mogrified_query)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/MySQLdb/cursors.py\", line 330, in _query\n    db.query(q)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/MySQLdb/connections.py\", line 261, in query\n    _mysql.connection.query(self, query)\nMySQLdb.OperationalError: (1050, \"Table 'Accesses' already exists\")\n\nThe above exception was the direct cause of the following exception:\n\nTraceback (most recent call last):\n  File \"/opt/archivematica/archivematica/src/dashboard/src/./manage.py\", line 10, in <module>\n    execute_from_command_line(sys.argv)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/core/management/__init__.py\", line 442, in execute_from_command_line\n    utility.execute()\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/core/management/__init__.py\", line 436, in execute\n    self.fetch_command(subcommand).run_from_argv(self.argv)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/core/management/base.py\", line 412, in run_from_argv\n    self.execute(*args, **cmd_options)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/core/management/base.py\", line 458, in execute\n    output = self.handle(*args, **options)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/core/management/base.py\", line 106, in wrapper\n    res = handle_func(*args, **kwargs)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/core/management/commands/migrate.py\", line 356, in handle\n    post_migrate_state = executor.migrate(\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/migrations/executor.py\", line 135, in migrate\n    state = self._migrate_all_forwards(\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/migrations/executor.py\", line 167, in _migrate_all_forwards\n    state = self.apply_migration(\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/migrations/executor.py\", line 252, in apply_migration\n    state = migration.apply(state, schema_editor)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/migrations/migration.py\", line 132, in apply\n    operation.database_forwards(\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/migrations/operations/models.py\", line 96, in database_forwards\n    schema_editor.create_model(model)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/base/schema.py\", line 451, in create_model\n    self.execute(sql, params or None)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/base/schema.py\", line 201, in execute\n    cursor.execute(sql, params)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/utils.py\", line 67, in execute\n    return self._execute_with_wrappers(\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/utils.py\", line 80, in _execute_with_wrappers\n    return executor(sql, params, many, context)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/utils.py\", line 89, in _execute\n    return self.cursor.execute(sql, params)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/utils.py\", line 91, in __exit__\n    raise dj_exc_value.with_traceback(traceback) from exc_value\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/utils.py\", line 87, in _execute\n    return self.cursor.execute(sql)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/mysql/base.py\", line 75, in execute\n    return self.cursor.execute(query, args)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/MySQLdb/cursors.py\", line 179, in execute\n    res = self._query(mogrified_query)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/MySQLdb/cursors.py\", line 330, in _query\n    db.query(q)\n  File \"/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/MySQLdb/connections.py\", line 261, in query\n    _mysql.connection.query(self, query)\ndjango.db.utils.OperationalError: (1050, \"Table 'Accesses' already exists\")\n", "path": "/usr/share/archivematica/virtualenvs/archivematica/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/snap/bin", "syspath": ["/tmp/ansible_django_manage_payload_k2j28g20/ansible_django_manage_payload.zip", "/usr/lib/python310.zip", "/usr/lib/python3.10", "/usr/lib/python3.10/lib-dynload", "/usr/local/lib/python3.10/dist-packages", "/usr/lib/python3/dist-packages"]}

Steps to reproduce Start at Archivematica 1.15 and upgrade using the Ansible Playbook in deploy-pub

Your environment (version of Archivematica, operating system, other relevant details) Archivematica 1.15 -> 1.16 Ubuntu 22.04

For Artefactual use:

Before you close this issue, you must check off the following:

[ ] All pull requests related to this issue are properly linked
[ ] All pull requests related to this issue have been merged
[ ] A testing plan for this issue has been implemented and passed (testing plan information should be included in the issue body or comments)
[ ] Documentation regarding this issue has been written and merged (if applicable)
[ ] Details about this issue have been added to the release notes (if applicable)

misilot commented 1 month ago

When trying to run the migrations by hand

(archivematica) [PROD] root@arc22:/usr/share/archivematica/dashboard# ./manage.py migrate  --pythonpath=/usr/lib/archivematica/archivematicaCommon
System check identified some issues:

WARNINGS:
tastypie.ApiAccess: (models.W042) Auto-created primary key used when not defining a primary key type, by default 'django.db.models.AutoField'.
    HINT: Configure the DEFAULT_AUTO_FIELD setting or the AppConfig.default_auto_field attribute to point to a subclass of AutoField, e.g. 'django.db.models.BigAutoField'.
tastypie.ApiKey: (models.W042) Auto-created primary key used when not defining a primary key type, by default 'django.db.models.AutoField'.
    HINT: Configure the DEFAULT_AUTO_FIELD setting or the AppConfig.default_auto_field attribute to point to a subclass of AutoField, e.g. 'django.db.models.BigAutoField'.
Operations to perform:
  Apply all migrations: administration, auth, contenttypes, fpr, main, sessions, tastypie
Running migrations:
  Applying main.0001_initial...Traceback (most recent call last):
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
MySQLdb.OperationalError: (1050, "Table 'Accesses' already exists")

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

Traceback (most recent call last):
  File "/opt/archivematica/archivematica/src/dashboard/src/./manage.py", line 10, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/migrations/operations/models.py", line 96, in database_forwards
    schema_editor.create_model(model)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 451, in create_model
    self.execute(sql, params or None)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/django/db/backends/mysql/base.py", line 75, in execute
    return self.cursor.execute(query, args)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/MySQLdb/cursors.py", line 179, in execute
    res = self._query(mogrified_query)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/MySQLdb/cursors.py", line 330, in _query
    db.query(q)
  File "/usr/share/archivematica/virtualenvs/archivematica/lib/python3.9/site-packages/MySQLdb/connections.py", line 261, in query
    _mysql.connection.query(self, query)
django.db.utils.OperationalError: (1050, "Table 'Accesses' already exists")

mamedin commented 1 month ago

Have you installed AM in a new server and then you imported the database from the old server?

If this is the case, probably you have not deleted the default AM1.16 database so you have merged both databases. Please, can you try:

droping the default databases
creating empty databases
importing databases
Run the script to fix encoding and collation in database imported from MySQL 5.x
run AM migrations

mamedin commented 1 month ago

Instead of doing all the steps I said in last note, please, can you run the migrations with the following commands instead:

sudo -u archivematica bash -c " \
    set -a -e -x
    source /etc/default/archivematica-dashboard || \
        source /etc/sysconfig/archivematica-dashboard \
            || (echo 'Environment file not found'; exit 1)
    cd /usr/share/archivematica/dashboard
    /usr/share/archivematica/virtualenvs/archivematica/bin/python manage.py migrate --noinput
";

sudo -u archivematica bash -c " \
    set -a -e -x
    source /etc/default/archivematica-storage-service || \
        source /etc/sysconfig/archivematica-storage-service \
            || (echo 'Environment file not found'; exit 1)
    cd /usr/lib/archivematica/storage-service
    /usr/share/archivematica/virtualenvs/archivematica-storage-service/bin/python manage.py migrate
";

See: https://www.archivematica.org/en/docs/archivematica-1.16/admin-manual/installation-setup/upgrading/upgrading/

misilot commented 1 month ago

Have you installed AM in a new server and then you imported the database from the old server?

If this is the case, probably you have not deleted the default AM1.16 database so you have merged both databases. Please, can you try:
* droping the default databases

* creating empty databases

* importing databases

* Run the script to fix encoding and collation in database imported from MySQL 5.x

* run AM migrations

Nope this was done as an in-place upgrade. However I think when I restored the 16GB file (and possibly the export isn't complete) but I noticed there were missing migrations, as if I go look at the restored system I see all the existing migrations with much older dates, compared to there being only one migration in the django_migrations table with the current date.

I'm trying to do an export from the recovered system, and seeing if that will work.

misilot commented 1 month ago

@mamedin is there a preferred method do the a MySQL export?

The directions at https://www.archivematica.org/en/docs/archivematica-1.16/admin-manual/installation-setup/upgrading/upgrading mysqldump -u root -p MCP > ~/am_backup.sql fail with

mysqldump: Couldn't execute 'SHOW FIELDS FROM `developmentAide_choicesDisplayed`': View 'MCP.developmentAide_choicesDisplayed' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them (1356)

misilot commented 1 month ago

What is the best way to recreate this view MCP.developmentAide_choicesDisplayed?

I guess maybe this view shouldn't exist?

select `MCP`.`MicroServiceChainChoice`.`pk` AS `pk`,`MCP`.`MicroServiceChainChoice`.`lastModified` AS `lastModified`,`MCP`.`MicroServiceChainChoice`.`chainAvailable` AS `chainAvailable`,`MCP`.`MicroServiceChainChoice`.`choiceAvailableAtLink` AS `choiceAvailableAtLink`,`MCP`.`MicroServiceChainChoice`.`replaces` AS `replaces`,`MCP`.`TasksConfigs`.`description` AS `Text`,`MCP`.`MicroServiceChains`.`description` AS `Choice` from (((`MCP`.`MicroServiceChainChoice` join `MCP`.`MicroServiceChainLinks` on((`MCP`.`MicroServiceChainChoice`.`choiceAvailableAtLink` = `MCP`.`MicroServiceChainLinks`.`pk`))) join `MCP`.`TasksConfigs` on((`MCP`.`MicroServiceChainLinks`.`currentTask` = `MCP`.`TasksConfigs`.`pk`))) join `MCP`.`MicroServiceChains` on((`MCP`.`MicroServiceChainChoice`.`chainAvailable` = `MCP`.`MicroServiceChains`.`pk`)))

misilot commented 4 weeks ago

I deleted the view and it all worked for me. Not sure when that view got removed in the code but I guess it was still sticking around for us.

archivematica / Issues

Problem: Migrations Fail when Upgrading #1695