**Reported in Launchpad by Jernej last update 17-01-2016 04:17:29
We have a three node multi-master cluster (almost same hardware on each) set up behind load balancer with only one active member at a time. We are constantly having failure of two nodes in the cluster when the active master receives certain load of queries from Hibernate enabled application.
Two of the nodes go down with:
131230 10:28:19 [ERROR] Slave SQL: Could not execute Write_rows event on table aris.user_example_print_queue; Cannot add or update a child row: a foreign key constraint fails (`aris`.`user_example_print_queue`, CONSTRAINT `FK_user_example_print_queue` FOREIGN KEY (`user_example_id`) REFERENCES `user_example` (`id`)), Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW; the event's master log FIRST, end_log_pos 499, Error_code: 1452
131230 10:28:19 [Warning] WSREP: RBR event 4 Write_rows apply warning: 151, 743194820
131230 10:28:19 [Warning] WSREP: Failed to apply app buffer: seqno: 743194820, status: 1
Retrying 2th time
We have narrowed down the problematic tables and queries (attaching db.sql, error logs, query logs). After the crash the whole cluster goes into unstable mode and we have to rebuild two nodes from the master each time, which helps us to survive till next execution of this queries from application.
Linux sql-1.example.com 2.6.32-431.el6.x86_64 #1 SMP Fri Nov 22 03:15:09 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
The example in the attached logs show:
1. at the time only two nodes were up: 1 and 3 (it happens the same if they are all up)
2. query came to sql-1 and got executed there, but i failed on sql-3
3. sql-3 was unable to execute the statement and therefore it shuts down
I am attaching:
- configs from all three nodes
- error logs from all three nodes
- query logs
- database structure
I have checked other bugs: https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1217653 but we have primary keys set up and I don't find the connection to any other options available. At first, we thought it is the case of auto_increment field, however we are running latest versions available...
Thank you in advance, regards, Jernej