Unable to migrate database on staging

[karenc]

Summary

When running main.yml on staging, the database migration handler failed:

RUNNING HANDLER [repository_db : migrate repository database] *********************************************************************************************************************************************************************************
fatal: [staging08.cnx.org]: FAILED! => {"changed": true, "cmd": ["/var/cnx/venvs/publishing/bin/dbmigrator", "--config", "/etc/cnx/archive/app.ini", "--context", "cnx-db", "migrate"], "delta": "0:00:02.394862", "end": "2017-08-14 16:10:37.530792", "failed": true, "rc": 1, "start": "2017-08-14 16:10:35.135930", "stderr": "Traceback (most recent call last):\n  File \"/var/cnx/venvs/publishing/bin/dbmigrator\", line 11, in <module>\n    sys.exit(main())\n  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/cli.py\", line 89, in main\n    return args['cmmd'](**args)\n  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/utils.py\", line 83, in wrapper\n    return func(cursor, *args, **kwargs)\n  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/commands/migrate.py\", line 30, in cli_command\n    migration)\n  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/utils.py\", line 150, in compare_schema\n    callback(*args, **kwargs)\n  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/utils.py\", line 161, in run_migration\n    migration.up(cursor)\n  File \"../../var/cnx/venvs/publishing/local/lib/python2.7/site-packages/cnxdb/migrations/20160506121704_add_filename_to_resources.py\", line 7, in up\n    ADD COLUMN filename TEXT\"\"\")\npsycopg2.ProgrammingError: column \"filename\" of relation \"pending_resources\" already exists", "stderr_lines": ["Traceback (most recent call last):", "  File \"/var/cnx/venvs/publishing/bin/dbmigrator\", line 11, in <module>", "    sys.exit(main())", "  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/cli.py\", line 89, in main", "    return args['cmmd'](**args)", "  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/utils.py\", line 83, in wrapper", "    return func(cursor, *args, **kwargs)", "  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/commands/migrate.py\", line 30, in cli_command", "    migration)", "  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/utils.py\", line 150, in compare_schema", "    callback(*args, **kwargs)", "  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/utils.py\", line 161, in run_migration", "    migration.up(cursor)", "  File \"../../var/cnx/venvs/publishing/local/lib/python2.7/site-packages/cnxdb/migrations/20160506121704_add_filename_to_resources.py\", line 7, in up", "    ADD COLUMN filename TEXT\"\"\")", "psycopg2.ProgrammingError: column \"filename\" of relation \"pending_resources\" already exists"], "stdout": "Running migration 20160506121704 add_filename_to_resources", "stdout_lines": ["Running migration 20160506121704 add_filename_to_resources"]}

To make it easier to read, the traceback:

RUNNING HANDLER [repository_db : migrate repository database] *********************************************************************************************************************************************************************************
"cmd": ["/var/cnx/venvs/publishing/bin/dbmigrator", "--config", "/etc/cnx/archive/app.ini", "--context", "cnx-db", "migrate"],
"stderr": "Traceback (most recent call last):
  File \"/var/cnx/venvs/publishing/bin/dbmigrator\", line 11, in <module>
    sys.exit(main())
  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/cli.py\", line 89, in main
    return args['cmmd'](**args)
  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/utils.py\", line 83, in wrapper
    return func(cursor, *args, **kwargs)
  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/commands/migrate.py\", line 30, in cli_command
    migration)
  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/utils.py\", line 150, in compare_schema
    callback(*args, **kwargs)
  File \"/var/cnx/venvs/publishing/local/lib/python2.7/site-packages/dbmigrator/utils.py\", line 161, in run_migration
    migration.up(cursor)
  File \"../../var/cnx/venvs/publishing/local/lib/python2.7/site-packages/cnxdb/migrations/20160506121704_add_filename_to_resources.py\", line 7, in up
    ADD COLUMN filename TEXT\"\"\")
psycopg2.ProgrammingError: column \"filename\" of relation \"pending_resources\" already exists",
"stdout": "Running migration 20160506121704 add_filename_to_resources"

There are 2 problems:

1. The migration has already been applied to the database, but dbmigrator does not know about it.
2. cnx-db migrations should not run because archive and publishing are running code that predates cnx-db.

Environment

staging (and probably prod)

[karenc]

https://github.com/Connexions/cnx-deploy/commit/8da6e95f5770569bfde4a8396f2c8b5ddecc0ad8

[karenc]

https://github.com/Connexions/cnx-deploy/commit/f0c0d3acfce527a274476348dd17a2fa36ae508b

[karenc]

On the prod database:

cnxarchive=# select * from schema_migrations order by version;
    version     |            applied
----------------+-------------------------------
 20160101000000 | 2016-06-10 06:15:39.323207+02
 20160104115058 | 2016-06-10 07:05:50.081735+02
 20160128110515 | 2016-06-10 06:46:51.34293+02
 20160128111115 | 2016-06-10 07:05:51.207721+02
 20160308064742 | 2016-06-10 07:05:52.081576+02
 20160309072525 | 2016-06-10 07:21:48.126611+02
 20160314200432 | 2016-06-10 07:23:29.640983+02
 20160423022147 | 2016-06-10 07:23:30.747354+02
(8 rows)     

There are 2 migrations in cnx-db that ran are not in the schema_migrations table:

• 20160506121704_add_filename_to_resources.py
• 20160506125405_add_pending_resource_associations.py

In order to be able to run migrations again, we need to do mark those migrations as completed:

dbmigrator --db-connection-string='dbname=cnxarchive user=cnxarchive' mark -t 20160506121704
dbmigrator --db-connection-string='dbname=cnxarchive user=cnxarchive' mark -t 20160506125405

[reedstrm]

Staging is a copy of production, so those should be:

dbmigrator --db-connection-string='dbname=repository user=rhaptos' mark -t 20160506121704
dbmigrator --db-connection-string='dbname=repository user=rhaptos' mark -t 20160506125405

[karenc]

We've now migrated all databases so closing this.