Open Stack

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.

[1] https://jira.mariadb.org/browse/MDEV-11114

[2] https://bitbucket.org/zzzeek/alembic/issues/440/cannot-drop-boolean-column-in-mysql