All nodes on Galera cluster crash on foreign key check

Your rating: None

[ERROR] Slave SQL: Could not execute Delete_rows event on table fk.parent; Cannot delete or update a parent row: a foreign key constraint fails

Symptoms

There seems to be a compatibility issue with App Suite (ver. 7.8.3 or higher) and MySQL/Galera database (Perocna 56 or higher).
If Galera replication is configured between several nodes, then in some circumstances, under heavy load (such as during initial data migration), foreign key constraints can end up causing errors in the replication which brings down the entire database cluster. In the logs you will see an error like this;

2017-05-22 20:37:29 983 [ERROR] Slave SQL: Could not execute Delete_rows event on table fk.parent; Cannot delete or update a parent row: a foreign key constraint fails (`fk`.`child`, CONSTRAINT `fk_parent` FOREIGN KEY (`pID`) REFERENCES `parent` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION), Error_code: 1451; handler error HA_ERR_ROW_IS_REFERENCED; the event's master log FIRST, end_log_pos 158, Error_code: 1451
2017-05-22 20:37:29 983 [Warning] WSREP: RBR event 3 Delete_rows apply warning: 152, 203
2017-05-22 20:37:29 983 [Warning] WSREP: Failed to apply app buffer: seqno: 203, status: 1
at galera/src/trx_handle.cpp:apply():351

Reason

This seems to be a Bug currently mentioned on Percona official website;
https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1692745

Workaround

A workaround is to set the slave threads to 1, or run a single node. This prevents the race condition that appears to apply changes out of sequence causing failures; however it reduces available bandwidth for inter-node replication:

You can change this by editing the configuration file my.cnf or wsrep.cnf (based on your pre-defined server configuration).

wsrep_slave_threads=1

Open-Xchange AG | Impressum