Troubleshooting foreign key dependency error when setting up new MariaDB replica

Question

Question: Does anyone have advice on what I can do to successfully get this new replica set up and replicating from our production server just like the old replica?

---

Details:

I am trying to set up a new MariaDB replica from an existing replica so that we do not have to take the production server down to create the new replica. Here is the procedure I go through to import the DB to the new host.

1. On the current replica: MariaDB> STOP SLAVE;
2. On the current replica: $ /usr/bin/mysql -u $USER -p $PASS -e 'SHOW SLAVE STATUS \G' > slave_status.txt
3. On the new replica: $ /usr/bin/mysqldump --add-drop-database --all-databases -u $USER -p $PASS -h $CURRENT_REPLICA --flush-privileges | /usr/bin/mysql -u root
4. On the current replica: Make sure that the MariaDB> SHOW SLAVE STATUS \G output is still the same as it was in slave_status.txt
5. Grab the Relay_Master_Log_File, Master_Host, Master_User, and Exec_Master_Log_Pos values from slave_status.txt
6. On the new replica: MariaDB> CHANGE MASTER TO MASTER_HOST='$Master_Host', MASTER_USER='$Master_User', MASTER_PASSWORD='$Master_Password', MASTER_LOG_FILE='$Relay_Master_Log_File', MASTER_LOG_POS=$Exec_Master_Log_Pos;
7. On the new replica: MariaDB> SHOW SLAVE STATUS \G and compare to slave_status.txt... All looks good
8. On the new replica: MariaDB> START SLAVE;

Once the replication has been running for a little bit, usually a few minutes, I get error #1451 below

Error 'Cannot delete or update a parent row: a foreign key constraint fails ('$DATABASE'.'$TABLE', CONSTRAINT '$TABLE_ibfk_3' FOREIGN KEY ('$KEY') REFERENCES '$OTHER_TABLE' ('$OTHER_TABLE_KEY') ON UPDATE CASCADE)' on query. Default database: '$DATABASE'. Query: 'DELETE FROM $OTHER_TABLE WHERE $OTHER_TABLE_KEY in (#########, #########, #########, #########, #########, ...

There are multiple databases hosted in this MariaDB instance and every time I have done this process it is the same database that runs into this foreign key dependency error. The current replica has been running for years without a problem. I have tried the same MariaDB version (10.1.12) on the new host as the old host, as well as a newer version (10.4.17) on the new host. The database which results in this error is using InnoDB.

I have verified that /etc/my.cnf and /etc/my.cnf.d/* are the same except for the server ID for the two replicas. One of the first times I ran through this process I copy-pasted the failed command from the error log and it ran without a problem, I then set the Skip_Counter to 1 and it continued for a while and got the same error. I did this 5 times before giving up on that as a method to proceed.

If there is any other useful information I can provide, please let me know. I tried to include everything I could think of but I wouldn't be surprised if I missed something useful.

Answer 1

There seems to have been some issue regarding timezones with the replicated database. I tried using mysql_tzinfo_to_sql to set up the timezone in the SQL database but that didn't work. So I moved on to trying docker and setting up the timezone at the image level and now I have the replication fully functioning.