OpenStack DBaaS (Trove)

db_sync code incompatible with Mariadb 10.0

Bug #1473226 reported by Tom Verdaat
32
This bug affects 7 people
Affects Status Importance Assigned to Milestone
OpenStack DBaaS (Trove)
Fix Released
High
Victoria Martinez de la Cruz
Liberty
Fix Committed
High
Unassigned
Mitaka
Fix Committed
High
Unassigned
Newton
Fix Released
High
Victoria Martinez de la Cruz
OpenStack DBaaS (Trove) newton-rc1 "RC1"

Bug Description

Installing trove (kilo version) and it fails when running trove-manage db_sync

OperationalError: (OperationalError) (1832, "Cannot change column 'datastore_version_id': used in a foreign key constraint 'instances_ibfk_1'") '\nALTER TABLE instances CHANGE COLUMN datastore_version_id datastore_version_id VARCHAR(36) COLLATE utf8_unicode_ci NOT NULL'

This is due to the current version of mariadb 10.0 being more strict about altering columns used in foreign key constraints.

Workaround:

1. Create trove database:
CREATE DATABASE trove;
GRANT ALL PRIVILEGES ON trove.* TO trove@'localhost' IDENTIFIED BY 'pass';
GRANT ALL PRIVILEGES ON trove.* TO trove@'%' IDENTIFIED BY 'pass';
2. Disable FK checks globally (affects all sessions):
use trove;
SET GLOBAL foreign_key_checks=0;
3. Setup all config files on Trove controller, then run:
su -s /bin/sh -c "trove-manage db_sync" trove
4. Enable FK again
SET GLOBAL foreign_key_checks=1;

Nikhil Manchanda (slicknik)
Changed in trove:
importance: Undecided → High
status: New → Confirmed
milestone: none → liberty-3
Victoria Martinez de la Cruz (vkmc)
Changed in trove:
assignee: nobody → Victoria Martinez de la Cruz (vkmc)
Nikhil Manchanda (slicknik)
Changed in trove:
status: Confirmed → Triaged
Nikhil Manchanda (slicknik)
Changed in trove:
milestone: liberty-3 → ongoing
Nikhil Manchanda (slicknik)
Changed in trove:
milestone: ongoing → liberty-rc1
Revision history for this message
Nikhil Manchanda (slicknik) wrote :

This is not blocking for Liberty-RC1

Changed in trove:
milestone: liberty-rc1 → next
Revision history for this message
Victoria Martinez de la Cruz (vkmc) wrote :

This is no longer reproducible in Liberty.

Revision history for this message
Javier Peña (jpena-c) wrote :

I have reproduced it in Liberty, using RDO Trunk packages on Fedora 22. A simple Packstack installation with Trove enabled fails with the same error when running trove-manage.

Revision history for this message
Victoria Martinez de la Cruz (vkmc) wrote :

Thanks Javier, I'll try to reproduce with Packstack and fix it.

Revision history for this message
Doug Shelley (0-doug) wrote :

This issue occurs on all post Mysql 5.6.7 variants. It is described here:
http://stackoverflow.com/questions/17015844/mysql-5-6-foreign-key-constraint-error-didnt-occur-in-5-5

In sqlalchemy/migrate_repo/019-datastore-fix, we are attempting to change a column from NULL to NOT NULL. The column is part of a foreign key - as of Mysql 5.6 (or MariaDB 10) this is no longer allowed by default.

My suggestion would be to change that migrate file to disable foreign_key_checks for the duration of it's run. This will need to be special cased for mysql and I have no idea if the same issue happens on Postgresql.

Revision history for this message
Doug Shelley (0-doug) wrote :

One this I forgot to mention - the issue can be reproduced using "trove-manage db_recreate"

Revision history for this message
Victoria Martinez de la Cruz (vkmc) wrote :

Thanks Doug. Indeed, it's affecting MySQL latest version as well.

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to trove (master)

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

Changed in trove:
status: Triaged → In Progress
Amrith Kumar (amrith)
Changed in trove:
milestone: next → newton-1
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to trove (master)

Reviewed: https://review.openstack.org/299568
Committed: https://git.openstack.org/cgit/openstack/trove/commit/?id=679e2283ecc888bd567955b4bf431c87043e8d12
Submitter: Jenkins
Branch: master

