[openstack-dev] [all] [oslo.db] [relational database users] heads up for a MariaDB issue that will affect most projects

Michael Bayer mbayer at redhat.com
Mon Jul 24 15:16:32 UTC 2017

On Mon, Jul 24, 2017 at 10:37 AM, Doug Hellmann <doug at doughellmann.com> wrote:
> 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.

OK let me give a little bit more context, that if MariaDB holds steady
here, I will have to implement #3 within Alembic itself (though yes,
for SQLAlchemy-migrate, still needed :) ).     MS SQL Server has the
same limitation for CHECK constraints and Alembic provides for a
SQL-only procedure that can run as a static SQL element on that
backend; hopefully the same is possible for MySQL.

> 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: OpenStack-dev-request at lists.openstack.org?subject:unsubscribe
> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev

More information about the OpenStack-dev mailing list