Open Stack

On Thu, Jul 27, 2017 at 3:23 AM, ChangBo Guo <glongwave at gmail.com> wrote:
> Thanks for the follow up, maybe we need document the issue and work around
> in some place, in alembic?

So yes, there's a whole bunch implied by this:

1. SQLAlchemy knows how to reflect CHECK constraints.  Since MariaDB
supports these as of 10.2, SQLAlchemy itself would need to learn to
read these for MariaDB; on all MySQL/MariaDB versions right now there
is no logic to do this since CHECK constraints are not persisted.

The usual approach taken in Openstack migration scripts when they have
to navigate around constraints is to use inspector / reflection to
read things about constraints and indexes into the Python application,
make decisions, then emit statements as a result.    This approach
would not be possible until SQLAlchemy implements this, or we add
helpers into oslo.db that do something similar.

2. Microsoft SQL Server has this same limitation, e.g. that you can't
drop a column that has a CHECK on it.   Over there, Alembic includes a
feature that generates SQL to detect the name of this constraint and
then drop the constraint of that name.  What is very useful about how
it is done in this case is that it is all within a server-side SQL
script that does the whole locate / drop without any round trips.   If
something similar could be implemented for MySQL/MariaDB, preferably
something that runs without error on all MySQL variants, it would be
just a simple bit of boilerplate that has to run before you drop a
column, and this is the kind of thing we could possibly bundle as a
"mysql_drop_check" flag for the drop_column() operation.

3. if the name of this CHECK constraint is known up front, it's much
easier to just emit "DROP CONSTRAINT xyz".   But it's not clear that
all openstack projects are sending out names for CHECK constraints in
particular the one that sneaks out when the Boolean datatype is used.
 Applying predictable names to the CHECK constraints retroactively of
course means you need to do something like #1 or #2 to find them.

4. *maybe* mariadb's CHECK constraint here already has a predictable
name.   I'd have to get a 10.2 instance running and see what it comes
up with.

>
> 2017-07-24 23:21 GMT+08:00 Michael Bayer <mbayer at redhat.com>:
>>
>> 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
>>
>> __________________________________________________________________________
>> 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
>
>
>
>
> --
> ChangBo Guo(gcb)
>
> __________________________________________________________________________
> 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
>