Re: [openstack-dev] [all] [oslo.db] [relational database users] heads up for a MariaDB issue that will affect most projects
On Thu, Jul 27, 2017 at 3:23 AM, ChangBo Guo 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 : >> >> 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 wrote: >> > On Mon, Jul 24, 2017 at 10:37 AM, Doug Hellmann >> > 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
Re: [openstack-dev] [all] [oslo.db] [relational database users] heads up for a MariaDB issue that will affect most projects
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 : > 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 wrote: > > On Mon, Jul 24, 2017 at 10:37 AM, Doug Hellmann > 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-4 > >>> > >>> [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 > -- ChangBo Guo(gcb) __ 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
Re: [openstack-dev] [all] [oslo.db] [relational database users] heads up for a MariaDB issue that will affect most projects
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 wrote: > On Mon, Jul 24, 2017 at 10:37 AM, Doug Hellmann 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-4 >>> >>> [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
Re: [openstack-dev] [all] [oslo.db] [relational database users] heads up for a MariaDB issue that will affect most projects
On Mon, Jul 24, 2017 at 10:37 AM, Doug Hellmann 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-4 >> >> [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
Re: [openstack-dev] [all] [oslo.db] [relational database users] heads up for a MariaDB issue that will affect most projects
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. Doug > > [1] https://jira.mariadb.org/browse/MDEV-4 > > [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-dev] [all] [oslo.db] [relational database users] heads up for a MariaDB issue that will affect most projects
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. [1] https://jira.mariadb.org/browse/MDEV-4 [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
