Cannot downgrade to database revision 4070806f6972 in PostgreSQL

[Juan Antonio Osorio Robles]

When trying to downgrade to revision 4070806f6972 I get the following stack trace:

2015-01-29 13:56:40.983 12113 ERROR __main__ [-] Problem trying to execute Alembic commands
2015-01-29 13:56:40.983 12113 TRACE __main__ Traceback (most recent call last):
2015-01-29 13:56:40.983 12113 TRACE __main__ File "bin/barbican-db-manage.py", line 132, in main
2015-01-29 13:56:40.983 12113 TRACE __main__ dm.execute()
2015-01-29 13:56:40.983 12113 TRACE __main__ File "bin/barbican-db-manage.py", line 116, in execute
2015-01-29 13:56:40.983 12113 TRACE __main__ args.func(args)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "bin/barbican-db-manage.py", line 102, in downgrade
2015-01-29 13:56:40.983 12113 TRACE __main__ commands.downgrade(to_version=args.version, sql_url=args.dburl)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/development/barbican/barbican/model/migration/commands.py", line 73, in downgrade
2015-01-29 13:56:40.983 12113 TRACE __main__ alembic_command.downgrade(alembic_cfg, to_version)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/alembic/command.py", line 193, in downgrade
2015-01-29 13:56:40.983 12113 TRACE __main__ script.run_env()
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/alembic/script.py", line 382, in run_env
2015-01-29 13:56:40.983 12113 TRACE __main__ util.load_python_file(self.dir, 'env.py')
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/alembic/util.py", line 242, in load_python_file
2015-01-29 13:56:40.983 12113 TRACE __main__ module = load_module_py(module_id, path)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/alembic/compat.py", line 79, in load_module_py
2015-01-29 13:56:40.983 12113 TRACE __main__ mod = imp.load_source(module_id, path, fp)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/development/barbican/barbican/model/migration/alembic_migrations/env.py", line 100, in <module>
2015-01-29 13:56:40.983 12113 TRACE __main__ run_migrations_online()
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/development/barbican/barbican/model/migration/alembic_migrations/env.py", line 91, in run_migrations_online
2015-01-29 13:56:40.983 12113 TRACE __main__ context.run_migrations()
2015-01-29 13:56:40.983 12113 TRACE __main__ File "<string>", line 7, in run_migrations
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/alembic/environment.py", line 742, in run_migrations
2015-01-29 13:56:40.983 12113 TRACE __main__ self.get_context().run_migrations(**kw)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/alembic/migration.py", line 305, in run_migrations
2015-01-29 13:56:40.983 12113 TRACE __main__ step.migration_fn(**kw)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/development/barbican/barbican/model/migration/alembic_migrations/versions/2ab3f5371bde_dsa_in_container_type_modelbase_to.py", line 38, in downgrade
2015-01-29 13:56:40.983 12113 TRACE __main__ op.execute( 'ALTER TABLE container_secret DROP PRIMARY KEY, ADD PRIMARY KEY(`container_id`,`secret_id`)');
2015-01-29 13:56:40.983 12113 TRACE __main__ File "<string>", line 7, in execute
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/alembic/operations.py", line 1270, in execute
2015-01-29 13:56:40.983 12113 TRACE __main__ execution_options=execution_options)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/alembic/ddl/impl.py", line 108, in execute
2015-01-29 13:56:40.983 12113 TRACE __main__ self._exec(sql, execution_options)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/alembic/ddl/impl.py", line 105, in _exec
2015-01-29 13:56:40.983 12113 TRACE __main__ return conn.execute(construct, *multiparams, **params)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 729, in execute
2015-01-29 13:56:40.983 12113 TRACE __main__ return meth(self, multiparams, params)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 322, in _execute_on_connection
2015-01-29 13:56:40.983 12113 TRACE __main__ return connection._execute_clauseelement(self, multiparams, params)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 826, in _execute_clauseelement
2015-01-29 13:56:40.983 12113 TRACE __main__ compiled_sql, distilled_params
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context
2015-01-29 13:56:40.983 12113 TRACE __main__ context)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1159, in _handle_dbapi_exception
2015-01-29 13:56:40.983 12113 TRACE __main__ exc_info
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
2015-01-29 13:56:40.983 12113 TRACE __main__ reraise(type(exception), exception, tb=exc_tb)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context
2015-01-29 13:56:40.983 12113 TRACE __main__ context)
2015-01-29 13:56:40.983 12113 TRACE __main__ File "/home/ejuaoso/.virtualenvs/Barbican/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute
2015-01-29 13:56:40.983 12113 TRACE __main__ cursor.execute(statement, parameters)
2015-01-29 13:56:40.983 12113 TRACE __main__ ProgrammingError: (ProgrammingError) syntax error at or near "PRIMARY"
2015-01-29 13:56:40.983 12113 TRACE __main__ LINE 1: ALTER TABLE container_secret DROP PRIMARY KEY, ADD PRIMARY K...
2015-01-29 13:56:40.983 12113 TRACE __main__ ^
2015-01-29 13:56:40.983 12113 TRACE __main__ 'ALTER TABLE container_secret DROP PRIMARY KEY, ADD PRIMARY KEY(`container_id`,`secret_id`)' {}
2015-01-29 13:56:40.983 12113 TRACE __main__

This might be working in mysql but it certainly does not in postgresql.

[John Wood (john-wood-w)]

I think the issue is that some of the alembic version files are doing more direct to SQL calls than going thru the ORM...so as you say they may work in mysql but not in postgresql. We've talked of using Grenade gate jobs to help with the migration gut checking, but I'm not sure if that includes testing via differing database types?

[Juan Antonio Osorio Robles (juan-osorio-robles)]

Probably, that would be a good idea. Meanwhile I'm working in porting this to standard sqlalchemy/alembic commands, so it will work in both databases.

[Juan Antonio Osorio Robles (juan-osorio-robles)]

I'm having a hard time fixing this bug, actually. The migration script is not clear at all (not even the script description message makes sense). Yet, this is for a very old version of the database. I'm not sure if this should even be supported. I do propose ditching many of these scripts (this and the ones before it) as I don't think there is someone with a Barbican version that old (that would need these scripts). Or even that these script (and the ones before it) will ever be ran.

I will bring this up for the weekly meeting.

[John Wood (john-wood-w)]

Yeah, we'll need to flesh this sort of thing out at the L summit if not before. I'm thinking though the idea is for migration scripts to be cleaned out when the next release starts up, and a lot of these scripts are Juno related.

[Juan Antonio Osorio Robles (juan-osorio-robles)]

It's actually this script 2ab3f5371bde_dsa_in_container_type_modelbase_to.py

[Juan Antonio Osorio Robles (juan-osorio-robles)]

Took me too long with no avail. I offer a beer to whoever makes this work properly in multiple databases.

[Giovanni Torres (gitorres)]

Progress Report
---

The error message implies this is a syntax error in the script
2ab3f5371bde_dsa_in_container_type_modelbase_to.py. This is the
offending line:

ALTER TABLE container_secret DROP CONSTRAINT container_secret_pk, ADD PRIMARY KEY(`id`,`container_id`,`secret_id`)

Fixing this by modifying the alter table *hard codes* the PRIMARY KEY
constraint name. These names, if not explicitly set, are generated by
the database engine, which means we can not safely use them.

Luckily, alembic/sqlalchemy provides a facility to automatically
generate these names. See:

http://alembic.readthedocs.org/en/latest/naming.html

It seems it's in used in repositories.py, however there is no naming
convention set, which means only indexes' names get auto-generated.

Even if it would be in used, the use of SQL in the migration scripts
breaks Alembic's name auto-generation, leaving us back where we are now.

The way forward, in no particular order and assuming my understanding is
correct, seems to be:

* Remove the SQL statements from the migration scripts
* Use a better naming convention in the meta-data object
* Verify that constraints get named accordingly
* Use the new names to produce Alembic statements to alter the constraints

The current gaps I have:

* How can we improved the auto-generation of the scripts so we avoid
this issue in the future?
* What do we do with the current install base? Constraint names will
not match, breaking upgrade/downgrade scripts, possibly making the way forward
described above a non-started.

[Douglas Mendizábal (dougmendizabal)]

OpenStack no longer supports alembic downgrades.

All downgrades have been removed from Barbican per https://git.openstack.org/cgit/openstack/barbican/commit/?id=4436695b328c2b1aa6d0cc6d1628a00e32675af8