Migrating from MariaDB to Mysql - Duplicate Constraint Name
0
votes
1
answer
917
views
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.
Asked by noslenkwah
(101 rep)
May 27, 2020, 04:43 PM
Last activity: Jul 7, 2025, 12:45 PM
Last activity: Jul 7, 2025, 12:45 PM