search

codership / mysql-wsrep

wsrep API patch for MySQL server
Other
64 stars 34 forks source link

Duplicate autoinc values can happen during configuration change #18

Open sjaakola opened 9 years ago

sjaakola commented 9 years ago

Inserts to a table with autoinc primamry key can result in duplicate key error, if another node joins or drops from the cluster during the insert processing. This issue has been logged earlier in:

https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1366997 https://mariadb.atlassian.net/browse/MDEV-7129

sjaakola commented 9 years ago

for autocommit inserts, there is a workaround by configuring: wsrep_drupal_282555_workaround = ON wsrep_retry_autocommit= < reasonably large value, in my test 10 is enough to prevent dupkey errors >

With this configuration, mysql server will silently retry an autocommit insert that would fail for duplicate key error. Max retry count can be controlled with wsrep_retry_autocommit limit.

yaguangtang commented 9 years ago

Is it possible to backport this fix to 5.5? thanks.

romuald commented 9 years ago

And when not using autocommit? :) Is there a workaround / pending fix?

We've seen this issue using "classic" transactions. Node leaving / rejoining cluster during network flap → duplicate primary entry for primary key

xathien commented 8 years ago

This bug is especially catastrophic for INSERT ... ON DUPLICATE KEY UPDATE commands, as MySQL won't throw an error that will allow for a retry; instead, it will simply overwrite whatever row it collided with. :(