yahoo-eng-team team mailing list archive

[gnought <1697835@xxxxxxxxxxxxxxxxxx>]

Public bug reported:

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?

** Affects: glance
     Importance: Undecided
         Status: New


** Tags: alembic constraint database mariadb mysql ocata sqlalchemy upgrade

-- 
You received this bug notification because you are a member of Yahoo!
Engineering Team, which is subscribed to Glance.
https://bugs.launchpad.net/bugs/1697835

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

Status in Glance:
  New

Bug description:
  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?

To manage notifications about this bug go to:
https://bugs.launchpad.net/glance/+bug/1697835/+subscriptions