keystone-manage db_sync fails with MariaDB 10.3

[Lance Bragstad]

When preparing a new installation using MariaDB 10.3, the keystone-manage db_sync command fails with the following trace [0]:

2018-06-01 09:03:51.902 7052 CRITICAL keystone [-] Unhandled error: DBMigrationError: (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VARCHAR(64)' at line 1") [SQL: u'\nALTER TABLE application_credential ADD system VARCHAR(64)']
2018-06-01 09:03:51.902 7052 ERROR keystone Traceback (most recent call last):
2018-06-01 09:03:51.902 7052 ERROR keystone File "/usr/bin/keystone-manage", line 10, in <module>
2018-06-01 09:03:51.902 7052 ERROR keystone sys.exit(main())
2018-06-01 09:03:51.902 7052 ERROR keystone File "/usr/lib/python2.7/dist-packages/keystone/cmd/manage.py", line 45, in main
2018-06-01 09:03:51.902 7052 ERROR keystone cli.main(argv=sys.argv, config_files=config_files)
2018-06-01 09:03:51.902 7052 ERROR keystone File "/usr/lib/python2.7/dist-packages/keystone/cmd/cli.py", line 1349, in main
2018-06-01 09:03:51.902 7052 ERROR keystone CONF.command.cmd_class.main()
2018-06-01 09:03:51.902 7052 ERROR keystone File "/usr/lib/python2.7/dist-packages/keystone/cmd/cli.py", line 548, in main
2018-06-01 09:03:51.902 7052 ERROR keystone CONF.command.version)
2018-06-01 09:03:51.902 7052 ERROR keystone File "/usr/lib/python2.7/dist-packages/keystone/common/sql/upgrades.py", line 250, in offline_sync_database_to_version
2018-06-01 09:03:51.902 7052 ERROR keystone expand_schema()
2018-06-01 09:03:51.902 7052 ERROR keystone File "/usr/lib/python2.7/dist-packages/keystone/common/sql/upgrades.py", line 325, in expand_schema
2018-06-01 09:03:51.902 7052 ERROR keystone _sync_repo(repo_name=EXPAND_REPO)
2018-06-01 09:03:51.902 7052 ERROR keystone File "/usr/lib/python2.7/dist-packages/keystone/common/sql/upgrades.py", line 187, in _sync_repo
2018-06-01 09:03:51.902 7052 ERROR keystone init_version=init_version, sanity_check=False)
2018-06-01 09:03:51.902 7052 ERROR keystone File "/usr/lib/python2.7/dist-packages/oslo_db/sqlalchemy/migration.py", line 81, in db_sync
2018-06-01 09:03:51.902 7052 ERROR keystone raise exception.DBMigrationError(ex)
2018-06-01 09:03:51.902 7052 ERROR keystone DBMigrationError: (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'VARCHAR(64)' at line 1") [SQL: u'\nALTER TABLE application_credential ADD system VARCHAR(64)']

Version 10.2 doesn't have an issue with the database migrations, but 10.3 does because of a new syntax requirement.

This was brought to our attention in IRC [1].

[0] https://paste.pound-python.org/raw/PtFq4UKF8MGjFAoa19OS/
[1] http://eavesdrop.openstack.org/irclogs/%23openstack-keystone/%23openstack-keystone.2018-06-01.log.html#t2018-06-01T16:29:25

[Lance Bragstad (lbragstad)]

This will likely depend on a new version of sqlalchemy (which doesn't appear to be compatible with MariaDB 10.3)

[Lance Bragstad (lbragstad)]

I don't think we'll be able to target this for Rocky, bumping to Stein.

[Nicholas Reynolds (reynoni)]

This issue has larger impacts in my environment at least.

I'm allowed to add roles to users and projects but when trying to remove them with either the CLI or horizon dashboard, I get the following error in the keystone-wsgi logs:

2018-10-16 16:00:02.408 11845 ERROR keystone.common.wsgi DBError: (pymysql.err.InternalError) (1054, u"Unknown column 'application_credential.unrestricted' in 'field list'") [SQL: u'SELECT application_credential.internal_id AS application_credential_internal_id, application_credential.id AS application_credential_id, application_credential.name AS application_credential_name, application_credential.secret_hash AS application_credential_secret_hash, application_credential.description AS application_credential_description, application_credential.user_id AS application_credential_user_id, application_credential.project_id AS application_credential_project_id, application_credential.system AS application_credential_system, application_credential.expires_at AS application_credential_expires_at, application_credential.unrestricted AS application_credential_unrestricted \nFROM application_credential \nWHERE application_credential.project_id = %(project_id_1)s AND application_credential.user_id = %(user_id_1)s'] [parameters: {u'project_id_1': u'28b6e5170c1444b29d0aa95b656f95a1', u'user_id_1': u'36656624730b4a3bbe843b3f6134720e'}]

Workaround for me was to create any missing fields in that table, however, I know that this is not an idea solution (considering I don't know what the data type is for the unrestricted field).

I had the same error as above but for application_credential.system previously.

[Carlos Goncalves (cgoncalves)]

FYI, as I encountered a similar issue but turned out to be devstack doing (stable/queens, CentOS 7).

The problem for me is that devstack installs package rdo-release from https://rdoproject.org/repos/rdo-release.rpm which redirects to latest stable, Stein at present, and where MariaDB 10.3.z is available and gets installed.

[vagrant@localhost devstack]$ git rev-parse HEAD
e3100794d1615fc6b4216f54e2f7859a33d60b05

vagrant@localhost devstack]$ rpm -qa | grep maria
mariadb-config-10.3.10-1.el7.0.0.rdo2.x86_64
mariadb-devel-10.3.10-1.el7.0.0.rdo2.x86_64
mariadb-10.3.10-1.el7.0.0.rdo2.x86_64
mariadb-server-10.3.10-1.el7.0.0.rdo2.x86_64
mariadb-common-10.3.10-1.el7.0.0.rdo2.x86_64
mariadb-errmsg-10.3.10-1.el7.0.0.rdo2.x86_64
mariadb-libs-10.3.10-1.el7.0.0.rdo2.x86_64

vagrant@localhost devstack]$ ./stack.sh
[...]
INFO migrate.versioning.api [-] done
INFO migrate.versioning.api [-] 32 -> 33...
INFO migrate.versioning.api [-] done
INFO migrate.versioning.api [-] 33 -> 34...
INFO migrate.versioning.api [-] done
INFO migrate.versioning.api [-] 34 -> 35...
ERROR oslo_db.sqlalchemy.exc_filters [-] DBAPIError exception wrapped from (pymysql.err.ProgrammingError) (1064, u"You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax
 to use near 'VARCHAR(64)' at line 1") [SQL: u'\nALTER TABLE application_credential ADD system VARCHAR(64)'] (Background on this error at: http://sqlalche.me/e/f405): ProgrammingError: (1064, u"You have an error in your SQL syntax; check
the manual that corresponds to your MariaDB server version for the right syntax to use near 'VARCHAR(64)' at line 1")
ERROR oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):
ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context
ERROR oslo_db.sqlalchemy.exc_filters context)
ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib64/python2.7/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute
ERROR oslo_db.sqlalchemy.exc_filters cursor.execute(statement, parameters)
ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 165, in execute
ERROR oslo_db.sqlalchemy.exc_filters result = self._query(query)
ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/cursors.py", line 321, in _query
ERROR oslo_db.sqlalchemy.exc_filters conn.query(q)
ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 860, in query
ERROR oslo_db.sqlalchemy.exc_filters self._affected_rows = self._read_query_result(unbuffered=unbuffered)
ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1061, in _read_query_result
ERROR oslo_db.sqlalchemy.exc_filters result.read()
ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/site-packages/pymysql/connections.py", line 1349, in read
ERROR oslo_db.sqlalchemy.exc_filters first_packet = self.connection._read_packet()
ERROR oslo_db.sqlalchemy.exc_filters File "/usr/lib/python2.7/sit...

[Carlos Goncalves (cgoncalves)]

Also FYI, bug report for the issue in my previous comment: https://bugs.launchpad.net/devstack/+bug/1833696

[Olaf Seibert (oseibert-sys11)]

@lbragstad: I created a very hacky workaround for the original problem:

/usr/lib/python2.7/dist-packages# diff -u ./sqlalchemy/dialects/mysql/base.py.bak ./sqlalchemy/dialects/mysql/base.py
--- ./sqlalchemy/dialects/mysql/base.py.bak 2021-09-16 16:21:31.863078338 +0000
+++ ./sqlalchemy/dialects/mysql/base.py 2021-09-16 16:19:43.935195678 +0000
@@ -982,7 +982,7 @@
         """Builds column DDL."""

         colspec = [
- self.preparer.format_column(column),
+ "`"+self.preparer.format_column(column)+"`",
             self.dialect.type_compiler.process(
                 column.type, type_expression=column)
         ]

After the keystone-manage db_sync command it's likely wise to undo this patch, since I don't trust that it works properly for all queries.