[Bug 1846548] Re: sqlalchemy fails to autoquote reserved words with MySQL 8

James Page james.page at ubuntu.com
Fri Oct 4 09:57:43 UTC 2019 

uploads to the UNAPPROVED queue in eoan completed for glance and
sqlalchemy - great if the release team could review.

Thanks!

** Summary changed:

- sqlalchemy fails to autoquote reserved works with MySQL 8
+ sqlalchemy fails to autoquote reserved words with MySQL 8

** Changed in: glance (Ubuntu)
       Status: Triaged => In Progress

** Changed in: glance (Ubuntu)
     Assignee: (unassigned) => James Page (james-page)

-- 
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:
  sqlalchemy fails to autoquote reserved words with MySQL 8

Status in glance package in Ubuntu:
  In Progress
Status in sqlalchemy package in Ubuntu:
  In Progress

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".
  2019-10-03 18:13:33.013 23795 INFO alembic.runtime.migration [-] Context impl MySQLImpl.
  2019-10-03 18:13:33.014 23795 INFO alembic.runtime.migration [-] Will assume non-transactional DDL.
  2019-10-03 18:13:33.025 23795 INFO alembic.runtime.migration [-] Context impl MySQLImpl.
  2019-10-03 18:13:33.025 23795 INFO alembic.runtime.migration [-] Will assume non-transactional DDL.
  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

More information about the Ubuntu-openstack-bugs mailing list