0

I cannot get mysqldump to generate a dump that will properly restore. The dump is being generated to setup a new slave, but whenever the slave starts up, it gets foreign key constraint failures.

I'm generating the dump with slave positions and as a single transaction so that the dump is (allegedly) atomic, but when I essentially execute the following:

mysqldump \
    -h master
    --flush-logs --single-transaction --master-data \
    --apply-slave-statements \
    --databases my-db \
    | mysql -h slave

I get the following error when I "SHOW SLAVE STATUS":

Last_Errno: 1452
Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails ...

All of the tables use the InnoDB engine, and the foreign key is an integer.

Using MariaDB 10.3.

Rich Remer
  • 205
  • 2
  • 8
  • consider moving to dba.stackexchange.com – Rick James Dec 04 '19 at 20:07
  • I've done so. Thanks. Is there some way of linking the two questions? https://dba.stackexchange.com/questions/254829/mysqldump-generating-inconsistent-dumps – Rich Remer Dec 04 '19 at 22:13
  • 1
    try SET FOREIGN_KEY_CHECKS=0 before doing operation reference: https://stackoverflow.com/questions/5005388/cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails – asktyagi Dec 05 '19 at 06:28
  • Unless I'm mistaken, disabling foreign key checks will simply cause invalid data to be written. – Rich Remer Dec 05 '19 at 19:45

0 Answers0