We have a Percona PXC with three nodes. last week, we had a third node, db-node-3, go down with an error:
2017-03-12T10:41:22.070396+08:00 30 [ERROR] Slave SQL: Could not execute Write_rows event on table p13n.mih_card_ranking; Duplicate entry '53648815-3-1' for key 'msno_version_algorithm_id', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 6117, Error_code: 1062
And today the second node db-name-2, also experienced the same error:
2017-10-12T08:48:32.070396+08:00 30 [ERROR] Slave SQL: Could not execute Write_rows event on table p13n.mih_card_ranking; Duplicate entry '83628815-3-1' for key 'msno_version_algorithm_id', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log FIRST, end_log_pos 6117, Error_code: 1062
2017-10-12T08:48:32.070417+08:00 30 [Warning] WSREP: RBR event 3 Write_rows apply warning: 121, 575158440
2017-10-12T08:48:32.086370+08:00 30 [Warning] WSREP: Failed to apply app buffer: seqno: 575158440, status: 1
at galera/src/trx_handle.cpp:apply():351
Right now, i know that our Developers are performing a lot of data INSERT on the reported table, and suspect that might be corrupting the data somehow and breaking the PXC.
This kind of error to me like something you would expect from a slave. On a slave, Whenever there is a 1062 error, the usual table with the problem is the actual table being updated in the query. And on a slave, you can normally solve this kind of issue with the SQL_SLAVE_SKIP_COUNTER = 1.
But since this is a PXC environment, should this sort of error be happening? And what would be the best possible solution to solve this and avoid it in the future?
mysql> SHOW CREATE TABLE mih_card_ranking\G
*************************** 1. row ***************************
Table: mih_card_ranking
Create Table: CREATE TABLE `mih_card_ranking` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`version` smallint(5) unsigned NOT NULL,
`algorithm_id` tinyint(4) unsigned NOT NULL,
`msno` bigint(20) unsigned NOT NULL,
`data` json NOT NULL,
`updated_ts` int(20) unsigned NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `msno_version_algorithm_id` (`msno`,`version`,`algorithm_id`),
KEY `btree__updated_ts` (`updated_ts`)
) ENGINE=InnoDB AUTO_INCREMENT=169056776 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)
SHOW CREATE TABLE mih_card_ranking
.SKIP...
sweeps the problem under the rug. After awhile the mound under the rug gets so tall that everyone is tripping over it.UNIQUE
key?