Public bug reported: Because 006_mysql_downgrade didn't correctly quote 'key' column [0][1] of 'image_properties' table when creating index [2], the index wasn't created as needed, and then operator could not run any upgrade script successfully since 006_mysql_upgrade script could not to handle this unexpected db status [3].
[0] http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html [1] http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-0.html [2] https://github.com/openstack/glance/blob/master/glance/db/sqlalchemy/migrate_repo/versions/006_mysql_downgrade.sql#L11 [3] https://github.com/openstack/glance/blob/master/glance/db/sqlalchemy/migrate_repo/versions/006_mysql_upgrade.sql#L5 Note, this issue doesn't impact one-way upgrade migration operation, since bug existed in downgrade script. But once operator do a reciprocating migration operation, this issue will rise up. When I open "snake walk" mode on migration test, this issue could be triggered as well. ====================================================================== FAIL: glance.tests.unit.test_migrations.TestMigrations.test_walk_versions tags: worker-0 ---------------------------------------------------------------------- Traceback (most recent call last): File "glance/tests/unit/test_migrations.py", line 251, in test_walk_versions self._walk_versions(engine, self.snake_walk) File "glance/tests/unit/test_migrations.py", line 345, in _walk_versions self._migrate_up(engine, version) File "glance/tests/unit/test_migrations.py", line 394, in _migrate_up version) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/migrate/versioning/api.py", line 186, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "<string>", line 2, in _migrate File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/migrate/versioning/util/__init__.py", line 160, in with_engine return f(*a, **kw) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/migrate/versioning/api.py", line 366, in _migrate schema.runchange(ver, change, changeset.step) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/migrate/versioning/schema.py", line 93, in runchange change.run(self.engine, step) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/migrate/versioning/script/sql.py", line 43, in run conn.execute(text) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 721, in execute return self._execute_text(object, multiparams, params) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 870, in _execute_text statement, parameters File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context context) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in _handle_dbapi_exception exc_info File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause reraise(type(exception), exception, tb=exc_tb) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context context) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute cursor.execute(statement, parameters) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute self.errorhandler(self, exc, value) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue OperationalError: (OperationalError) (1091, "Can't DROP 'ix_image_properties_image_id_key'; check that column/key exists") '/*\n * This file is necessary because MySQL does not support\n * renaming indexes.\n */\nDROP INDEX ix_image_properties_image_id_key ON image_properties;\n\n/* Rename the `key` column to `name` */\nALTER TABLE image_properties\nCHANGE COLUMN `key` name VARCHAR(255) NOT NULL;\n\nCREATE UNIQUE INDEX ix_image_properties_image_id_name ON image_properties (image_id, name);\n' () ** Affects: glance Importance: Undecided Status: Triaged ** Tags: db ** Tags added: db -- You received this bug notification because you are a member of Yahoo! Engineering Team, which is subscribed to Glance. https://bugs.launchpad.net/bugs/1351413 Title: DB migration failure due to 006_mysql_downgrade script handled mysql reserved word as column name wrongly Status in OpenStack Image Registry and Delivery Service (Glance): Triaged Bug description: Because 006_mysql_downgrade didn't correctly quote 'key' column [0][1] of 'image_properties' table when creating index [2], the index wasn't created as needed, and then operator could not run any upgrade script successfully since 006_mysql_upgrade script could not to handle this unexpected db status [3]. [0] http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-5.html [1] http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedwords-5-0.html [2] https://github.com/openstack/glance/blob/master/glance/db/sqlalchemy/migrate_repo/versions/006_mysql_downgrade.sql#L11 [3] https://github.com/openstack/glance/blob/master/glance/db/sqlalchemy/migrate_repo/versions/006_mysql_upgrade.sql#L5 Note, this issue doesn't impact one-way upgrade migration operation, since bug existed in downgrade script. But once operator do a reciprocating migration operation, this issue will rise up. When I open "snake walk" mode on migration test, this issue could be triggered as well. ====================================================================== FAIL: glance.tests.unit.test_migrations.TestMigrations.test_walk_versions tags: worker-0 ---------------------------------------------------------------------- Traceback (most recent call last): File "glance/tests/unit/test_migrations.py", line 251, in test_walk_versions self._walk_versions(engine, self.snake_walk) File "glance/tests/unit/test_migrations.py", line 345, in _walk_versions self._migrate_up(engine, version) File "glance/tests/unit/test_migrations.py", line 394, in _migrate_up version) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/migrate/versioning/api.py", line 186, in upgrade return _migrate(url, repository, version, upgrade=True, err=err, **opts) File "<string>", line 2, in _migrate File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/migrate/versioning/util/__init__.py", line 160, in with_engine return f(*a, **kw) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/migrate/versioning/api.py", line 366, in _migrate schema.runchange(ver, change, changeset.step) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/migrate/versioning/schema.py", line 93, in runchange change.run(self.engine, step) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/migrate/versioning/script/sql.py", line 43, in run conn.execute(text) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 721, in execute return self._execute_text(object, multiparams, params) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 870, in _execute_text statement, parameters File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 958, in _execute_context context) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1160, in _handle_dbapi_exception exc_info File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause reraise(type(exception), exception, tb=exc_tb) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 951, in _execute_context context) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 436, in do_execute cursor.execute(statement, parameters) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute self.errorhandler(self, exc, value) File "/home/lzy/workspace/openstack/glance/.tox/py27/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler raise errorclass, errorvalue OperationalError: (OperationalError) (1091, "Can't DROP 'ix_image_properties_image_id_key'; check that column/key exists") '/*\n * This file is necessary because MySQL does not support\n * renaming indexes.\n */\nDROP INDEX ix_image_properties_image_id_key ON image_properties;\n\n/* Rename the `key` column to `name` */\nALTER TABLE image_properties\nCHANGE COLUMN `key` name VARCHAR(255) NOT NULL;\n\nCREATE UNIQUE INDEX ix_image_properties_image_id_name ON image_properties (image_id, name);\n' () To manage notifications about this bug go to: https://bugs.launchpad.net/glance/+bug/1351413/+subscriptions -- Mailing list: https://launchpad.net/~yahoo-eng-team Post to : yahoo-eng-team@lists.launchpad.net Unsubscribe : https://launchpad.net/~yahoo-eng-team More help : https://help.launchpad.net/ListHelp