Open Stack

hey good news, the owner of the issue upstream found that the SQL
standard agrees with my proposed behavior.   So while this is current
MariaDB 10.2 / 10.3 behavior, hopefully it will be resolved in an
upcoming release within those series.   not sure of the timing though
so we may not be able to duck it.

On Mon, Jul 24, 2017 at 11:16 AM, Michael Bayer <mbayer at redhat.com> wrote:
> 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