alembic_version table has no primary key

[Proskurin Kirill]

Currently alembic_version table in the neutron db has no primary key.

Which is a bad thing, if you consider to use Galera as a database, since it requires primary keys in all tables.

For example, during the "INFO [alembic.runtime.migration] Running upgrade -> kilo, kilo_initial" migration you will get the:

oslo_db.exception.DBError: (pymysql.err.InternalError) (1105, u'Percona-XtraDB-Cluster prohibits use of DML command on a table (neutron.alembic_version) without an explicit primary key with pxc_strict_mode = ENFORCING or MASTER') [SQL: u"INSERT INTO alembic_version (version_num) VALUES ('kilo')"]

[Boden R (boden)]

While this defect reads as high impact, the fact that this issue has existed since kilo (perhaps forever) seems to indicate it's not impacting our users greatly.

Moreover based on Percona docs [1] it appears one could change pxc_strict_mode to mitigate the issue temporary.

[1] https://www.percona.com/doc/percona-xtradb-cluster/5.7/features/pxc-strict-mode.html

[Proskurin Kirill (kproskurin)]

Its true - you could set it to the permissive, but you should not do it. Percona just make its an option, but Galera, in general, depends on the primary keys in all tables.

[Ann Taraday (akamyshnikova)]

The main issue here - alembic_version table is not created/updated by Neutron. It does Alembic automatically. I pointed this bug to Mike Bayer I hope to get some input from him here.

[Mike Bayer (zzzeek)]

in http://galeracluster.com/documentation-webpages/limitations.html, the only limitation that affects alembic as far as the primary key issue is the claim that DELETE is not supported. When they say statements like "SELECT..LIMIT" can return different results, that's not an issue for alembic, and IMO it should not be an issue in general because you should never use LIMIT without ORDER BY. I'm not sure how the claim that "DELETE is not supported" can be true because Alembic uses DELETE on the alembic_version table whenever branches merge, and Neutron IIRC uses a lot of branching. If "not supported" just means, "may or may not work", that would be more plausible.

This issue has never been reported upstream and it is trivial to put a primary key on the alembic_version table so I've created upstream at https://bitbucket.org/zzzeek/alembic/issues/406 and https://gerrit.sqlalchemy.org/#/c/279/.

[Ann Taraday (akamyshnikova)]

I guess the appropriate fix for that will be waiting for new version of alembic and then update the requirements.

[Ann Taraday (akamyshnikova)]

I put change for Neutron on review as well https://review.openstack.org/#/c/419320/

[Ann Taraday (akamyshnikova)]

I propose change for requirements https://review.openstack.org/#/c/423118/ with bumping alembic version for 0.8.10. Note, change https://review.openstack.org/#/c/419320/ still needed.

We need to decide how this can be solved for Newton. Is it possible to bump version of alembic in requirements for stable/newton? If not we need to backport https://review.openstack.org/#/c/419320/ partially.

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/419320
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=fc075fa0d83bbd2a4c7b46299a35ccedb91388dc
Submitter: Jenkins
Branch: master

commit fc075fa0d83bbd2a4c7b46299a35ccedb91388dc
Author: Ann <email address hidden>
Date: Thu Jan 12 08:12:50 2017 +0000

    Manually add pk for alembic_version table

    We hit a problem that Neutron migrations cannot be applied
    on galera cluster with ENFORCING mode as alembic_version table
    missing primary key on the table and it is expected that all tables
    will have primary keys.

    alembic_version is created and managed by Alembic and fixes for this
    on Alembic side are on the review [1]. Anyway here migration is
    proposed that will add pk on this table if it is missing, as in
    other case we will have inconsistency between new deployments and
    old ones.

    [1] - https://gerrit.sqlalchemy.org/#/c/279/

    Change-Id: I543a1ee286bdf11ae35adb87125d044a351a2648
    Closes-bug: #1655610

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: stable/newton
Review: https://review.openstack.org/425031

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/425031
Committed: https://git.openstack.org/cgit/openstack/neutron/commit/?id=bd272cb0599436bba5414037ca33bf80c52bb784
Submitter: Jenkins
Branch: stable/newton

commit bd272cb0599436bba5414037ca33bf80c52bb784
Author: Ann <email address hidden>
Date: Thu Jan 12 08:12:50 2017 +0000

    Manually add pk for alembic_version table

    We hit a problem that Neutron migrations cannot be applied
    on galera cluster with ENFORCING mode as alembic_version table
    missing primary key on the table and it is expected that all tables
    will have primary keys.

    alembic_version is created and managed by Alembic and fixes for this
    on Alembic side are available since 0.8.10.
    We cannot bump requirements for stable/newton but we can add
    pk manually to allow new deployments with Newton code on Galera.

    Change-Id: I543a1ee286bdf11ae35adb87125d044a351a2648
    Closes-bug: #1655610

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron 10.0.0.0b3 development milestone.

[György Szombathelyi (gyurco)]

Just want to add that neutron has more alembic_version tables:
alembic_version
alembic_version_vpnaas
alembic_version_lbaas
alembic_version_fwaas

And they're also affected.

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/neutron 9.3.0 release.