[Bug 1846548] Re: Glance manage db_sync fails with MySQL 8

Fri Oct 4 08:11:28 UTC 2019

sqlalchemy should be automatically quoting reserved works, however the
version in eoan does not have full support for mysql8 yet.

Rather than fixup glance, we should pick the relevant commits into the
sqlalchemy package:

https://github.com/sqlalchemy/sqlalchemy/commit/d08d27b897569ff8f18ca869b00a058652111c24
#diff-ed2c5bb86128c11f3281ccee3633104e

https://github.com/sqlalchemy/sqlalchemy/commit/9a6654e3af74710b55feb6b5b0218dc767d7013b
#diff-ed2c5bb86128c11f3281ccee3633104e

** Also affects: sqlalchemy (Ubuntu)
   Importance: Undecided
       Status: New

-- 
You received this bug notification because you are a member of Ubuntu
OpenStack, which is subscribed to glance in Ubuntu.
https://bugs.launchpad.net/bugs/1846548

Title:
  Glance manage db_sync fails with MySQL 8

Status in glance package in Ubuntu:
  Triaged
Status in sqlalchemy package in Ubuntu:
  New

Bug description:
  [Steps to recreate]

  Configure glance to use a MySQL 8 databse
  Run glance-manage db_sync

  [Error Output]
  https://paste.ubuntu.com/p/NbTQgsxJZw/

  #glance-manage db_sync
  /usr/lib/python3/dist-packages/oslo_db/sqlalchemy/enginefacade.py:1374: OsloDBDeprecationWarning: EngineFacade is deprecated; please use oslo_db.sqlalchemy.enginefacade
    expire_on_commit=expire_on_commit, _conf=conf)
  2019-10-03 18:13:32.980 23795 WARNING oslo_config.cfg [-] Deprecated: Option "idle_timeout" from group "database" is deprecated. Use option "connection_recycle_time" from group "database".[00m
  2019-10-03 18:13:33.013 23795 INFO alembic.runtime.migration [-] Context impl MySQLImpl.[00m
  2019-10-03 18:13:33.014 23795 INFO alembic.runtime.migration [-] Will assume non-transactional DDL.[00m
  2019-10-03 18:13:33.025 23795 INFO alembic.runtime.migration [-] Context impl MySQLImpl.[00m
  2019-10-03 18:13:33.025 23795 INFO alembic.runtime.migration [-] Will assume non-transactional DDL.[00m
  INFO  [alembic.runtime.migration] Context impl MySQLImpl.
  INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
  INFO  [alembic.runtime.migration] Running upgrade  -> liberty, liberty initial
  /usr/lib/python3/dist-packages/pymysql/cursors.py:170: Warning: (3719, "'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")
    result = self._query(query)
  /usr/lib/python3/dist-packages/pymysql/cursors.py:170: Warning: (3719, "'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")
    result = self._query(query)
  /usr/lib/python3/dist-packages/pymysql/cursors.py:170: Warning: (3719, "'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.")
    result = self._query(query)
  CRITI [glance] Unhandled error
  Traceback (most recent call last):
    File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1236, in _execute_context
      cursor, statement, parameters, context
    File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 536, in do_execute
      cursor.execute(statement, parameters)
    File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 170, in execute
      result = self._query(query)
    File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 328, in _query
      conn.query(q)
    File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 517, in query
      self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 732, in _read_query_result
      result.read()
    File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1075, in read
      first_packet = self.connection._read_packet()
    File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 684, in _read_packet
      packet.check_error()
    File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 220, in check_error
      err.raise_mysql_exception(self._data)
    File "/usr/lib/python3/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
      raise errorclass(errno, errval)
  pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'member VARCHAR(255) NOT NULL, \n\tcan_share BOOL NOT NULL, \n\tcreated_at DATETIME N' at line 4")

  The above exception was the direct cause of the following exception:

  Traceback (most recent call last):
    File "/usr/bin/glance-manage", line 10, in <module>
      sys.exit(main())
    File "/usr/lib/python3/dist-packages/glance/cmd/manage.py", line 563, in main
      return CONF.command.action_fn()
    File "/usr/lib/python3/dist-packages/glance/cmd/manage.py", line 395, in sync
      self.command_object.sync(CONF.command.version)
    File "/usr/lib/python3/dist-packages/glance/cmd/manage.py", line 165, in sync
      self.expand(online_migration=False)
    File "/usr/lib/python3/dist-packages/glance/cmd/manage.py", line 222, in expand
      self._sync(version=expand_head)
    File "/usr/lib/python3/dist-packages/glance/cmd/manage.py", line 180, in _sync
      alembic_command.upgrade(a_config, version)
    File "/usr/lib/python3/dist-packages/alembic/command.py", line 254, in upgrade
      script.run_env()
    File "/usr/lib/python3/dist-packages/alembic/script/base.py", line 427, in run_env
      util.load_python_file(self.dir, 'env.py')
    File "/usr/lib/python3/dist-packages/alembic/util/pyfiles.py", line 81, in load_python_file
      module = load_module_py(module_id, path)
    File "/usr/lib/python3/dist-packages/alembic/util/compat.py", line 82, in load_module_py
      spec.loader.exec_module(module)
    File "<frozen importlib._bootstrap_external>", line 728, in exec_module
    File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
    File "/usr/lib/python3/dist-packages/glance/db/sqlalchemy/alembic_migrations/env.py", line 88, in <module>
      run_migrations_online()
    File "/usr/lib/python3/dist-packages/glance/db/sqlalchemy/alembic_migrations/env.py", line 83, in run_migrations_online
      context.run_migrations()
    File "<string>", line 8, in run_migrations
    File "/usr/lib/python3/dist-packages/alembic/runtime/environment.py", line 836, in run_migrations
      self.get_context().run_migrations(**kw)
    File "/usr/lib/python3/dist-packages/alembic/runtime/migration.py", line 330, in run_migrations
      step.migration_fn(**kw)
    File "/usr/lib/python3/dist-packages/glance/db/sqlalchemy/alembic_migrations/versions/liberty_initial.py", line 37, in upgrade
      add_images_tables.upgrade()
    File "/usr/lib/python3/dist-packages/glance/db/sqlalchemy/alembic_migrations/add_images_tables.py", line 200, in upgrade
      _add_image_members_table()
    File "/usr/lib/python3/dist-packages/glance/db/sqlalchemy/alembic_migrations/add_images_tables.py", line 155, in _add_image_members_table
      extend_existing=True)
    File "<string>", line 8, in create_table
    File "<string>", line 3, in create_table
    File "/usr/lib/python3/dist-packages/alembic/operations/ops.py", line 1120, in create_table
      return operations.invoke(op)
    File "/usr/lib/python3/dist-packages/alembic/operations/base.py", line 319, in invoke
      return fn(self, operation)
    File "/usr/lib/python3/dist-packages/alembic/operations/toimpl.py", line 101, in create_table
      operations.impl.create_table(table)
    File "/usr/lib/python3/dist-packages/alembic/ddl/impl.py", line 190, in create_table
      self._exec(schema.CreateTable(table))
    File "/usr/lib/python3/dist-packages/alembic/ddl/impl.py", line 115, in _exec
      return conn.execute(construct, *multiparams, **params)
    File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 980, in execute
      return meth(self, multiparams, params)
    File "/usr/lib/python3/dist-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
      return connection._execute_ddl(self, multiparams, params)
    File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1042, in _execute_ddl
      compiled,
    File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1240, in _execute_context
      e, statement, parameters, cursor, context
    File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1456, in _handle_dbapi_exception
      util.raise_from_cause(newraise, exc_info)
    File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 296, in raise_from_cause
      reraise(type(exception), exception, tb=exc_tb, cause=cause)
    File "/usr/lib/python3/dist-packages/sqlalchemy/util/compat.py", line 276, in reraise
      raise value.with_traceback(tb)
    File "/usr/lib/python3/dist-packages/sqlalchemy/engine/base.py", line 1236, in _execute_context
      cursor, statement, parameters, context
    File "/usr/lib/python3/dist-packages/sqlalchemy/engine/default.py", line 536, in do_execute
      cursor.execute(statement, parameters)
    File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 170, in execute
      result = self._query(query)
    File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 328, in _query
      conn.query(q)
    File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 517, in query
      self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 732, in _read_query_result
      result.read()
    File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1075, in read
      first_packet = self.connection._read_packet()
    File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 684, in _read_packet
      packet.check_error()
    File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 220, in check_error
      err.raise_mysql_exception(self._data)
    File "/usr/lib/python3/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
      raise errorclass(errno, errval)
  oslo_db.exception.DBError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'member VARCHAR(255) NOT NULL, \n\tcan_share BOOL NOT NULL, \n\tcreated_at DATETIME N' at line 4") [SQL: "\nCREATE TABLE image_members (\n\tid INTEGER NOT NULL AUTO_INCREMENT, \n\timage_id VARCHAR(36) NOT NULL, \n\tmember VARCHAR(255) NOT NULL, \n\tcan_share BOOL NOT NULL, \n\tcreated_at DATETIME NOT NULL, \n\tupdated_at DATETIME, \n\tdeleted_at DATETIME, \n\tdeleted BOOL NOT NULL, \n\tstatus VARCHAR(20) NOT NULL DEFAULT 'pending', \n\tPRIMARY KEY (id), \n\tFOREIGN KEY(image_id) REFERENCES images (id), \n\tCONSTRAINT image_members_image_id_member_deleted_at_key UNIQUE (image_id, member, deleted_at), \n\tCHECK (can_share IN (0, 1)), \n\tCHECK (deleted IN (0, 1))\n)CHARSET=utf8 ENGINE=InnoDB\n\n"] (Background on this error at: http://sqlalche.me/e/f405)

  [Root Cause]

  The root cause is that "member" is a reserved word in MySQL 8 [0].
  Glance is attempting to create a table named "member" but is not
  adequately quoting the table name. Reserved words should be back tick
  quoted: `member`

  [Bigger Picture]

  The bigger picture is that all OpenStack databases should back tick
  quote all table names. This may be a bug in oslo.db, sqlalchemy or the
  OpenStack projects themselves.

  [0] https://dev.mysql.com/doc/refman/8.0/en/keywords.html

To manage notifications about this bug go to:
https://bugs.launchpad.net/ubuntu/+source/glance/+bug/1846548/+subscriptions