Bug 1734424 - RDO sqlalchemy fail to work with MySQL 8.0.17+
Summary: RDO sqlalchemy fail to work with MySQL 8.0.17+
Alias: None
Product: RDO
Classification: Community
Component: distribution
Version: unspecified
Hardware: x86_64
OS: Linux
Target Milestone: ---
: trunk
Assignee: Alfredo Moralejo
QA Contact: Shai Revivo
Depends On:
TreeView+ depends on / blocked
Reported: 2019-07-30 14:01 UTC by Nikita Gerasimov
Modified: 2020-07-23 10:28 UTC (History)
2 users (show)

Fixed In Version:
Doc Type: If docs needed, set a value
Doc Text:
Clone Of:
Last Closed: 2020-07-23 10:28:46 UTC

Attachments (Terms of Use)

System ID Private Priority Status Summary Last Updated
Github sqlalchemy sqlalchemy issues 4783 0 None closed ARRAY and MEMBER are new MySQL RESERVED_WORDS 2020-07-28 08:51:03 UTC
Launchpad 1837655 0 None None None 2019-07-30 14:01:52 UTC

Description Nikita Gerasimov 2019-07-30 14:01:52 UTC
Description of problem:
MySQL 8.0.17 add new Reserved keywords. OpenStack glance can't work with DB because of that.

Version-Release number of selected component (if applicable):
python2-sqlalchemy-1.2.17-2.el7.x86_64 and 1.2.7-1.el7	

How reproducible:

Steps to Reproduce:
1. Install and configure MySQL 8.0.17 or newer
2. Try `glance-manage --config-file /etc/glance/glance-api.conf db_sync`

Actual results:
DBError: (pymysql.err.ProgrammingError) (1064, u"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: u"\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)ENGINE=InnoDB CHARSET=utf8\n\n"] (Background on this error at: http://sqlalche.me/e/f405)

Expected results:
Pass of `glance-manage`

Additional info:
Requesting update SQLAlchemy to 1.3.7 or backport of https://github.com/sqlalchemy/sqlalchemy/issues/4783

Comment 1 Alfredo Moralejo 2020-07-23 10:28:46 UTC
The version of sqlalchemy included in RDO is intended to be used with the mariadb packages include in RDO. Unfortunatelly, we can't support nor validate deployments with a different database.

Note that in CentOS8 RDO is using the python3-sqlalchemy included in CentOS8 OS repo. It may worth if it works with your desired DB.

Note You need to log in before you can comment on or make changes to this bug.