Tacker db population fails due to sql foreign key constraint on MariaDB version >5.5

[Janki Chhatbar]

DB migration scripts needs to be modified to handle modification of foreign key columns by dropping the constraint, and then adding back the constraint once the column changes are done. This needs to be done for applicable scripts which try to change the column that is involved in a foreign key constraint.

Tacker (master) Devstack installation is failing due to db migration.

INFO [alembic.runtime.migration] Running upgrade acf941e54075 -> f958f58e5daa, uuid consistency
2016-06-21 06:28:56.910 | Traceback (most recent call last):
2016-06-21 06:28:56.910 | File "/usr/bin/tacker-db-manage", line 10, in <module>
2016-06-21 06:28:56.910 | sys.exit(main())
2016-06-21 06:28:56.910 | File "/opt/stack/tacker/tacker/db/migration/cli.py", line 153, in main
2016-06-21 06:28:56.910 | CONF.command.func(config, CONF.command.name)
2016-06-21 06:28:56.910 | File "/opt/stack/tacker/tacker/db/migration/cli.py", line 67, in do_upgrade_downgrade
2016-06-21 06:28:56.910 | do_alembic_command(config, cmd, revision, sql=CONF.command.sql)
2016-06-21 06:28:56.910 | File "/opt/stack/tacker/tacker/db/migration/cli.py", line 45, in do_alembic_command
2016-06-21 06:28:56.910 | getattr(alembic_command, cmd)(config, *args, **kwargs)
2016-06-21 06:28:56.910 | File "/usr/lib/python2.7/site-packages/alembic/command.py", line 174, in upgrade
2016-06-21 06:28:56.910 | script.run_env()
2016-06-21 06:28:56.910 | File "/usr/lib/python2.7/site-packages/alembic/script/base.py", line 397, in run_env
2016-06-21 06:28:56.910 | util.load_python_file(self.dir, 'env.py')
2016-06-21 06:28:56.910 | File "/usr/lib/python2.7/site-packages/alembic/util/pyfiles.py", line 93, in load_python_file
2016-06-21 06:28:56.910 | module = load_module_py(module_id, path)
2016-06-21 06:28:56.910 | File "/usr/lib/python2.7/site-packages/alembic/util/compat.py", line 79, in load_module_py
2016-06-21 06:28:56.910 | mod = imp.load_source(module_id, path, fp)
2016-06-21 06:28:56.910 | File "/opt/stack/tacker/tacker/db/migration/alembic_migrations/env.py", line 84, in <module>
2016-06-21 06:28:56.910 | run_migrations_online()
2016-06-21 06:28:56.910 | File "/opt/stack/tacker/tacker/db/migration/alembic_migrations/env.py", line 76, in run_migrations_online
2016-06-21 06:28:56.910 | context.run_migrations()
2016-06-21 06:28:56.910 | File "<string>", line 8, in run_migrations
2016-06-21 06:28:56.910 | File "/usr/lib/python2.7/site-packages/alembic/runtime/environment.py", line 797, in run_migrations
2016-06-21 06:28:56.911 | self.get_context().run_migrations(**kw)
2016-06-21 06:28:56.911 | File "/usr/lib/python2.7/site-packages/alembic/runtime/migration.py", line 312, in run_migrations
2016-06-21 06:28:56.911 | step.migration_fn(**kw)
2016-06-21 06:28:56.911 | File "/opt/stack/tacker/tacker/db/migration/alembic_migrations/versions/f958f58e5daa_uuid_consistency.py", line 40, in upgrade
2016-06-21 06:28:56.911 | type_=types.Uuid)
2016-06-21 06:28:56.911 | File "<string>", line 8, in alter_column
2016-06-21 06:28:56.911 | File "<string>", line 3, in alter_column
2016-06-21 06:28:56.911 | File "/usr/lib/python2.7/site-packages/alembic/operations/ops.py", line 1414, in alter_column
2016-06-21 06:28:56.911 | return operations.invoke(alt)
2016-06-21 06:28:56.911 | File "/usr/lib/python2.7/site-packages/alembic/operations/base.py", line 318, in invoke
2016-06-21 06:28:56.911 | return fn(self, operation)
2016-06-21 06:28:56.911 | File "/usr/lib/python2.7/site-packages/alembic/operations/toimpl.py", line 53, in alter_column
2016-06-21 06:28:56.911 | **operation.kw
2016-06-21 06:28:56.911 | File "/usr/lib/python2.7/site-packages/alembic/ddl/mysql.py", line 67, in alter_column
2016-06-21 06:28:56.911 | else existing_autoincrement
2016-06-21 06:28:56.911 | File "/usr/lib/python2.7/site-packages/alembic/ddl/impl.py", line 118, in _exec
2016-06-21 06:28:56.911 | return conn.execute(construct, *multiparams, **params)
2016-06-21 06:28:56.911 | File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
2016-06-21 06:28:56.911 | return meth(self, multiparams, params)
2016-06-21 06:28:56.911 | File "/usr/lib64/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
2016-06-21 06:28:56.911 | return connection._execute_ddl(self, multiparams, params)
2016-06-21 06:28:56.911 | File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 968, in _execute_ddl
2016-06-21 06:28:56.911 | compiled
2016-06-21 06:28:56.911 | File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
2016-06-21 06:28:56.911 | context)
2016-06-21 06:28:56.911 | File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1341, in _handle_dbapi_exception
2016-06-21 06:28:56.912 | exc_info
2016-06-21 06:28:56.912 | File "/usr/lib64/python2.7/site-packages/sqlalchemy/util/compat.py", line 202, in raise_from_cause
2016-06-21 06:28:56.912 | reraise(type(exception), exception, tb=exc_tb, cause=cause)
2016-06-21 06:28:56.912 | File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
2016-06-21 06:28:56.912 | context)
2016-06-21 06:28:56.912 | File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
2016-06-21 06:28:56.912 | cursor.execute(statement, parameters)
2016-06-21 06:28:56.912 | File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 161, in execute
2016-06-21 06:28:56.912 | result = self._query(query)
2016-06-21 06:28:56.912 | File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 317, in _query
2016-06-21 06:28:56.912 | conn.query(q)
2016-06-21 06:28:56.912 | File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 835, in query
2016-06-21 06:28:56.912 | self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2016-06-21 06:28:56.912 | File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1019, in _read_query_result
2016-06-21 06:28:56.912 | result.read()
2016-06-21 06:28:56.912 | File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1302, in read
2016-06-21 06:28:56.912 | first_packet = self.connection._read_packet()
2016-06-21 06:28:56.912 | File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 981, in _read_packet
2016-06-21 06:28:56.912 | packet.check_error()
2016-06-21 06:28:56.912 | File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 393, in check_error
2016-06-21 06:28:56.912 | err.raise_mysql_exception(self._data)
2016-06-21 06:28:56.912 | File "/usr/lib/python2.7/site-packages/pymysql/err.py", line 120, in raise_mysql_exception
2016-06-21 06:28:56.912 | _check_mysql_exception(errinfo)
2016-06-21 06:28:56.912 | File "/usr/lib/python2.7/site-packages/pymysql/err.py", line 115, in _check_mysql_exception
2016-06-21 06:28:56.912 | raise InternalError(errno, errorvalue)
2016-06-21 06:28:56.913 | sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1833, u"Cannot change column 'id': used in a foreign key constraint 'devices_ibfk_2' of table 'tacker.devices'") [SQL: u'ALTER TABLE vims MODIFY id VARCHAR(36) NULL']
2016-06-21 06:28:56.948 | Error on exit

