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 <[email protected]>: > 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 <[email protected]> wrote: > > On Mon, Jul 24, 2017 at 10:37 AM, Doug Hellmann <[email protected]> > 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: [email protected]?subject: > unsubscribe > >> http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev > > __________________________________________________________________________ > OpenStack Development Mailing List (not for usage questions) > Unsubscribe: [email protected]?subject:unsubscribe > http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev > -- ChangBo Guo(gcb)
__________________________________________________________________________ OpenStack Development Mailing List (not for usage questions) Unsubscribe: [email protected]?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev
