Open Stack

Thanks for the follow up, maybe we need document the issue and work around
in some place, in alembic?

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)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstack.org/pipermail/openstack-dev/attachments/20170727/1bd63ace/attachment.html>