Alembic: MySQL 8.0.0 ER_DEPENDENT_BY_CHECK_CONSTRAINT error

[stephenfin]

Describe the bug

Apologies if this has already been resolved, but I couldn't find references to it anywhere. I'm attempting to install a project, Barbican, that uses alembic for migrations. One of the migrations contains this statement:

op.alter_column('secret_acls', 'creator_only', existing_type=sa.BOOLEAN(),
                new_column_name='project_access')

This is generating the following SQL:

ALTER TABLE secret_acls CHANGE creator_only project_access BOOL NOT NULL;

which is yielding the following error:

ERROR 3959 (HY000): Check constraint 'secret_acls_chk_2' uses column 'creator_only', hence column cannot be dropped or renamed.

This appears to be a compatibility issue due to MySQL 8.0, as this error was introduced in MySQL 8.0.19.

Expected behavior

I suspect the constraint needs to be dropped and recreated.

To Reproduce

The issue is seen when deploying OpenStack using DevStack with the Barbican DevStack plugin. I am working on a minimal reproducer and will share if/when I am successful.

Error

2020-05-27 15:11:20.289 INFO alembic.runtime.migration [-] Running upgrade 30dba269cc64 -> 6a4457517a3, rename ACL creator_only to project_access                                                                  
2020-05-27 15:11:20.292 WARNING oslo_db.sqlalchemy.exc_filters [-] DBAPIError exception wrapped.: pymysql.err.InternalError: (3959, "Check constraint 'secret_acls_chk_2' uses column 'creator_only', hence column cannot be dropped or renamed.")                                                                                                                                                                                    
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters Traceback (most recent call last):                                                                                                                    
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1283, in _execute_context                                             
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters     self.dialect.do_execute(                                                                                                                          
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 590, in do_execute
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters     cursor.execute(statement, parameters)
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python3.8/dist-packages/pymysql/cursors.py", line 170, in execute
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters     result = self._query(query)
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python3.8/dist-packages/pymysql/cursors.py", line 328, in _query
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters     conn.query(q)
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 517, in query
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters     self._affected_rows = self._read_query_result(unbuffered=unbuffered)
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 732, in _read_query_result
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters     result.read()
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 1075, in read
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters     first_packet = self.connection._read_packet()
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python3.8/dist-packages/pymysql/connections.py", line 684, in _read_packet
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters     packet.check_error()
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python3.8/dist-packages/pymysql/protocol.py", line 220, in check_error
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters     err.raise_mysql_exception(self._data)
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters   File "/usr/local/lib/python3.8/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters     raise errorclass(errno, errval)
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters pymysql.err.InternalError: (3959, "Check constraint 'secret_acls_chk_2' uses column 'creator_only', hence column cannot be dropped or renamed.")
2020-05-27 15:11:20.292 TRACE oslo_db.sqlalchemy.exc_filters 
ERROR: (pymysql.err.InternalError) (3959, "Check constraint 'secret_acls_chk_2' uses column 'creator_only', hence column cannot be dropped or renamed.")
[SQL: ALTER TABLE secret_acls CHANGE creator_only project_access BOOL NULL]

Versions.

• OS: Ubuntu 20.04
• Python: 3.8.2
• Alembic: 1.4.2
• SQLAlchemy: 1.3.17
• Database: MySql 8.0.20-0ubuntu0.20.04.1
• DBAPI: pymysql 0.9.3

Additional context

None.

[stephenfin]

In case it's useful, here's the schema before attempting to apply the migration.

mysql> SHOW CREATE TABLE secret_acls;

CREATE TABLE `secret_acls` (
  `id` varchar(36) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `deleted` tinyint(1) NOT NULL,
  `status` varchar(20) NOT NULL,
  `secret_id` varchar(36) NOT NULL,
  `operation` varchar(255) NOT NULL,
  `creator_only` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `_secret_acl_operation_uc` (`secret_id`,`operation`),
  KEY `ix_secret_acls_secret_id` (`secret_id`),
  CONSTRAINT `secret_acls_ibfk_1` FOREIGN KEY (`secret_id`) REFERENCES `secrets` (`id`),
  CONSTRAINT `secret_acls_chk_1` CHECK ((`deleted` in (0,1))),
  CONSTRAINT `secret_acls_chk_2` CHECK ((`creator_only` in (0,1)))
) ENGINE=InnoDB DEFAULT CHARSET=utf8

[jvanasco]

This is related to #652 ("track if we decide we will try to DROP the CHECK constraints generated for boolean and enum"), which grew out of #605 ("detection of boolean -> TINYINT").

[stephenfin]

Got a minimal reproducer.

First off, create a working database:

mkdir /tmp/issue699 && cd /tmp/issue699
mysql -u root -ppassword -e "CREATE DATABASE issue699"

Create a simple model:

import sqlalchemy as sa
from sqlalchemy.ext import declarative

BASE = declarative.declarative_base()


class SecretACL(BASE):
    __tablename__ = 'secret_acls'

    id = sa.Column(sa.Integer, primary_key=True)

    operation = sa.Column(sa.String(255), nullable=False)
    creator_only = sa.Column(sa.Boolean, nullable=False, default=True)


if __name__ == '__main__':
    engine = sa.create_engine("mysql://root:password@localhost/issue699", echo=True)
    BASE.metadata.create_all(engine

Initialize migrations infrastructure:

alembic init migration
crudini --set alembic.ini alembic sqlalchemy.uri mysql://root:password@localhost/issue699
alembic revision -m "rename column"

Populate the migration by editing upgrade in the created migration to the following:

def upgrade():
    op.alter_column('secret_acls', 'creator_only', existing_type=sa.BOOLEAN(),
                    new_column_name='project_access')

Now create the database and attempt the migration.

python3 main.py
alembic upgrade head

It will fail with the same error seen above.

INFO  [alembic.runtime.migration] Context impl MySQLImpl.                                                                                                                                                          
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.                                                                                                                                               
INFO  [alembic.runtime.migration] Running upgrade  -> 5c274c8e577f, rename column                                                                                                                                  
Traceback (most recent call last):                                                                                                                                                                                 
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1283, in _execute_context                                                                                                          
    self.dialect.do_execute(                                                                                                                                                                                       
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 590, in do_execute                                                                                                              
    cursor.execute(statement, parameters)                                                                                                                                                                          
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 209, in execute                                                                                                                                   
    res = self._query(query)                                                                                                                                                                                       
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 315, in _query                                                                                                                                    
    db.query(q)                                                                                                                                                                                                    
  File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 226, in query                                                                                                                                 
    _mysql.connection.query(self, query)                                                                                                                                                                           
MySQLdb._exceptions.OperationalError: (3959, "Check constraint 'secret_acls_chk_1' uses column 'creator_only', hence column cannot be dropped or renamed.")

The above exception was the direct cause of the following exception:                                                                                                                                               

Traceback (most recent call last):                                                                                                                                                                                 
  File "/usr/local/bin/alembic", line 8, in <module>                                                                                                                                                               
    sys.exit(main())                                                                                                                                                                                               
  File "/usr/local/lib/python3.8/dist-packages/alembic/config.py", line 577, in main                                                                                                                               
    CommandLine(prog=prog).main(argv=argv)                                                                                                                                                                         
  File "/usr/local/lib/python3.8/dist-packages/alembic/config.py", line 571, in main                                                                                                                               
    self.run_cmd(cfg, options)                                                                                                                                                                                     
  File "/usr/local/lib/python3.8/dist-packages/alembic/config.py", line 548, in run_cmd                                                                                                                            
    fn(                                                                                                                                                                                                            
  File "/usr/local/lib/python3.8/dist-packages/alembic/command.py", line 298, in upgrade                                                                                                                           
    script.run_env()                                                                                                                                                                                               
  File "/usr/local/lib/python3.8/dist-packages/alembic/script/base.py", line 489, in run_env                                                                                                                       
    util.load_python_file(self.dir, "env.py")                                                                                                                                                                      
  File "/usr/local/lib/python3.8/dist-packages/alembic/util/pyfiles.py", line 98, in load_python_file                                                                                                              
    module = load_module_py(module_id, path)                                                                                                                                                                       
  File "/usr/local/lib/python3.8/dist-packages/alembic/util/compat.py", line 184, in load_module_py                                                                                                                
    spec.loader.exec_module(module)                                                                                                                                                                                
  File "<frozen importlib._bootstrap_external>", line 783, in exec_module                                                                                                                                          
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed                                                                                                                                     
  File "migration/env.py", line 77, in <module>                                                                                                                                                                    
    run_migrations_online()
  File "migration/env.py", line 71, in run_migrations_online
    context.run_migrations()
  File "<string>", line 8, in run_migrations
  File "/usr/local/lib/python3.8/dist-packages/alembic/runtime/environment.py", line 846, in run_migrations
    self.get_context().run_migrations(**kw)
  File "/usr/local/lib/python3.8/dist-packages/alembic/runtime/migration.py", line 520, in run_migrations 
    step.migration_fn(**kw)
  File "/home/ubuntu/alembic-bug/migration/versions/5c274c8e577f_rename_column.py", line 20, in upgrade
    op.alter_column('secret_acls', 'creator_only', existing_type=sa.BOOLEAN(),
  File "<string>", line 8, in alter_column
  File "<string>", line 3, in alter_column
  File "/usr/local/lib/python3.8/dist-packages/alembic/operations/ops.py", line 1779, in alter_column
    return operations.invoke(alt)
  File "/usr/local/lib/python3.8/dist-packages/alembic/operations/base.py", line 374, in invoke
    return fn(self, operation)
  File "/usr/local/lib/python3.8/dist-packages/alembic/operations/toimpl.py", line 43, in alter_column
    operations.impl.alter_column(
  File "/usr/local/lib/python3.8/dist-packages/alembic/ddl/mysql.py", line 51, in alter_column
    self._exec(
  File "/usr/local/lib/python3.8/dist-packages/alembic/ddl/impl.py", line 140, in _exec
    return conn.execute(construct, *multiparams, **params)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1020, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/sql/ddl.py", line 72, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1077, in _execute_ddl
    ret = self._execute_context(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1323, in _execute_context
    self._handle_dbapi_exception(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1517, in _handle_dbapi_exception
    util.raise_(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/base.py", line 1283, in _execute_context
    self.dialect.do_execute(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/engine/default.py", line 590, in do_execute
    cursor.execute(statement, parameters)
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 226, in query
    _mysql.connection.query(self, query)
sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (3959, "Check constraint 'secret_acls_chk_1' uses column 'creator_only', hence column cannot be dropped or renamed.")
[SQL: ALTER TABLE secret_acls CHANGE creator_only project_access BOOL NULL]
(Background on this error at: http://sqlalche.me/e/e3q8)

[zzzeek]

alembic is unlikely to automate this, at the very least we may add directives to specify the constraint to be dropped. Barbican has to make a change here regardless, the best is just to drop the boolean constraint altogether and ensure the Boolean() datatype has "create_constraint=False" on it - these boolean constraints aren't really worth it.

[zzzeek]

yeah this is not really too different from #652 and they would be resolved at the same time. this one is related to the name so a different test case would be present.

[jvanasco]

@zzzeek this is also somewhat related to https://github.com/sqlalchemy/sqlalchemy/issues/4784 , in that Boolean constraints often create too many problems.

@stephenfin I'm not sure how much you know about Alembic/SqlAlchemy, so I'll try to dump some info here to ensure you know what @zzzeek is talking about above.

On a handful of database backends, SQLAlchemy emulates a BOOLEAN column by using an INT datatype with a CHECK CONSTRAINT to ensure a 1 or 0.

The constraint's name might have been auto-generated or might have been specified by the SQLAlchemy or Alembic creation routines. If it were auto-generated, while SQLAlchemy has defaults, they could have been overridden by user-configurable templating options (via the MetaData(naming_convention=convention)). In order for Alembic to handle this situation natively, it would require a lot of guesswork and assumptions on interpreting existing constraints.

Right now, it's really unlikely that Alembic will handle this. For added context on this being unlikely, @zzzeek is an OpenStack developer and would probably be the most motivated person to try and make this happen.

The best option right now, is to file a ticket with Barbican for them to take @zzzeek's advice and drop the constraint in the migration or the initial setups.

[zzzeek]

Alembic will at most handle it by adding some keywords to the op that name the constraint which should be dropped. Alembic can't guess the name of the constraint. if it comes from naming conventions, that part would occur during the autogenerate phase, which means for existing migrations it still needs to be added manually.

[flniu]

alembic is unlikely to automate this, at the very least we may add directives to specify the constraint to be dropped. Barbican has to make a change here regardless, the best is just to drop the boolean constraint altogether and ensure the Boolean() datatype has "create_constraint=False" on it - these boolean constraints aren't really worth it.

Is there a global option for "create_constraint=False"? If we need add this parameter on every Boolean(), that would be a big change on historical code base.

[flniu]

alembic is unlikely to automate this, at the very least we may add directives to specify the constraint to be dropped. Barbican has to make a change here regardless, the best is just to drop the boolean constraint altogether and ensure the Boolean() datatype has "create_constraint=False" on it - these boolean constraints aren't really worth it.

Is there a global option for "create_constraint=False"? If we need add this parameter on every Boolean(), that would be a big change on historical code base.

Set supports_native_boolean=True parameter in create_engine solved my issue. I think non_native_boolean_check_constraint=False is more precise but it is not a valid parameter for MySQLDialect.

My environment:

• percona-server(MySQL): 8.0.17-8
• PyMySQL: 0.10.1
• SQLAlchemy: 1.3.20
• alembic: 1.4.3

[zzzeek]

alembic is unlikely to automate this, at the very least we may add directives to specify the constraint to be dropped. Barbican has to make a change here regardless, the best is just to drop the boolean constraint altogether and ensure the Boolean() datatype has "create_constraint=False" on it - these boolean constraints aren't really worth it.

Is there a global option for "create_constraint=False"? If we need add this parameter on every Boolean(), that would be a big change on historical code base.

you make your own function and change the imports:

from sqlalchemy import Boolean as _Boolean
def Boolean(*arg, **kw):
    kw['create_constraint'] = False
    return _Boolean(*arg, **kw)

then in all relevant modules add:

from myapp.util import Boolean

it might affect a lot of files but it's a simple search and replace.

but if this is a legacy codebase, all the existing databases will have those constraints.

it looks like MySQL 8.0 does in fact support the things that native_boolean does, surprising, here is an example:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1128787
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [test]> SELECT NOT true;
+----------+
| NOT true |
+----------+
|        0 |
+----------+
1 row in set (0.001 sec)

MySQL [test]> SELECT 'x' WHERE true;
+---+
| x |
+---+
| x |
+---+
1 row in set (0.001 sec)

MySQL [test]> SELECT 'x' WHERE false;
Empty set (0.001 sec)

that's new. Here's mariadb 10.3:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 172
Server version: 10.3.22-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [test]> SELECT 'x' WHERE true;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE true' at line 1

so for mysql8 it seems like you're good. older MySQL versions, older MariaDB versions, it will be more risky to set that.

[flniu]

Thank you for your patient reply!

Our existing prod databases are still using MySQL 5.7, no check constraint was created on them. My issue just happened when I initialized new database on MySQL 8.0. So disable creating check constraint seems fine for me.

[flniu]

On both MySQL 5.7 & 8.0, BOOL/BOOLEAN are synonyms for TINYINT(1). A value of zero is considered false. Nonzero values are considered true.

CREATE TABLE t_bool(
    id int NOT NULL PRIMARY KEY,
    flag bool NOT NULL
);

INSERT INTO t_bool(id, flag)
VALUES (0, 0), (1, 1), (2, 2);

-- OK
SELECT * FROM t_bool WHERE flag;
-- OK
SELECT * FROM t_bool WHERE NOT flag;
-- OK
SELECT * FROM t_bool WHERE flag = 0;
-- Not all true values
SELECT * FROM t_bool WHERE flag = 1;

If values other than 0 and 1 are saved in a boolean column, that may cause unexpected result. However, we can use ORM or program logic to ensure valid values. No check constraint is acceptable.

[zzzeek]

just a heads up this issue from an alembic pov is mostly like #652, and the bigger issue was my bad idea to automate and turn on by default CHECK constraints within the Enum and Boolean SQLAlchemy datatypes. SQLAlchemy 1.4 turns off these poorly considered defaults as described at https://docs.sqlalchemy.org/en/14/changelog/migration_14.html#enum-and-boolean-datatypes-no-longer-default-to-create-constraint . so basically the approach is going to be slowly getting people off having these constraints as they are not needed for PostgreSQL or MySQL at all, SQLite tends to be a less formalized database in any case, and for Oracle and SQL Server these are just limitations in those platforms which can be handled with explicit CHECK constraints for those users that want to deal with them.