can't drop CHECK constraint when upgrading from mitaka02 to ocata01

[gnought]

Database: Mariadb 10.2.6
alembic: 0.9.2
SQLAlchemy: 1.0.17
sqlalchemy-migrate: 0.11.0
PyMySQL: 0.7.11
glance: 14.0.0
python-glanceclient: 2.7.0

I started a clean database <glance> and did a "glance-manage db_sync" command.

It faults an error even "check_constraint_checks", "foreign_key_checks" and "unique_checks" are disabled.
InternalError: (pymysql.err.InternalError) (1054, u"Unknown column 'is_public' in 'CHECK'") [SQL: u'ALTER TABLE images DROP COLUMN is_public']

ANALYSIS:

is_public is created by " Column('is_public', Boolean(), nullable=False)" defined in glance.db.sqlalchemy.alembic_migrations.add_images_tables, and the Boolean is defined in glance.db.sqlalchemy.migrate_repo.schema.Boolean that create_constraint=True and Name=None

This logic will create all unnamed CHECK constraint.

To solve the error,
the is_public unnamed CHECK constraint should be dropped before dropping its column.

In ocata01_add_visibility_remove_is_public.py

First attempt:
  op.drop_index('ix_images_is_public', 'images')
+ op.drop_constraint('ck_images_is_public', 'images', type_='check')
  op.drop_column('images', 'is_public')

It fails, as the CHECK constraint is unnamed, and drop_constraint faults "MySQL does not support CHECK constraints." in alembic/ddl/mysql.py line 325

Second attempt:
  op.drop_index('ix_images_is_public', 'images')
+ op.execute("""ALTER TABLE images DROP CONSTRAINT CONSTRAINT_1""")
  op.drop_column('images', 'is_public')

It works but it is nasty as the constraint name "CONSTRAINT_1" is only be found in "SHOW CREATE TABLE images"

It seems that the db upgrade scripts do no support MariaDB well.

Any comments?

[Marcelo Lima (mlimalotic)]

Same problem here

New installation of Glance Ocata
MariaDB: 10.2.7
OS: CentOS 7.3

The workaround was downgrade to MariaDB 10.1.25

Error:
[root@controller01 ~]# su -s /bin/sh -c "glance-manage db_sync" glance
Option "verbose" from group "DEFAULT" is deprecated for removal. Its value may be silently ignored in the future.
/usr/lib/python2.7/site-packages/oslo_db/sqlalchemy/enginefacade.py:1241: OsloDBDeprecationWarning: EngineFacade is deprecated; please use oslo_db.sqlalchemy.enginefacade
  expire_on_commit=expire_on_commit, _conf=conf)
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
INFO [alembic.runtime.migration] Running upgrade liberty -> mitaka01, add index on created_at and updated_at columns of 'images' table
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:3000: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_1` CHECK (`is_public` in (0,1)),'
  util.warn("Unknown schema content: %r" % line)
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:3000: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_2` CHECK (`deleted` in (0,1)),'
  util.warn("Unknown schema content: %r" % line)
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:3000: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_3` CHECK (`protected` in (0,1))'
  util.warn("Unknown schema content: %r" % line)
INFO [alembic.runtime.migration] Running upgrade mitaka01 -> mitaka02, update metadef os_nova_server
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:3000: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_1` CHECK (`protected` in (0,1))'
  util.warn("Unknown schema content: %r" % line)
INFO [alembic.runtime.migration] Running upgrade mitaka02 -> ocata01, add visibility to and remove is_public from images
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:3000: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_1` CHECK (`is_public` in (0,1)),'
  util.warn("Unknown schema content: %r" % line)
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:3000: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_2` CHECK (`deleted` in (0,1)),'
  util.warn("Unknown schema content: %r" % line)
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:3000: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_3` CHECK (`protected` in (0,1))'
  util.warn("Unknown schema content: %r" % line)
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:3000: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_1` CHECK (`can_share` in (0,1)),'
  util.warn("Unknown schema content: %r" % line)
/usr/lib64/python2.7/site-packages/sqlalchemy/dialects/mysql/base.py:3000: SAWarning: Unknown schema content: u' CONSTRAINT `CONSTRAINT_2` CHECK (`deleted` in (0,1))'
  util.warn("Unknown schema content: %r" % line)
CRITI [glance] InternalError: (pymysql.err.InternalError) (1054, u"Unknown column 'is_public' in 'CHECK'") [SQL: u'ALT...

[Brian Rosmaita (brian-rosmaita)]

Looks like mburling has also run into this issue: https://paste.ubuntu.com/25962951/