stack.sh.log.summary
--------------------
2016-06-21 06:21:53.265 | stack.sh log /opt/stack/logs/stack.sh.log.2016-06-21-115153
2016-06-21 06:21:55.843 | Installing package prerequisites
2016-06-21 06:21:57.740 | tacker's plugin.sh was called...
2016-06-21 06:21:58.881 | Installing OpenStack project source
2016-06-21 06:22:09.394 | tacker's plugin.sh was called...
2016-06-21 06:22:09.420 | Installing Tacker
2016-06-21 06:22:10.014 | Starting RabbitMQ
2016-06-21 06:22:11.993 | Configuring and starting MySQL
2016-06-21 06:22:19.263 | Starting Keystone
2016-06-21 06:25:14.627 | Configuring Horizon
2016-06-21 06:25:17.501 | Configuring Glance
2016-06-21 06:25:21.938 | Configuring Neutron
2016-06-21 06:25:42.711 | Configuring Nova
2016-06-21 06:25:54.787 | tacker's plugin.sh was called...
2016-06-21 06:25:54.814 | Configuring Tacker
2016-06-21 06:26:15.560 | Starting Glance
2016-06-21 06:26:24.190 | Uploading images
2016-06-21 06:26:29.182 | Starting Nova API
2016-06-21 06:26:35.682 | Starting Neutron
2016-06-21 06:26:54.911 | Creating initial neutron network elements
2016-06-21 06:27:21.032 | Starting Nova
2016-06-21 06:27:56.307 | Configuring Heat
2016-06-21 06:27:57.332 | Starting Heat
2016-06-21 06:28:10.359 | Starting Horizon
2016-06-21 06:28:56.223 | tacker's plugin.sh was called...
2016-06-21 06:28:56.252 | Initializing Tacker

