Open Stack

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
>