Duplicate entry error for primary key following cluster size change
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL patches by Codership | Status tracked in 5.6 | |||||
5.5 |
Confirmed
|
Undecided
|
Unassigned | |||
5.6 |
Fix Released
|
Undecided
|
Unassigned | |||
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC | Status tracked in 5.6 | |||||
5.5 |
Fix Released
|
Undecided
|
Alexey Kopytov | |||
5.6 |
Fix Released
|
Undecided
|
Unassigned | |||
percona-cluster (Juju Charms Collection) |
Fix Released
|
Undecided
|
Mario Splivalo | |||
percona-xtradb-cluster-5.5 (Ubuntu) |
Fix Released
|
Undecided
|
Mario Splivalo | |||
Trusty |
Fix Released
|
Medium
|
Mario Splivalo |
Bug Description
[Impact]
* Duplicate entry errors on the primary key for tables occurs, data
can't be added to DB correctly and in time.
[Test Case]
* Prepare a .sql file full of INSERT queries:
INSERT INTO test.t VALUES(
that line million of times.
* Start a 3 node clusters.
* Load the data on node 1 (if you do several times in parallel you will get the error sooner): cat data.sql | mysql
* Restart node2 and wait until MySQL is running.
* Restart node3 and wait until MySQL is running.
* Repeat step 4 and 5 several times (sometimes lot of restarts are needed, sometimes just a few) and the node1 load will fail with duplicate key entry.
[Regression Potential]
* The patch to fix this issue is backported from a nearest version 5.6.
* There is failed build for powerpc architecture, but this was not introduced by this change - one can check that this has been like this in previous releases too:
https:/
If you expand previous versions (14.04.1 and 14.04.2) you can see that the build was failing for powerpc.
[Other Info]
* For Ubuntu SRU verification team :
https:/
We (PagerDuty) have experienced on multiple occasions duplicate entry errors on the primary key for some tables after our cluster's size changes. In the cases we have experienced so far, this is when gracefully adding or removing a node to vertically scale the nodes in the cluster. We don't experience a total failure of all transactions. Instead, a small subset error out. In some cases, the problem corrects itself, but several times now we have had the errors persist for several hours. When the problem persists, we have found that restarting one of the cluster members will sometimes fix the issue.
Restarting all transactions by rebooting the application does not fix the issue. Only a restart of a cluster member can eventually solve the problem.
Here are the packages we are running
ii percona-toolkit 2.2.7 Advanced MySQL and system command-line tools
ii percona-xtrabackup 2.1.9-744-1.lucid Open source backup tool for InnoDB and XtraDB
ii percona-
ii percona-
ii percona-
ii percona-
Here is an example error that we see.
Duplicate entry '623287' for key 'PRIMARY'
That error will be seen for multiple tables, and sometimes again for the same table.
Please let me know what additional information can help. The MySQL error log doesn't appear to have any interesting details in it (just node join and leave events) but I would be glad to pass it along if you think it would be helpful.
Related branches
- Raghavendra D Prabhu: Pending requested
-
Diff: 65 lines (+24/-3)2 files modifiedsql/wsrep_hton.cc (+1/-1)
storage/innobase/handler/ha_innodb.cc (+23/-2)
- Billy Olsen: Approve
- charmers: Pending requested
- Edward Hope-Morley: Pending requested
-
Diff: 87 lines (+27/-14)3 files modifiedconfig.yaml (+12/-8)
hooks/percona_hooks.py (+4/-1)
templates/my.cnf (+11/-5)
- Edward Hope-Morley: Pending requested
- charmers: Pending requested
-
Diff: 87 lines (+27/-14)3 files modifiedconfig.yaml (+12/-8)
hooks/percona_hooks.py (+4/-1)
templates/my.cnf (+11/-5)
Changed in percona-server-5.5 (Ubuntu): | |
assignee: | nobody → Yaguang Tang (heut2008) |
no longer affects: | percona-server-5.5 (Ubuntu) |
tags: | added: cts |
description: | updated |
Changed in percona-cluster (Juju Charms Collection): | |
assignee: | nobody → Edward Hope-Morley (hopem) |
status: | New → In Progress |
affects: | percona-cluster (Ubuntu) → percona-xtradb-cluster-5.5 (Ubuntu) |
Changed in percona-xtradb-cluster-5.5 (Ubuntu): | |
status: | New → Confirmed |
Changed in percona-cluster (Juju Charms Collection): | |
assignee: | Edward Hope-Morley (hopem) → Mario Splivalo (mariosplivalo) |
milestone: | none → 15.04 |
status: | In Progress → Fix Released |
Changed in percona-xtradb-cluster-5.5 (Ubuntu): | |
assignee: | nobody → Mario Splivalo (mariosplivalo) |
Changed in percona-xtradb-cluster-5.5 (Ubuntu Trusty): | |
assignee: | nobody → Mario Splivalo (mariosplivalo) |
status: | New → Confirmed |
Changed in percona-xtradb-cluster-5.5 (Ubuntu): | |
status: | Confirmed → In Progress |
Changed in percona-xtradb-cluster-5.5 (Ubuntu Trusty): | |
status: | Confirmed → In Progress |
tags: | added: sts sts-sru-needed |
Changed in percona-xtradb-cluster-5.5 (Ubuntu Trusty): | |
importance: | Undecided → Medium |
description: | updated |
tags: |
added: verification-done removed: verification-needed |
tags: |
added: verification-done-trusty removed: verification-done |
tags: |
added: sts-sru-done removed: sts-sru-needed |
Changed in percona-xtradb-cluster-5.5 (Ubuntu): | |
status: | In Progress → Fix Released |
Hey Doug, increment and auto_increment_ offset settings depending on the cluster size to avoid such collisions as these (assuming you have wsrep_auto_ increment_ control = ON, which is the default: http:// www.percona. com/doc/ percona- xtradb- cluster/ 5.5/wsrep- system- index.html# wsrep_auto_ increment_ control). I'm thinking this is a bug related to that.
Can you confirm the PRIMARY KEY in this case is an auto-increment? If so, PXC modifies the auto_increment_