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.

  • Is there a way to rename these constraints systematically Of course. For example, create your own pattern. It can be, for example, CONSTRAINT_tablename_digit... – Akina May 27 '20 at 17:03
  • @Akina - Perhaps I should have clarified. There are a large number of table that have these constraints such that manually changing them is not a reasonable option. Is there a way to rename them without manually changing each one? – noslenkwah May 27 '20 at 17:08
  • 1
    There is no such service in MySQL. But you may parse dump file, find all CREATE TABLE statements and rename their constraints. – Akina May 27 '20 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.