Deadlock with insertion of NULL unique key
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL patches by Codership |
Fix Committed
|
Low
|
Seppo Jaakola | |||
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC | Status tracked in 5.6 | |||||
5.5 |
Fix Released
|
Undecided
|
Unassigned | |||
5.6 |
Fix Released
|
Undecided
|
Unassigned |
Bug Description
Originally reported here https:/
MySQL [test]> CREATE TABLE IF NOT EXISTS `deadlock_test` (
-> `buggy_column` int(11) DEFAULT NULL,
-> UNIQUE KEY `buggy_column` (`buggy_column`)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
MySQL [test]> INSERT INTO deadlock_test VALUES (NULL);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
MySQL [test]> INSERT INTO deadlock_test VALUES (NULL);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
=======
2014-02-05 11:55:35 25337 [Note] WSREP: NULL key skipped: INSERT INTO deadlock_test VALUES (NULL)
2014-02-05 11:55:35 25337 [Warning] WSREP: SQL statement was ineffective, THD: 4, buf: 152
QUERY: INSERT INTO deadlock_test VALUES (NULL)
=> Skipping replication
2014-02-05 11:55:35 25337 [Note] WSREP: commit failed for reason: 3
2014-02-05 11:55:35 25337 [Note] WSREP: conflict state: 0
2014-02-05 11:55:35 25337 [Note] WSREP: cluster conflict due to certification failure for threads:
2014-02-05 11:55:35 25337 [Note] WSREP: Victim thread:
THD: 4, mode: local, state: executing, conflict: cert failure, seqno: -1
SQL: INSERT INTO deadlock_test VALUES (NULL)
2014-02-05 11:55:35 25337 [Note] WSREP: wsrep retrying AC query: INSERT INTO deadlock_test VALUES (NULL)
2014-02-05 11:55:35 25337 [Note] WSREP: NULL key skipped: INSERT INTO deadlock_test VALUES (NULL)
2014-02-05 11:55:35 25337 [Warning] WSREP: SQL statement was ineffective, THD: 4, buf: 152
QUERY: INSERT INTO deadlock_test VALUES (NULL)
=> Skipping replication
2014-02-05 11:55:35 25337 [Note] WSREP: commit failed for reason: 3
2014-02-05 11:55:35 25337 [Note] WSREP: conflict state: 0
2014-02-05 11:55:35 25337 [Note] WSREP: cluster conflict due to certification failure for threads:
2014-02-05 11:55:35 25337 [Note] WSREP: Victim thread:
THD: 4, mode: local, state: executing, conflict: cert failure, seqno: -1
SQL: INSERT INTO deadlock_test VALUES (NULL)
2014-02-05 11:55:35 25337 [Note] WSREP: cert failure, thd: 4 is_AC: 1, retry: 1 - 1 SQL: INSERT INTO deadlock_test VALUES (NULL)
2014-02-05 11:55:35 25337 [Note] WSREP: releasing retry_query: conf 0 sent 0 kill 0 errno 1213 SQL INSERT INTO deadlock_test VALUES (NULL)
=======
Few things:
a) Why is it retried?
b) Is deadlock the correct error to return to client
and finally
c) Is it correct to fail for NULL key append (looks like that).
Changed in codership-mysql: | |
importance: | Undecided → Low |
assignee: | nobody → Seppo Jaakola (seppo-jaakola) |
status: | New → In Progress |
The problem happens, because wsrep layer does not use keys which have NULL value. If a transaction has only statements which modify such NULL key rows, then the transaction has not populated any key information to the replication plugin.
At commit time, transaction cache has events, which should be replicated, but replicator does not recognize transaction because no keys were populated. Therefore the replication attempt fails and wsrep layer does rollback and gives back deadlock error.