cannot drop Boolean column in mysql/mariadb

[sqlalchemy-bot]

Migrated issue, originally created by raja me (jiangpengcheng)

because of the auto generated CHECK CONSTRAINT is not dropped

# the 'tty' and 'stdin_open' is Boolean
def upgrade():
    with op.batch_alter_table('container', schema=None) as batch_op:
        batch_op.add_column(sa.Column('interactive', sa.Boolean(),
                                      nullable=True))
        batch_op.drop_column('tty')
        batch_op.drop_column('stdin_open')

the error logs is

oslo_db.exception.DBError: (pymysql.err.InternalError) (1054, u"Unknown column 'tty' in 'CHECK'") [SQL: u'ALTER TABLE container DROP COLUMN tty']

I think the Enum column may has the same problem.

is there any documents I have missed?

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

what openstack project is this please?

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

additionally, the above batch_op directive does not specify "recreate='always'" so will not take effect for MySQL.

However, I am unable to reproduce:

#!python

from sqlalchemy import *

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)

m = MetaData()

t = Table(
    'x', m, Column('id', Integer), Column('tty', Boolean()),
    Column('stdin_open', Integer)
)

m.drop_all(e)
m.create_all(e)

from alembic.migration import MigrationContext
from alembic.operations import Operations

conn = e.connect()
ctx = MigrationContext.configure(conn)
op = Operations(ctx)

with op.batch_alter_table('x', schema=None) as batch_op:
    batch_op.add_column(Column('interactive', Boolean(),
                                  nullable=True))
    batch_op.drop_column('tty')
    batch_op.drop_column('stdin_open')

above, batch is turned off for MySQL, and we get:

#!

CREATE TABLE x (
    id INTEGER, 
    tty BOOL, 
    stdin_open INTEGER, 
    CHECK (tty IN (0, 1))
)

2017-07-21 10:04:12,212 INFO sqlalchemy.engine.base.Engine ()
2017-07-21 10:04:12,229 INFO sqlalchemy.engine.base.Engine COMMIT
2017-07-21 10:04:12,333 INFO sqlalchemy.engine.base.Engine ALTER TABLE x ADD COLUMN interactive BOOL
2017-07-21 10:04:12,333 INFO sqlalchemy.engine.base.Engine ()
2017-07-21 10:04:12,355 INFO sqlalchemy.engine.base.Engine COMMIT
2017-07-21 10:04:12,357 INFO sqlalchemy.engine.base.Engine ALTER TABLE x ADD CHECK (interactive IN (0, 1))
2017-07-21 10:04:12,357 INFO sqlalchemy.engine.base.Engine ()
2017-07-21 10:04:12,358 INFO sqlalchemy.engine.base.Engine COMMIT
2017-07-21 10:04:12,358 INFO sqlalchemy.engine.base.Engine ALTER TABLE x DROP COLUMN tty
2017-07-21 10:04:12,358 INFO sqlalchemy.engine.base.Engine ()
2017-07-21 10:04:12,382 INFO sqlalchemy.engine.base.Engine COMMIT
2017-07-21 10:04:12,383 INFO sqlalchemy.engine.base.Engine ALTER TABLE x DROP COLUMN stdin_open
2017-07-21 10:04:12,383 INFO sqlalchemy.engine.base.Engine ()
2017-07-21 10:04:12,408 INFO sqlalchemy.engine.base.Engine COMMIT

no error, this is mariadb-10.1. What is the full MySQL version being tested against?

[sqlalchemy-bot]

raja me (jiangpengcheng) wrote:

Sorry for the late response, the openstack project is zun

I test with your code, but the problem still occur:

CREATE TABLE x (                                                                                                                                                                       [29/170]
        id INTEGER,
        tty BOOL,
        stdin_open INTEGER,
        CHECK (tty IN (0, 1))
)

2017-07-23 07:57:39,432 INFO sqlalchemy.engine.base.Engine ()
2017-07-23 07:57:39,442 INFO sqlalchemy.engine.base.Engine COMMIT
2017-07-23 07:57:39,520 INFO sqlalchemy.engine.base.Engine ALTER TABLE x ADD COLUMN interactive BOOL
2017-07-23 07:57:39,520 INFO sqlalchemy.engine.base.Engine ()
2017-07-23 07:57:39,529 INFO sqlalchemy.engine.base.Engine COMMIT
2017-07-23 07:57:39,530 INFO sqlalchemy.engine.base.Engine ALTER TABLE x ADD CHECK (interactive IN (0, 1))
2017-07-23 07:57:39,530 INFO sqlalchemy.engine.base.Engine ()
2017-07-23 07:57:39,542 INFO sqlalchemy.engine.base.Engine COMMIT
2017-07-23 07:57:39,543 INFO sqlalchemy.engine.base.Engine ALTER TABLE x DROP COLUMN tty
2017-07-23 07:57:39,543 INFO sqlalchemy.engine.base.Engine ()
2017-07-23 07:57:39,545 INFO sqlalchemy.engine.base.Engine ROLLBACK
......
......
......
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1054, "Unknown column 'tty' in 'CHECK'") [SQL: u'ALTER TABLE x DROP COLUMN tty']

and the MariaDB version is

mysql Ver 15.1 Distrib 10.3.0-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2 (running in a container)

[sqlalchemy-bot]

raja me (jiangpengcheng) wrote:

I test with mariadb-10.1(also in docker container), it works well.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

it's mariadb 10.2 bug and I disagree with their conclusion here: https://jira.mariadb.org/browse/MDEV-11114 however I'm not hopeful they'll change their decision. for now you need to drop the constraint explicitly.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

Enum shouldn't normally have this problem on MySQL because we use MySQL's native ENUM by default.

[sqlalchemy-bot]

raja me (jiangpengcheng) wrote:

ok, I get it now, and thanks a lot for your help.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

Duplicate of #232.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

this will need to be addressed by providing for CHECK constraint reflection in SQLAlchemy along with recipes for manually locating and dropping these constraints within an alembic migration; there's no way to embed a server-side SQL phrase that will handle locating and dropping these constraints because they are not fully present in information schema (at the column level) and can only be seen by parsing SHOW CREATE TABLE. total crapshow on mariadb's part.

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

mariadb 10.2.8 fixes this, and mariadb 10.2.9 fixes an additional issue I reported regarding CHECK. SQLAlchemy will warn for mariadb 10.2 < 9 in https://bitbucket.org/zzzeek/sqlalchemy/issues/4097/mariadb-1029-is-ready

[sqlalchemy-bot]

Michael Bayer (zzzeek) wrote:

fixed upstream

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek):

• added labels: duplicate
• changed status to closed

[sqlalchemy-bot]

Changes by Michael Bayer (zzzeek):

• removed labels: duplicate
• changed status to reopened