Excerpts from Michael Bayer's message of 2017-07-23 16:39:20 -0400: > Hey list - > > It appears that MariaDB as of version 10.2 has made an enhancement > that overall is great and fairly historic in the MySQL community, > they've made CHECK constraints finally work. For all of MySQL's > existence, you could emit a CREATE TABLE statement that included CHECK > constraint, but the CHECK phrase would be silently ignored; there are > no actual CHECK constraints in MySQL. > > Mariadb 10.2 has now made CHECK do something! However! the bad news! > They have decided that the CHECK constraint against a single column > should not be implicitly dropped if you drop the column [1]. In case > you were under the impression your SQLAlchemy / oslo.db project > doesn't use CHECK constraints, if you are using the SQLAlchemy Boolean > type, or the "ENUM" type without using MySQL's native ENUM feature > (less likely), there's a simple CHECK constraint in there. > > So far the Zun project has reported the first bug on Alembic [2] that > they can't emit a DROP COLUMN for a boolean column. In [1] I've > made my complete argument for why this decision on the MariaDB side is > misguided. However, be on the lookout for boolean columns that can't > be DROPPED on some environments using newer MariaDB. Workarounds for > now include: > > 1. when using Boolean(), set create_constraint=False > > 2. when using Boolean(), make sure it has a "name" to give the > constraint, so that later you can DROP CONSTRAINT easily > > 3. if not doing #1 and #2, in order to drop the column you need to use > the inspector (e.g. from sqlalchemy import inspect; inspector = > inspect(engine)) and locate all the CHECK constraints involving the > target column, and then drop them by name.
Item 3 sounds like the description of a helper function we could add to oslo.db for use in migration scripts. Doug > > [1] https://jira.mariadb.org/browse/MDEV-11114 > > [2] > https://bitbucket.org/zzzeek/alembic/issues/440/cannot-drop-boolean-column-in-mysql > __________________________________________________________________________ OpenStack Development Mailing List (not for usage questions) Unsubscribe: [email protected]?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
