Bug 1734424

Summary: RDO sqlalchemy fail to work with MySQL 8.0.17+
Product: [Community] RDO Reporter: Nikita Gerasimov <nikita.gerasimov>
Component: distributionAssignee: Alfredo Moralejo <amoralej>
Status: CLOSED WONTFIX QA Contact: Shai Revivo <srevivo>
Severity: unspecified Docs Contact:
Priority: unspecified    
Version: unspecifiedCC: markmc, srevivo
Target Milestone: ---   
Target Release: trunk   
Hardware: x86_64   
OS: Linux   
Whiteboard:
Fixed In Version: Doc Type: If docs needed, set a value
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2020-07-23 10:28:46 UTC Type: Bug
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:

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:
100%

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.