# Occassional node failure (FK related) **URL:** \ **Category:** Percona XtraDB Cluster 5.x\ **Created:** [November 22, 2012, 11:39pm UTC](https://forums.percona.com/t/occassional-node-failure-fk-related/2505 "2012-11-22T23:39:30Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![theevilmuppet](https://avatars.discourse-cdn.com/v4/letter/t/f475e1/32.png) [@theevilmuppet](https://forums.percona.com/u/theevilmuppet)\ **Post date:** [November 22, 2012, 11:39pm UTC](https://forums.percona.com/t/occassional-node-failure-fk-related/2505/1 "2012-11-22T23:39:30Z")
Hi all, We have a 3 node cluster set up, with one of the three nodes acting as a replication slave to a standard Percona Server. There is no other traffic on the server at this time. Strangely, we’re finding that every now and then one node (sometimes 2 nodes) will die due to node consistency being compromised . The transactions in question are being committed without problem on the replication master and the node in the cluster that is acting as replication slave. We’re currently running the latest version of Percona Cluster as installed from the Percona repository (Percona-XtraDB-Cluster-server-5.5.28-23.7.369.rhel6.x86\_64 and Percona-XtraDB-Cluster-galera-2.0-1.117.rhel6.x86\_64) and have the following configuration applied on all three nodes (with appropriate address changes for various wsrep settings): [mysqld\_safe]wsrep\_urls=gcomm://192.168.160.5:4567,gcomm://192.168.160.8:4567[mysqld]datadir=/var/lib/mysqluser=mysqlmax\_connections=4000server-id=40binlog\_format=ROWdefault\_storage\_engine=InnoDBlong\_query\_time=10log\_error=/var/log/mysql/error\_loglog\_slow\_queries=/var/log/mysql/slow\_query\_loglog\_slow\_verbosity=fullsync\_binlog=0log\_bin = bin-loglog\_slave\_updates = 1wsrep\_node\_address=192.168.160.4wsrep\_sst\_receive\_address=192.168.160.4wsrep\_sst\_auth=root:742evergreentcewsrep\_provider=/usr/lib64/libgalera\_smm.sowsrep\_slave\_threads=2wsrep\_sst\_method=xtrabackupwsrep\_cluster\_name=percona\_clusterwsrep\_node\_name=node1wsrep\_replicate\_myisam=1innodb\_buffer\_pool\_instances=1innodb\_data\_home\_dir=/data/mysqlinnodb\_log\_group\_home\_dir=/data/mysqlinnodb\_data\_file\_path=ibdata1:1G:autoextendinnodb\_buffer\_pool\_size=96Ginnodb\_flush\_method=ALL\_O\_DIRECTinnodb\_additional\_mem\_pool\_size=256Minnodb\_log\_buffer\_size=256Minnodb\_log\_file\_size=256Minnodb\_flush\_log\_at\_trx\_commit=0innodb\_read\_io\_threads=8innodb\_autoinc\_lock\_mode=2innodb\_locks\_unsafe\_for\_binlog=1 When the error does occur, the following error occurs (always involving the same tables): 121123 15:03:32 [ERROR] Slave SQL: Could not execute Write\_rows event on table nablive.t\_schedule\_payment; Cannot add or update a child row: a foreign key constraint fails (`ntproddb`.`t_schedule_payment`, CONSTRAINT `t_schedule_payment_ibfk_1` FOREIGN KEY (`sched_id`) REFERENCES `t_schedule` (`id`) ON DELETE CASCADE), Error\_code: 1452; handler error HA\_ERR\_NO\_REFERENCED\_ROW; the event’s master log FIRST, end\_log\_pos 232, Error\_code: 1452121123 15:03:32 [Warning] WSREP: RBR event 2 Write\_rows apply warning: 151, 1663670121123 15:03:32 [ERROR] WSREP: Failed to apply trx: source: eed70aa6-346b-11e2-0800-24241b746e7d version: 2 local: 0 state: APPLYING flags: 1 conn\_id: 4 trx\_id: 676177914 seqnos (l: 59090, g: 1663670, s: 1663669, d: 1663365, ts: 1353643412747819106)121123 15:03:32 [ERROR] WSREP: Failed to apply app buffer: ���P�, seqno: 1663670, status: WSREP\_FATAL at galera/src/replicator\_smm.cpp:apply\_wscoll():49 at galera/src/replicator\_smm.cpp:apply\_trx\_ws():120121123 15:03:32 [ERROR] WSREP: Node consistency compromized, aborting…121123 15:03:32 [Note] WSREP: Closing send monitor…121123 15:03:32 [Note] WSREP: Closed send monitor.121123 15:03:32 [Note] WSREP: gcomm: terminating thread121123 15:03:32 [Note] WSREP: gcomm: joining thread121123 15:03:32 [Note] WSREP: gcomm: closing backend121123 15:03:32 [Note] WSREP: view(view\_id(NON\_PRIM,05aece68-3511-11e2-0800-5e30f373f04f,9) memb { 05aece68-3511-11e2-0800-5e30f373f04f,} joined {} left {} partitioned { eed70aa6-346b-11e2-0800-24241b746e7d, fbc2a20c-3503-11e2-0800-2365c6b05d93,})121123 15:03:32 [Note] WSREP: New COMPONENT: primary = no, bootstrap = no, my\_idx = 0, memb\_num = 1121123 15:03:32 [Note] WSREP: view((empty))121123 15:03:32 [Note] WSREP: gcomm: closed121123 15:03:32 [Note] WSREP: Flow-control interval: [16, 16]121123 15:03:32 [Note] WSREP: Received NON-PRIMARY.121123 15:03:32 [Note] WSREP: Shifting SYNCED → OPEN (TO: 1663674)121123 15:03:32 [Note] WSREP: Received self-leave message.121123 15:03:32 [Note] WSREP: Flow-control interval: [0, 0]121123 15:03:32 [Note] WSREP: Received SELF-LEAVE. Closing connection.121123 15:03:32 [Note] WSREP: Shifting OPEN → CLOSED (TO: 1663674)121123 15:03:32 [Note] WSREP: RECV thread exiting 0: Success121123 15:03:32 [Note] WSREP: recv\_thread() joined.121123 15:03:32 [Note] WSREP: Closing slave action queue.121123 15:03:32 [Note] WSREP: /usr/sbin/mysqld: Terminated.121123 15:03:33 mysqld\_safe Number of processes running now: 0121123 15:03:33 mysqld\_safe WSREP: not restarting wsrep node automatically The create table statements for the two tables in the FK relationship that are at the centre of this error are: CREATE TABLE `t_schedule` ( `id` int(11) NOT NULL AUTO\_INCREMENT, `name` varchar(50) NOT NULL, `version` int(11) NOT NULL, `client_ref` varchar(50) NOT NULL, `freq` int(11) NOT NULL, `start_dt` datetime NOT NULL, `to_dt` datetime DEFAULT NULL, `exec_cnt` int(11) DEFAULT NULL, `created_by` int(11) NOT NULL, `created_on` datetime NOT NULL, `payment_type` int(11) NOT NULL, `lastmod_by` int(11) DEFAULT NULL, `lastmod_on` datetime DEFAULT NULL, `merchant_id` varchar(6) NOT NULL, `type_id` smallint(6) NOT NULL DEFAULT ‘0’, KEY `merchant_id_fk` (`merchant_id`), KEY `id` (`id`), KEY `fk_schedule_isof_type` (`type_id`), CONSTRAINT `merchant_id_fk` FOREIGN KEY (`merchant_id`) REFERENCES `t_merchants` (`Merchant_ID`), CONSTRAINT `t_schedule_ibfk_1` FOREIGN KEY (`type_id`) REFERENCES `t_schedule_type` (`id`) ) ENGINE=InnoDB AUTO\_INCREMENT=71507 DEFAULT CHARSET=latin1 CREATE TABLE `t_schedule_payment` ( `id` int(11) NOT NULL AUTO\_INCREMENT, `version` int(11) NOT NULL, `processed` bit(1) NOT NULL, `cc_holder` varchar(50) DEFAULT NULL, `cc_exp_yy` varchar(2) DEFAULT NULL, `cc_exp_mm` varchar(2) DEFAULT NULL, `created_by` int(11) NOT NULL, `created_on` datetime NOT NULL, `cc_no` varchar(60) DEFAULT NULL, `transfer_dt` datetime NOT NULL, `lastmod_by` int(11) DEFAULT NULL, `lastmod_on` datetime DEFAULT NULL, `amt` int(11) DEFAULT NULL, `canceled` bit(1) NOT NULL, `pan` varchar(12) DEFAULT NULL, `sched_id` int(11) NOT NULL, `currency` char(3) NOT NULL DEFAULT ‘AUD’, `card_seq_no` char(3) DEFAULT NULL, `crn` varchar(20) DEFAULT NULL, `dd_bsb` varchar(20) DEFAULT NULL, `dd_acc_no` varchar(50) DEFAULT NULL, `dd_acc_name` varchar(255) DEFAULT NULL, `batch_id` int(11) DEFAULT NULL, `batch_trxid` int(11) DEFAULT NULL, KEY `id` (`id`), KEY `sched_id_fk` (`sched_id`), CONSTRAINT `t_schedule_payment_ibfk_1` FOREIGN KEY (`sched_id`) REFERENCES `t_schedule` (`id`) ON DELETE CASCADE) ENGINE=InnoDB AUTO\_INCREMENT=3964335 DEFAULT CHARSET=latin1 To rule out data corruption / referential integrity issues, we’ve run the following query on all the available nodes and gotten the same results: mysql\> select distinct sched\_id from t\_schedule\_payment where sched\_id not in (select id from t\_schedule);Empty set (2.47 sec) I’ve seen a few mentions of FK strangeness in this forum recently but it appeared that various patches had solved this issue. Is there something obvious (or not obvious, or undocumented etc) that we’re missing here? If there’s any further information we can provide, we definitely will. ---
**Author:** ![flyersa](https://avatars.discourse-cdn.com/v4/letter/f/9dc877/32.png) [@flyersa](https://forums.percona.com/u/flyersa)\ **Post date:** [April 9, 2013, 4:40am UTC](https://forums.percona.com/t/occassional-node-failure-fk-related/2505/2 "2013-04-09T04:40:11Z")
I have the same issues but with primary keys and duplicate keys that in theory should not even happen. ---
**Author:** ![shockwavecs](https://avatars.discourse-cdn.com/v4/letter/s/aca169/32.png) [@shockwavecs](https://forums.percona.com/u/shockwavecs)\ **Post date:** [June 5, 2014, 12:57pm UTC](https://forums.percona.com/t/occassional-node-failure-fk-related/2505/3 "2014-06-05T12:57:56Z")
anyone figure this out? ---
**Author:** ![przemek](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/przemek/32/3_2.png) [@przemek](https://forums.percona.com/u/przemek)\ **Post date:** [June 9, 2014, 4:05am UTC](https://forums.percona.com/t/occassional-node-failure-fk-related/2505/4 "2014-06-09T04:05:06Z")
Foreign keys are pretty tricky it seems in this kind of clustering solution, plus parallel applying makes it even more complicated. However many FK-related bugs were fixed till now. This thread is pretty old, but Shockwavecs - do you experience anything similar?