I can confirm the issue on PXC 5.5.34 + Galera 2.8 and PXC 5.5.37 + Galera 2.10.
This is how I tested (thanks to Celest for test case):
- created 3 nodes PXC cluster using Vagrant and these wonderful pack: https://github.com/lefred/percona-cluster
- installed parallel-ssh on vagrant host
- created sample table:
CREATE TABLE `tbl_0` (
`reg_name` varchar(127) NOT NULL,
`reg_key` varchar(64) NOT NULL DEFAULT '',
`reg_secret` varchar(128) NOT NULL,
`description` varchar(255) DEFAULT NULL,
`reg_status` varchar(16) NOT NULL,
`user_id` varchar(255) NOT NULL,
`owner_id` varchar(64) DEFAULT NULL,
PRIMARY KEY (`reg_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
- enabled debugging on one of the nodes:
SET GLOBAL wsrep_log_conflicts="ON";
SET GLOBAL wsrep_debug="ON";
- deployed attached bash script on each node
- ran it in parallel on each node with:
percona3 mysql> select count(*) total from tbl_0; select count(*) node1 from tbl_0 where reg_key like 'percona1%'; select count(*) node2 from tbl_0 where reg_key like 'percona2%'; select count(*) node3 from tbl_0 where reg_key like 'percona3%';
+-------+
| total |
+-------+
| 178 |
+-------+
1 row in set (0.00 sec)
+-------+
| node1 |
+-------+
| 43 |
+-------+
1 row in set (0.00 sec)
+-------+
| node2 |
+-------+
| 63 |
+-------+
1 row in set (0.00 sec)
+-------+
| node3 |
+-------+
| 72 |
+-------+
1 row in set (0.00 sec)
I can confirm the issue on PXC 5.5.34 + Galera 2.8 and PXC 5.5.37 + Galera 2.10.
This is how I tested (thanks to Celest for test case):
- created 3 nodes PXC cluster using Vagrant and these wonderful pack: https:/ /github. com/lefred/ percona- cluster
- installed parallel-ssh on vagrant host
- created sample table:
CREATE TABLE `tbl_0` (
`reg_name` varchar(127) NOT NULL,
`reg_key` varchar(64) NOT NULL DEFAULT '',
`reg_secret` varchar(128) NOT NULL,
`description` varchar(255) DEFAULT NULL,
`reg_status` varchar(16) NOT NULL,
`user_id` varchar(255) NOT NULL,
`owner_id` varchar(64) DEFAULT NULL,
PRIMARY KEY (`reg_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
- enabled debugging on one of the nodes: conflicts= "ON";
SET GLOBAL wsrep_log_
SET GLOBAL wsrep_debug="ON";
- deployed attached bash script on each node
- ran it in parallel on each node with:
parallel-ssh -P -h cluster1.pssh -x "-i /home/przemysla w.malkowski/ .vagrant. d/insecure_ private_ key -oStrictHostKey Checking= no" '/home/ vagrant/ test.sh 20 100 1' 127.0.0. 1:2211 127.0.0. 1:2205 127.0.0. 1:2204
[1] 08:43:10 [SUCCESS] vagrant@
[2] 08:43:11 [SUCCESS] vagrant@
[3] 08:43:12 [SUCCESS] vagrant@
percona3 mysql> select count(*) total from tbl_0; select count(*) node1 from tbl_0 where reg_key like 'percona1%'; select count(*) node2 from tbl_0 where reg_key like 'percona2%'; select count(*) node3 from tbl_0 where reg_key like 'percona3%';
+-------+
| total |
+-------+
| 178 |
+-------+
1 row in set (0.00 sec)
+-------+
| node1 |
+-------+
| 43 |
+-------+
1 row in set (0.00 sec)
+-------+
| node2 |
+-------+
| 63 |
+-------+
1 row in set (0.00 sec)
+-------+
| node3 |
+-------+
| 72 |
+-------+
1 row in set (0.00 sec)
Debug info example:
140505 14:43:11 [Note] WSREP: trx conflict for key 0 74657374 74626c5f30 010050004500520 043004f004e0041 00 : source: 5a67fd6a- cd2c-11e3- 9ec4-de1070c656 ba version: 2 local: 1 state: CERTIFYING flags: 1 conn_id: 7900 trx_id: 38507 seqnos (l: 24976, g: 24850, s: 24848, d: 24429, ts: 139929379101854 6985) <--X--> source: d067275c- d44d-11e3- b054-036e4c0d59 ea version: 2 local: 0 state: COMMITTED flags: 1 conn_id: 186 trx_id: 31963 seqnos (l: 24975, g: 24849, s: 24848, d: 24847, ts: 139929379109559 6655)
,"percona1_ key_75"
,"percona1_ pw_75"
,"percona1_ descr_75"
,"active"
,"uid_ dbs_75"
,"A_accessorL oadtestCk_ 1")
,"percona1_ key_75"
,"percona1_ pw_75"
,"percona1_ descr_75"
,"active"
,"uid_ dbs_75"
,"A_accessorL oadtestCk_ 1")
,"percona1_ key_75"
,"percona1_ pw_75"
,"percona1_ descr_75"
,"active"
,"uid_ dbs_75"
,"A_accessorL oadtestCk_ 1")
140505 14:43:11 [Note] WSREP: commit failed for reason: 3
140505 14:43:11 [Note] WSREP: conflict state: 0
140505 14:43:11 [Note] WSREP: cluster conflict due to certification failure for threads:
140505 14:43:11 [Note] WSREP: Victim thread:
THD: 7900, mode: local, state: executing, conflict: cert failure, seqno: 24850
SQL: INSERT INTO
tbl_0 (reg_name
, reg_key
, reg_secret
, description
, reg_status
, user_id
, owner_id)
VALUES ("percona1_name_75"
140505 14:43:11 [Note] WSREP: cert failure, thd: 7900 is_AC: 1, retry: 1 - 1 SQL: INSERT INTO
tbl_0 (reg_name
, reg_key
, reg_secret
, description
, reg_status
, user_id
, owner_id)
VALUES ("percona1_name_75"
140505 14:43:11 [Note] WSREP: releasing retry_query: conf 0 sent 0 kill 0 errno 1213 SQL INSERT INTO
tbl_0 (reg_name
, reg_key
, reg_secret
, description
, reg_status
, user_id
, owner_id)
VALUES ("percona1_name_75"