[Sripriya (sseetha)]

Can you please share the environment details you used to reproduce this bug? are you running devstack on a RHEL system?

[Janki Chhatbar (jankihchhatbar)]

Hi Sripriya,

I am running devstack in RHEL 7 Server. Following repos are enabled:

OpenStack Mitaka Repository
Red Hat Enterprise Linux 7 Server - Optional (RPMs)
Red Hat Enterprise Linux 7 Server (RPMs)

# mysql --version
mysql Ver 15.1 Distrib 10.1.12-MariaDB, for Linux (x86_64) using EditLine wrapper

[Sripriya (sseetha)]

Hi Janki,

MariaDB does not allow modifying foreign keys on already existing definition starting from 5.6 version onwards. Refer [1]. In Mitaka release, RHEL openstack installations upgraded from 5.5 to the newer 10.1.x stable version of MariaDB. This started to break our migration scripts which tries to do a modification on a foreign key column. Ubuntu however still uses the older version 5.5 of MariaDB for Mitaka release. I would suggest you to downgrade the MariaDB version from 10.1.x to 5.5 to get past the issue while we can come up with a solution to fix the breakage.

[1] http://dev.mysql.com/doc/refman/5.6/en/alter-table.html

[Sripriya (sseetha)]

Modified the description to handle the migration script changes in tacker db

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/336327
Committed: https://git.openstack.org/cgit/openstack/tacker/commit/?id=625b7e707cdaf4eee2a4a13e8bb0e895d79461e6
Submitter: Jenkins
Branch: master

commit 625b7e707cdaf4eee2a4a13e8bb0e895d79461e6
Author: Sripriya <email address hidden>
Date: Thu Jun 30 18:16:34 2016 -0700

    Fix foreign key and primary key errors in db scripts

    Starting from 10.1.x of MariaDB and 5.6 of MySQL, they not allow
    changes to primary key and foreign key columns after the definitions
    creation. This breaks db alembic scripts with
    IntegrityError constraints.

    Fix db scripts to drop the foreign key constraint and add them back
    once the definitions are modified in existing schemas.

    Change-Id: I4a4faa1e6d0aafc26f2a56986f83c4c4f0cfc763
    Closes-Bug: #1594807

[OpenStack Infra (hudson-openstack)]

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

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/340528
Committed: https://git.openstack.org/cgit/openstack/tacker/commit/?id=f898caeaed5a4e812712fefaa22792b80a894850
Submitter: Jenkins
Branch: stable/mitaka

commit f898caeaed5a4e812712fefaa22792b80a894850
Author: Sripriya Seetharam <email address hidden>
Date: Mon Jul 11 12:07:11 2016 -0700

    Fix foreign key error in db script

    Starting from 10.1.x of MariaDB and 5.6 of MySQL, they not allow
    changes to primary key and foreign key columns after the definitions
    creation. This breaks db alembic scripts with
    IntegrityError constraints.

    Fix db scripts to drop the foreign key constraint and add them back
    once the definitions are modified in existing schemas.

    Change-Id: Ia4fdb28d07617934a73b361c9b33e63e763c392c
    Closes-Bug: #1594807

[Doug Hellmann (doug-hellmann)]

This issue was fixed in the openstack/tacker 0.4.0 release.