On Mon, Jul 24, 2017 at 10:37 AM, Doug Hellmann <d...@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-requ...@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-requ...@lists.openstack.org?subject:unsubscribe http://lists.openstack.org/cgi-bin/mailman/listinfo/openstack-dev