[MDEV-5552] Deadlock when inserting NULL column value in column with UNIQUE index - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 5.5.33a-galera, 5.5.34-galera
Fix Version/s: 5.5.36-galera
Component/s: None
Labels:
- galera

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

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

conf.d.tar
10 kB
2014-01-28 21:10
config_dump
14 kB
2014-01-28 21:10
my.cnf
5 kB
2014-01-28 21:10

Activity

Ascending order - Click to sort in descending order

Justin Warkentin created issue - 2014-01-22 20:41

Sergei Golubchik made changes - 2014-01-28 18:22

Field	Original Value	New Value
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.	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: {code:sql} CREATE TABLE `deadlock_test` ( `buggy_column` int(11) DEFAULT NULL, UNIQUE KEY `buggy_column` (`buggy_column`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; {code} 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.

Sergei Golubchik added a comment - 2014-01-28 18:27

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.

Sergei Golubchik added a comment - 2014-01-28 18:27 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.

Sergei Golubchik made changes - 2014-01-28 18:28

Due Date

2014-02-28

Justin Warkentin made changes - 2014-01-28 18:32

Affects Version/s		5.5.34 [ 13700 ]
Affects Version/s	5.5.34-galera [ 13900 ]

Justin Warkentin added a comment - 2014-01-28 18:33

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.

Justin Warkentin added a comment - 2014-01-28 18:33 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.

Justin Warkentin added a comment - 2014-01-28 18:36

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.

Justin Warkentin added a comment - 2014-01-28 18:36 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.

Sergei Golubchik added a comment - 2014-01-28 18:58

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.

Sergei Golubchik added a comment - 2014-01-28 18:58 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.

Justin Warkentin added a comment - 2014-01-28 19:13

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

Justin Warkentin added a comment - 2014-01-28 19:13 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

Sergei Golubchik made changes - 2014-01-28 19:53

Assignee

Elena Stepanova [ elenst ]

Elena Stepanova added a comment - 2014-01-28 19:59 - edited

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.

Elena Stepanova added a comment - 2014-01-28 19:59 - edited 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.

Justin Warkentin added a comment - 2014-01-28 20:27 - edited

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 Warkentin added a comment - 2014-01-28 20:27 - edited 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

Elena Stepanova added a comment - 2014-01-28 20:49

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.

Elena Stepanova added a comment - 2014-01-28 20:49 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.

Justin Warkentin added a comment - 2014-01-28 21:10

Files:

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

Justin Warkentin added a comment - 2014-01-28 21:10 Files: 1) Output of `SHOW GLOBAL VARIABLES;` 2) /etc/mysql/my.cnf 3) All contents of /etc/mysql/conf.d

Justin Warkentin made changes - 2014-01-28 21:10

Attachment		config_dump [ 26102 ]
Attachment		my.cnf [ 26103 ]
Attachment		conf.d.tar [ 26104 ]

Justin Warkentin added a comment - 2014-01-28 21:12

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

Justin Warkentin added a comment - 2014-01-28 21:12 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

Elena Stepanova added a comment - 2014-01-28 21:20 - edited

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

Elena Stepanova added a comment - 2014-01-28 21:20 - edited 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

Justin Warkentin added a comment - 2014-01-28 21:30

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?

Justin Warkentin added a comment - 2014-01-28 21:30 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?

Elena Stepanova added a comment - 2014-01-28 21:37

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.

Elena Stepanova added a comment - 2014-01-28 21:37 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.

Justin Warkentin added a comment - 2014-01-28 22:00

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.

Justin Warkentin added a comment - 2014-01-28 22:00 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.

Elena Stepanova added a comment - 2014-01-28 22:03

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.

Elena Stepanova added a comment - 2014-01-28 22:03 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.

Justin Warkentin added a comment - 2014-01-28 22:15

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.

Justin Warkentin added a comment - 2014-01-28 22:15 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.

Justin Warkentin made changes - 2014-01-28 22:18

Affects Version/s		5.5.33a-galera [ 13600 ]
Affects Version/s		5.5.34-galera [ 13900 ]
Affects Version/s	5.5.34 [ 13700 ]

Elena Stepanova added a comment - 2014-01-28 22:26

Assigning to Nirbhay to determine whether it's a bug or an known limitation, and proceed accordingly.

Elena Stepanova added a comment - 2014-01-28 22:26 Assigning to Nirbhay to determine whether it's a bug or an known limitation, and proceed accordingly.

Elena Stepanova made changes - 2014-01-28 22:26

Fix Version/s		5.5.35-galera [ 14300 ]
Assignee	Elena Stepanova [ elenst ]	Nirbhay Choubey [ nirbhay_c ]
Due Date	2014-02-28

Elena Stepanova made changes - 2014-01-28 22:27

Labels

galera

Sergei Golubchik made changes - 2014-02-14 19:16

Fix Version/s		5.5.36-galera [ 14801 ]
Fix Version/s	5.5.35-galera [ 14300 ]

Nirbhay Choubey (Inactive) made changes - 2014-03-01 05:40

Status

Open [ 1 ]

In Progress [ 3 ]

Nirbhay Choubey (Inactive) made changes - 2014-03-01 06:17

Status

In Progress [ 3 ]

Stalled [ 10000 ]

Nirbhay Choubey (Inactive) made changes - 2014-03-01 06:17

Status

Stalled [ 10000 ]

In Progress [ 3 ]

Nirbhay Choubey (Inactive) made changes - 2014-03-01 06:17

Resolution		Fixed [ 1 ]
Status	In Progress [ 3 ]	Closed [ 6 ]

Sergei Golubchik made changes - 2014-06-13 15:06

Workflow

defaullt [ 33712 ]

MariaDB v2 [ 42904 ]

Rasmus Johansson (Inactive) made changes - 2015-05-18 17:50

Workflow

MariaDB v2 [ 42904 ]

MariaDB v3 [ 61932 ]

Sergei Golubchik made changes - 2021-12-06 21:39

Workflow

MariaDB v3 [ 61932 ]

MariaDB v4 [ 147425 ]

People

Assignee:: Nirbhay Choubey (Inactive)

Reporter:: Justin Warkentin

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2014-01-22 20:41

Updated:: 2014-03-01 06:17

Resolved:: 2014-03-01 06:17

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Activity

People

Dates

Git Integration