Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.33a-galera, 5.5.34-galera
-
None
Description
There is a problem that is consistently causing deadlocks when trying to INSERT or DELETE a row with a NULL value in a column that 1) can be NULL and 2) has a UNIQUE index.
I've tested this on MariaDB 5.5.34 and it has this problem. On MySQL 5.5.34 it does NOT seem to have the problem. I narrowed this down to a very simple table setup to reproduce. Simply create a table with the following:
CREATE TABLE `deadlock_test` ( |
`buggy_column` int(11) DEFAULT NULL, |
UNIQUE KEY `buggy_column` (`buggy_column`) |
) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Note that the column type does not matter. It can be VARCHAR or whatever else, so long as it can be NULL and it has a UNIQUE index.
Observations:
1) If you try to insert a row with the column value set to NULL it will deadlock.
2) If you insert a row with a value in the column it works. You can then UPDATE the column to NULL and it works fine.
3) If, while the column is NULL you try to DELETE the row, it deadlocks. You can, however, update it to have a value and then DELETE without a problem.
Attachments
Activity
Ok, that was not so good. 'wsrep_cluser_size' is set to 1. It just threw an error trying to set it to an empty string, but after a quick google search I set it to 'none'. That just hung (probably this bug https://bugs.launchpad.net/codership-mysql/+bug/1208493) and it brought down our production server. It's all back up now, but I'm going to have the IT guys setup a separate environment for testing.
Sorry to hear that. Hopefully while restarting it anyway, you removed wsrep configuration because it really does not do you any good to have all limitations of a Galera cluster without actually having the cluster.
Ok, I just tested another stage DB server that was running MariaDB 5.5.33a with galera that also has the same issue (even though it's not replicating anywhere right now). I then removed the mariadb.cnf file to disable galera and restarted the server and it no longer had the deadlock issue. So it's definitely a galera issue.
Assigning to Nirbhay to determine whether it's a bug or an known limitation, and proceed accordingly.
It should not affect traditional replication, but if you did not set it up and don't know why it is there, maybe you should not unset it either – it might be that not only do you have it configured, but also there is active Galera-style replication going on.
Can you run SHOW STATUS LIKE 'wsrep_cluster_size' and see what it shows? If it says anything but "1", you definitely should not touch it, I'll re-route your request to MariaDB-Galera developers to check if the behavior you observe with the NULL is expected. If cluster size is "1", then there is no Galera-style replication, so you can try to unset wsrep_provider and see if it helps.
In any case I suggest you contact your IT guys to find out if they configured it intentionally.