Deadlock with insertion of NULL unique key

[Raghavendra D Prabhu]

Originally reported here https://mariadb.atlassian.net/browse/MDEV-5552

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).

[Seppo Jaakola (seppo-jaakola)]

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.

[Seppo Jaakola (seppo-jaakola)]

To fully support NULL keys, wsrep API and replication plugin should make it possible to either pass NULL keys or just introduce transaction with no key information.

However, there is a potential workaround to support NULL keys, by using the md5 sum of full row as key value. This will cause unnecessary deadlocks in certain multi-master use cases, but should work fine at least in master-slave topology. There turns out to be a simple bug, which prevents NULL key cases to go for md5 sum row keys. This bug will be fixed first, and then by setting wsrep_cert_nonPK variable, this workaround should be usable.

[Seppo Jaakola (seppo-jaakola)]

The workaround fix pushed in revisions:

wsrep-5.6: http://bazaar.launchpad.net/~codership/codership-mysql/5.6/revision/4037
wsrep-5.5: http://bazaar.launchpad.net/~codership/codership-mysql/wsrep-5.5/revision/3945

set wsrep_cert_nonPK, and NULL keys should work in most cases.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1602