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?
Best Answer
Just found out that this was a bug that has been fixed in the next release, MySQL 5.7.11.
Extract from the changelog:
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:
mysqldump
as usualFULLTEXT KEY
linesALTER TABLE
statements to add each full-text index I had in my notepad back to its tableThis worked perfectly well and my tables are fully in sync now.