4

I'm setting up replication of an external MySQL instance to RDS.

Both the external MySQL instance and RDS run MySQL 5.7.10.

I've followed the procedure, but when dumping the database with mysqldump and sending it to mysql connected to RDS, I get the following error:

ERROR 1215 (HY000) at line 2081: Cannot add foreign key constraint

SHOW ENGINE InnoDB STATUS on RDS shows:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2016-03-25 01:38:56 0x2ad07ddcf700 Error in foreign key constraint of table db/Prospect:
 FOREIGN KEY (`restaurantId`) REFERENCES `Restaurant` (`id`),
  CONSTRAINT `FK_Prospect_Zone` FOREIGN KEY (`zoneId`) REFERENCES `Zone` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18292 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci:
Cannot resolve table name close to:
 (`id`),
  CONSTRAINT `FK_Prospect_Zone` FOREIGN KEY (`zoneId`) REFERENCES `Zone` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18292 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

OK, indeed the dump file recreates the tables alphabetically, so the Prospect table appears before Restaurant and Zone, which can explain the error.

But NO, because mysqldump adds SET FOREIGN_KEY_CHECKS=0 to the top of the file, so it should work anyway.

So I decided to test it against a vanilla MySQL 5.7 installation, and no surprise, the dump is imported successfully with the exact same commands.

What causes this error specifically on RDS, and how to avoid it?

Improve this question
4
  • Please present the following: SHOW CREATE TABLE Prospect\G, SHOW CREATE TABLE Restaurant\G, and SHOW CREATE TABLE Zone\G
    – RolandoMySQLDBA
    Mar 29, 2016 at 2:15
  • @RolandoMySQLDBA My tables are large, so I trimmed down my dump file to give you a small, reproducible example, but this lead me to find out that this was a known bug (see my answer below). So, thank you for putting me on the right track, in some way :)
    – BenMorel
    Mar 29, 2016 at 11:58
  • +1 for both question and answer. You found your own answer and you will eventually get back the 200 points. Moral of the Story for everybody else ? Read the Release Notes for MySQL (or any other RDBMS) before just installing.
    – RolandoMySQLDBA
    Mar 31, 2016 at 22:21
  • @RolandoMySQLDBA If I'm not mistaken, bounties are never refunded; but it doesn't matter, I've found the solution to my problem, this is the most important thing! Thanks for your comment anyway, I stumble upon your answers on this website regularly, and they do help a lot.
    – BenMorel
    Mar 31, 2016 at 22:45

1 Answer 1

Reset to default
7

Just found out that this was a bug that has been fixed in the next release, MySQL 5.7.11.

Extract from the changelog:

InnoDB: Creating a table with a full-text index and a foreign key constraint failed when foreign_key_checks was disabled. (Bug #22094601, Bug #78955)

It took me time to figure out that the full-text index was the source of the problem, I had to trim down my dump file line by line until the dump could be successfully imported.

Unfortunately RDS does not support MySQL 5.7.11 yet, so I had to work around this issue. Here is what I did:

  • created the dump file with mysqldump as usual
  • opened the dump file and commented out all FULLTEXT KEY lines
  • noted down these lines in notepad
  • imported the dump to the replica as usual
  • connected to the replica, and issued ALTER TABLE statements to add each full-text index I had in my notepad back to its table

This worked perfectly well and my tables are fully in sync now.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.