Are you using galera? You've selected 5.5.34-galera as your "affected version", was it intentional, or you've meant 5.5.34 without galera?

I've tried the following test case:

CREATE TABLE `deadlock_test` (

  `buggy_column` int(11) DEFAULT NULL,

  UNIQUE KEY `buggy_column` (`buggy_column`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

insert deadlock_test values (NULL);

And saw no deadlock. Please provide a full test case, not only a table definition.

No, it's not galera. I didn't see a 5.5.34 option when I created it. Maybe I just missed it. I changed it now.

Also, I don't know what more you want for a "full test case". I provided 3 specific steps you can do to see the issue. It reliably reproduces the deadlock every time for me. I'm not sure what else I can give you.

Thanks for correcting the version.

I mean the following — you wrote

1) If you try to insert a row with the column value set to NULL it will deadlock.

but I have tried exactly that (see the INSERT statement above, in my first comment) and did not get a deadlock. Perhaps you used a different form of the INSERT statement. Perhaps your table already had rows and that made a difference. I don't know. This is why I am asking for a complete test case in SQL. Something, I can save to a file and execute as

$ mysql < deadlock_test.sql

and it will reproduce the deadlock.

I just dropped the test table, put this in a file and ran it:

CREATE TABLE IF NOT EXISTS `deadlock_test` (

  `buggy_column` int(11) DEFAULT NULL,

  UNIQUE KEY `buggy_column` (`buggy_column`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO deadlock_test VALUES (NULL);

When I run that it gives me this:

ERROR 1213 (40001) at line 6: Deadlock found when trying to get lock; try restarting transaction

Hi Justin,

Please

• open the client in interactive mode (rather than putting it in a file and running it);
• run
  DROP TABLE deadlock_test;
  CREATE TABLE `deadlock_test` (
  `buggy_column` int(11) DEFAULT NULL,
  UNIQUE KEY `buggy_column` (`buggy_column`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO deadlock_test VALUES (NULL);

Paste the whole unabridged output starting from when you started the client and up to and including the error message.

Thanks.

Ok, I just did that, but it's really no different. Here's the output:

MariaDB [deseret_studio]> DROP TABLE `deadlock_test`;

Query OK, 0 rows affected (0.00 sec)

MariaDB [deseret_studio]> CREATE TABLE `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)

MariaDB [deseret_studio]> INSERT INTO deadlock_test VALUES (NULL);

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Is there any other database configuration or system information that may affect this that could be helpful to post? It's running on a Debian server if that matters. Also, here's the specific version info, in case you care:

$ mysql --version

mysql  Ver 15.1 Distrib 5.5.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.1

Justin,

Are you able to locate your error log? It should be either in the location where @@log_error variable points, or in the syslog (with mysqld prefix).
If you find it, please copy it into a file and attach to this task.

Please also attach your cnf file(s) or, if it's easier, output of SHOW GLOBAL VARIABLES command.

Thanks.

Files:

1) Output of `SHOW GLOBAL VARIABLES;`
2) /etc/mysql/my.cnf
3) All contents of /etc/mysql/conf.d

After running that INSERT that deadlocks it dumps this to syslog

Jan 28 11:59:38 dc-mariadb01 mysqld: 140128 11:59:38 [Warning] WSREP: SQL statement was ineffective, THD: 10932755, buf: 92

Jan 28 11:59:38 dc-mariadb01 mysqld: QUERY: INSERT INTO deadlock_test VALUES (NULL)

Jan 28 11:59:38 dc-mariadb01 mysqld:  => Skipping replication

Hi Justin,

So it turns out you are using Galera after all. If it's unintentional, please remove conf.d/mariadb.cnf file (or move it somewhere outside the config dirs) and restart the server.
If you can't restart the server right away, please try to
set global wsrep_provider=''
and repeat your test. It might also help

It's probably not unintentional. I didn't set all this up, it was setup by our IT guys. And seeing as how it's a production environment I probably shouldn't mess with it without understanding what exactly I'm doing. We have a MySQL (not MariaDB) slave setup right now. The MariaDB server is the master. Will changing wresp_provider mess any of that up?

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.

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.