0

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)
6
  • Please provide SHOW CREATE TABLE mih_card_ranking.
    – Rick James
    Oct 12, 2017 at 3:32
  • 1
    SKIP... sweeps the problem under the rug. After awhile the mound under the rug gets so tall that everyone is tripping over it.
    – Rick James
    Oct 12, 2017 at 3:33
  • I have posted the table structure as requested. We are thinking about creating a temp new table structure, dump data in it for temp reads, then truncate the old table, then insert new data back in it. Not sure if that would help resolve the issue? Oct 12, 2017 at 4:06
  • Do all nodes have the same number of rows in that table? And all have the UNIQUE key?
    – Rick James
    Oct 12, 2017 at 4:28
  • The table structure (all have unique key) on all nodes is the same. As for data, since the reported node has an issue and mysql broken, obviously the data cannot be the same as the data in remaining nodes. Oct 12, 2017 at 5:49

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.