Mariadb – MySQL Master-Slave replication foreign key constraint error

mariadbmariadb-10.1replication

This is the cluster setup:

------       ------
| m1 |  ---  | m2 |
------       ------
  ||           ||
------       ------
| s1 |       | s2 |
------       ------

m1 and m2 run mariadb-server-10.1 version 10.1.18+maria-1~jessie.

m1 and s2 run mariadb-server-10.1 version 10.1.18+maria-1~wheezy.

Nodes m1 and m2 are in a Galera Cluster, and replication between these nodes works fine. There is also an arbitrator node running on a separate server.

s1 is a slave node to m1, and s2 is a slave node to m2. The replication to slave nodes is using classic MySQL replication.

Now, the issue here is that the slave nodes needed to be started from fresh state. This was the process to start the replication:

service mysql stop on slave node.
Empty /var/lib/mysql on slave node.
Run innobackupex -u user -p password on master node.
Run innobackupex -u user -p password --apply-log /path/to/backup on the master node.
Copy database backup files to slave server.
Run innobackupex --copy-back /path/to/backup on slave server.
Run chown -r mysql.mysql /var/lib/mysql/* on slave server to fix permissions.
service mysql start on slave server.

After this, I started mysql -u root -p password, and issued this command:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='m1', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mariadb-bin.NNNNNN', MASTER_LOG_POS=nnn;
START SLAVE;

Values for NNNNNN and nnn were retrieved from xtrabackup_binlog_info file from the backup files.

Now, this process worked perfectly between m1 and s1. However, with m2 and s2, it generates an error message (I split the error message to multiple lines for better readability):

Could not execute Write_rows_v1 event on table db.table;
Cannot add or update a child row: a foreign key constraint fails
(`db`.`table`, CONSTRAINT `FK3EBA76781F6AACF1` FOREIGN KEY (`job`)
REFERENCES `table2` (`id`)), Error_code: 1452; handler error
HA_ERR_NO_REFERENCED_ROW; the event's master log mariadb-bin.000710, end_log_pos 1523526

I have tried to repeat the process again a couple of times without any success.

I have also issued RESET MASTER on m2 to reset the replication log file in case it had some corruption.

I am running out of ideas what is happening here and how I can restart replication correctly. Any ideas?

Best Answer

I see that you have used a multi-master configuration for m1 and m2 and a master-slave config for m1-s1 and m2-s2. Currently, the multi-master configuration does not support write actions on the tables with foreign keys. Thus, when there is a write action on either m1 or m2, the error occurs.

test to debug: set up a master-slave config for m1-s1 and m2-s2 without connecting m1 and m2. If both of them work as expected, then you can confirm that the foreign keys issue is due to the multi-master m1-m2 config.

Related Solutions

Mysql – Adding slave to existing master-master config throws foreign key constraint failed error

Given the Following

+<------+
|       ^
V       |
M1      M2
|       ^
V       |
+-------+

and You Want the Following

      +<------+
      |       ^
      V       |
S1<---M1      M2
      |       ^
      V       |
      +-------+

Given Master IP is 10.1.2.30

Logon to the Slave and run the mysqldump like this

MAS_IP=10.1.2.30
SLV_IP=localhost
MYSQL_MAS_CONN="${MAS_IP} -uroot -ppassword"
MYSQL_SLV_CONN="${SLV_IP} -uroot -ppassword"
MYSQLDUMP_OPTIONS="--single-transaction --master-data=1 --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --add-drop-database --all-databases"
mysql ${MYSQL_MAS_CONN} -e"FLUSH LOGS;"
echo "STOP SLAVE;" > MySQLData.sql
mysqldump ${MYSQL_MAS_CONN} ${MYSQLDUMP_OPTIONS} >> MySQLData.sql
echo "START SLAVE;" >> MySQLData.sql
mysql ${MYSQL_MAS_CONN} < MySQLData.sql

If you have already performed this, let me address your comments.

But upon adding the third DB server and starting slave on it, it throws Cannot add or update a child row. foreign key constraint fails error.

Why would foreign key problems happen if you copied the data from a Master to a Slave? The auto_increment ids on the Slave simply did not match that of the Master. This is true even if you the Slave's Master is where you got the data from. Why?

auto_increment_increment is set to 2 in both masters. auto_increment_offset is set to 1 in Master 1 and 2 in Master 2. We have mostly innoDB tables and some MyISAM tables.

You have to make sure the Slave has the exact same auto_increment_increment and auto_increment_offset as the Master you are replicating from.

Goto the Master and run

SHOW VARIABLES LIKE 'auto_increment%';

Put those values into the Slave's my.cnf and restart mysql.

REMEMBER : A Slave's auto_increment behavior needs to be identical to that of its master.

Give it a Try !!!

UPDATE 2013-04-06 17:26

Here is the problem: Let's say you have the setup

      +<------+
      |       ^
      V       |
S1<---M1      M2
      |       ^
      V       |
      +-------+

If you did the following:

mysqldump data from M1
load dump it S1
enabled Replication from M1 to S1
M1 and S1 have identical auto_increment_offset
M1 and S1 have identical auto_increment_increment

there should never be foreign key violations.

What you may need to do is cleanup every server's view of the auto increment values

Here is something to try

Set these values on M1's /etc/my.cnf
- auto_increment_increment = 10
- auto_increment_offset = 1
Set these values on M2's /etc/my.cnf
- auto_increment_increment = 10
- auto_increment_offset = 1
Set these values on S1's /etc/my.cnf
- auto_increment_increment = 10
- auto_increment_offset = 1

Restart mysql on all three servers

Load Data like this:

mysqldump from M1 to
Run SET GLOBAL sql_log_bin = 0; on M1
Load the mysqldump into M1
Run SET GLOBAL sql_log_bin = 1; on M1
Run SET GLOBAL sql_log_bin = 0; on M2
Load the mysqldump into M2
Run SET GLOBAL sql_log_bin = 1; on M2
Load the mysqldump into S1
Reset Replication amongst the threee server

If nothing breaks, you can then do this

Set these values on M2's /etc/my.cnf - auto_increment_increment = 10 - auto_increment_offset = 2

Restart mysql on M2

Everything should be clean now. From here, INSERTs should be properly handled.

Give it a Try !!1

Mysql – Broken thesql replication on slave server (error 1236)

I believe the log file and position need to be specified with the host.

Try:

CHANGE MASTER TO MASTER_HOST='192.168.1.105', MASTER_USER='slaveuser', MASTER_PASSWORD='mypassword', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107;