commit 679e2283ecc888bd567955b4bf431c87043e8d12
Author: Victoria Martinez de la Cruz <email address hidden>
Date: Wed Mar 30 15:05:59 2016 -0300

    Fixes migrations for MySQL 5.6.* and MariaDB 10.1.*

    In MySQL 5.6/MariaDB 10.1 and later versions there was a change
    on the restrictions over foreign keys (FK).

    In 019-datastore-fix.py, we attempt to change a column that is part
    of a FK from NULL to not NULL. This sort of modifications are not
    longer allowed.

    This situation caused trove-manage db_sync and trove-manage db_migrate
    to fail.

    To workaround this, the FK check before executing the ALTER query
    is disabled and then reenabled.

    Change-Id: I666d01235f2c3225aca3fe7520ebdf6d53831cab
    Closes-Bug: #1473226

Changed in trove:
status: New → Fix Released
Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to trove (stable/liberty)

Fix proposed to branch: stable/liberty
Review: https://review.openstack.org/301722

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix proposed to trove (stable/mitaka)

Fix proposed to branch: stable/mitaka
Review: https://review.openstack.org/301726

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to trove (stable/mitaka)

Reviewed: https://review.openstack.org/301726
Committed: https://git.openstack.org/cgit/openstack/trove/commit/?id=be6f18892c199c100d5a94f2bb30ddb66c943bb1
Submitter: Jenkins
Branch: stable/mitaka

commit be6f18892c199c100d5a94f2bb30ddb66c943bb1
Author: Victoria Martinez de la Cruz <email address hidden>
Date: Wed Mar 30 15:05:59 2016 -0300

    Fixes migrations for MySQL 5.6.* and MariaDB 10.1.*

    In MySQL 5.6/MariaDB 10.1 and later versions there was a change
    on the restrictions over foreign keys (FK).

    In 019-datastore-fix.py, we attempt to change a column that is part
    of a FK from NULL to not NULL. This sort of modifications are not
    longer allowed.

    This situation caused trove-manage db_sync and trove-manage db_migrate
    to fail.

    To workaround this, the FK check before executing the ALTER query
    is disabled and then reenabled.

    Change-Id: I666d01235f2c3225aca3fe7520ebdf6d53831cab
    Closes-Bug: #1473226
    (cherry picked from commit 679e2283ecc888bd567955b4bf431c87043e8d12)

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix merged to trove (stable/liberty)

Reviewed: https://review.openstack.org/301722
Committed: https://git.openstack.org/cgit/openstack/trove/commit/?id=1a349bb96be5dece65a672648ecc6f7cae9641b2
Submitter: Jenkins
Branch: stable/liberty

commit 1a349bb96be5dece65a672648ecc6f7cae9641b2
Author: Victoria Martinez de la Cruz <email address hidden>
Date: Wed Mar 30 15:05:59 2016 -0300

    Fixes migrations for MySQL 5.6.* and MariaDB 10.1.*

    In MySQL 5.6/MariaDB 10.1 and later versions there was a change
    on the restrictions over foreign keys (FK).

    In 019-datastore-fix.py, we attempt to change a column that is part
    of a FK from NULL to not NULL. This sort of modifications are not
    longer allowed.

    This situation caused trove-manage db_sync and trove-manage db_migrate
    to fail.

    To workaround this, the FK check before executing the ALTER query
    is disabled and then reenabled.

    Change-Id: I666d01235f2c3225aca3fe7520ebdf6d53831cab
    Closes-Bug: #1473226
    (cherry picked from commit 679e2283ecc888bd567955b4bf431c87043e8d12)

Revision history for this message
Doug Hellmann (doug-hellmann) wrote : Fix included in openstack/trove 5.0.1

This issue was fixed in the openstack/trove 5.0.1 release.

Revision history for this message
Doug Hellmann (doug-hellmann) wrote : Fix included in openstack/trove 6.0.0.0b2

This issue was fixed in the openstack/trove 6.0.0.0b2 development milestone.

Revision history for this message
OpenStack Infra (hudson-openstack) wrote : Fix included in openstack/trove 4.0.1

This issue was fixed in the openstack/trove 4.0.1 release.

To post a comment you must log in.
This report contains Public information  
Everyone can see this information.
Subscribing...

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.