Sample Header Ad - 728x90

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