MySQL 8.0.0 ER_DEPENDENT_BY_CHECK_CONSTRAINT error #699
Comments
|
In case it's useful, here's the schema before attempting to apply the migration. |
|
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(engineInitialize 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 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 headIt will fail with the same error seen above. |
zzzeek
added
bug
|
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. |
|
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. |
|
@zzzeek this is also somewhat related to sqlalchemy/sqlalchemy#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 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. |
|
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. |
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 My environment:
|
you make your own function and change the imports: then in all relevant modules add: 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: that's new. Here's mariadb 10.3: so for mysql8 it seems like you're good. older MySQL versions, older MariaDB versions, it will be more risky to set that. |
|
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. |
|
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. 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. |
|
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. |
On mysql 8, Boolean fields create constraints which later make it impossible to alter the name of the column. See: sqlalchemy/alembic#699 Per upstream alembic recommendation, do not create constraints explicitly. sqlalchemy/alembic#699 (comment) story: 2008488 task: 41537 Change-Id: I51659c6e179d7e4e2cfc5be46348fac483d76e3b Signed-off-by: Spyros Trigazis <[email protected]>
* Update magnum from branch 'master'
to 8dcf91b2d3f04b7b5cb0e7711d82438b69f975a1
- Merge "Do not create constraints for boolean fields"
- Do not create constraints for boolean fields
On mysql 8, Boolean fields create constraints which later
make it impossible to alter the name of the column.
See: sqlalchemy/alembic#699
Per upstream alembic recommendation, do not create constraints
explicitly.
sqlalchemy/alembic#699 (comment)
story: 2008488
task: 41537
Change-Id: I51659c6e179d7e4e2cfc5be46348fac483d76e3b
Signed-off-by: Spyros Trigazis <[email protected]>
On mysql 8, Boolean fields create constraints which later make it impossible to alter the name of the column. See: sqlalchemy/alembic#699 Per upstream alembic recommendation, do not create constraints explicitly. sqlalchemy/alembic#699 (comment) story: 2008488 task: 41537 Signed-off-by: Spyros Trigazis <[email protected]> (cherry picked from commit bcf771b) Fix database migrations The pattern of adding a column and then reading a table with it no longer works in SQLAlchemy 1.3.20. This has been reported upstream [1]. [1] sqlalchemy/sqlalchemy#5669 squashed with: I5fd1deeef9cf70794bc61c101e1d7d4379d4b96b (cherry picked from commit f5cf6b9) Change-Id: I51659c6e179d7e4e2cfc5be46348fac483d76e3b
On mysql 8, Boolean fields create constraints which later make it impossible to alter the name of the column. See: sqlalchemy/alembic#699 Per upstream alembic recommendation, do not create constraints explicitly. sqlalchemy/alembic#699 (comment) story: 2008488 task: 41537 Signed-off-by: Spyros Trigazis <[email protected]> (cherry picked from commit bcf771b) Fix database migrations The pattern of adding a column and then reading a table with it no longer works in SQLAlchemy 1.3.20. This has been reported upstream [1]. [1] sqlalchemy/sqlalchemy#5669 squashed with: I5fd1deeef9cf70794bc61c101e1d7d4379d4b96b (cherry picked from commit f5cf6b9) Change-Id: I51659c6e179d7e4e2cfc5be46348fac483d76e3b (cherry picked from commit 210984f) Conflicts: magnum/db/sqlalchemy/alembic/versions/1d045384b966_add_insecure_baymodel_attr.py Change-Id: Iba6f68822e4c7219f21ab2da252802dc7c3ca933
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:
This is generating the following SQL:
which is yielding the following error:
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
Versions.
Additional context
None.
The text was updated successfully, but these errors were encountered: