← Back to team overview

yahoo-eng-team team mailing list archive

  1. yahoo-eng-team team
  2. Mailing list archive
  3. Message #18449

[Bug 1351413] [NEW] DB migration failure due to 006_mysql_downgrade script handled mysql reserved word as column name wrongly

  Thread Previous • Date Previous • • Thread Next 
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

Follow ups

References

  Thread Previous • Date Previous • • Thread Next