I am attempting to migrate from MariaDB to MySQL by doing a
mysqldump from MariaDB and then restoring it to MySQL (
mysql:latest docker container).
I am getting the following error when importing into MySQL:
ERROR 3822 (HY000) at line 172: Duplicate check constraint name 'CONSTRAINT_1'.
If I look at the mysqldump file I can see why this is happening. All boolean columns in my database have a constraints that looks something like:
CONSTRAINT `CONSTRAINT_1` CHECK (`bool_col_1` in (0,1)) CONSTRAINT `CONSTRAINT_2` CHECK (`bool_col_2` in (0,1)) CONSTRAINT `CONSTRAINT_3` CHECK (`bool_col_3` in (0,1))
These constraints were not explicitly created by me but implicitly by Flask-SQLAlchemy (I think).
Notice how the constraint names are incremented starting with CONSTRAINT_1. Well the problem is that each table starts incrementing its constraint names starting with CONSTRAINT_1. Thus the error I am seeing gets thrown when trying to create the second table. According to the MySQL docs, duplicate constraint names are not allowed. Apparently MariaDB allows them.
Is there a way to rename these constraints systematically or an alternative way to migrate the data?
The quantity of tables prohibits manually changing the name of each constraint.
Note: This fiddle tests duplicate constraint names and executes without error on MySQL. However if I run the same commands on a fresh MySQL container, it fails with the duplicate constraint.