db util drop_unique_constraint doesn't work for sqlite

[Matt Riedemann]

This patch adds the drop_unique_constraint db utility to oslo-incubator and says it comes from nova:

https://review.openstack.org/#/c/37439/

But that's not the same code that's in nova.db.sqlalchemy.utils:

https://github.com/openstack/nova/blob/stable/havana/nova/db/sqlalchemy/utils.py#L168

The version in nova handles sqlite differently by recreating the table since sqlite doesn't support 'alter table drop constraint' calls, as shown in this nova patch here using the oslo db utility:

http://logs.openstack.org/47/69047/19/check/gate-nova-python27/ed044cc/testr_results.html.gz

So the oslo-incubator version needs to be synced up with the latest version in nova.

This is also going to block on my change to nova to remove the duplicate code from nova that's in oslo's db utils:

https://review.openstack.org/#/c/79070/

[Matt Riedemann (mriedem)]

Probably the best proof that this doesn't work is sqlalchemy-migrate's sqlite constraint dropper doesn't support dropping constraints in migrations:

https://github.com/stackforge/sqlalchemy-migrate/blob/0.9/migrate/changeset/databases/sqlite.py#L143

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/87369

[Matt Riedemann (mriedem)]

I just saw this: https://review.openstack.org/#/c/75026/

That said the drop_unique_constraint method in oslo's db utils is deprecated but that still doesn't make it work with sqlite from what I'm seeing when running nova migrations with that code.

[Viktor Serhieiev (vsergeyev)]

Seems to be, that you don't use monkey-patching for sqlalchemy-migrate in your code. See https://github.com/openstack/nova/blob/master/nova/openstack/common/db/sqlalchemy/migration.py#L131.
This monkey-patch adds a workaround for SQLite's inability to alter things and a possibility to use the unified drop_unique_constraint()
This feature was added to Oslo in patch https://review.openstack.org/#/c/31016/ (Add a monkey-patching util for sqlalchemy-migrate)

[Matt Riedemann (mriedem)]

Victor, thanks, I've abandoned my oslo-incubator change and marked this as a bug for nova, I'll probably make that change here:

https://review.openstack.org/#/c/79070/