Open Stack

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