[Maria-discuss] INSERT IGNORE (and other SQL commands) fail with: Deadlock found when trying to get lock;
Hello We have migrated a mailserver project from MySQL NDBCluster to MariaDB Gallera Cluster because we often had sync problems with NDBCluster. Now we found that various of our tools and also our SOAP API Server do not work anymore because some SQL Statements that used to work on NDBCluster do not work with Gallera anymore. I wonder if we stumbled over bugs or if this is some kind of expected behaviour and if yes, how to work around them. We run the most actual version: 5.5.33a Some examples: We have a skript, that from time to time wants to completely delete some statistics data. We don't know if there is already data in that table, we just want to get rid of it... Just some examples how to reproduce the problem: === DELETE on EMPTY TABLE === MariaDB [maildb]> create table statistics (id int(11) primary key, data varchar(30)); Query OK, 0 rows affected (0.80 sec) MariaDB [maildb]> insert into statistics set id=1,data="FooBar"; Query OK, 1 row affected (0.04 sec) MariaDB [maildb]> delete from statistics; Query OK, 1 row affected (0.26 sec) MariaDB [maildb]> delete from statistics; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction Why the heck do we get a severe error when deleting data from an empty table. This causes the perl skipt to die and not continue it's execution. === INSERT IGNORE === We merge information about users from two sources. One source contains the password. One source might contain additional user which were not added from the first source, but do not contain the passwort. But they also contain users already inserted from the first source, so if a unique key exists, it must not be overwroten. Therefore we can not use REPLACE INTO but would use INSERT IGNORE: MariaDB [maildb]> show create table user\G *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(30) DEFAULT NULL, `passwd` varchar(30) DEFAULT NULL, `domain` varchar(30) DEFAULT NULL, `homeserver` varchar(30) DEFAULT NULL, `quotaInMiB` int(11) DEFAULT '100', `password_type` varchar(10) DEFAULT 'plain', `spamaction` enum('REJECT','ACCEPT','TAGGING') DEFAULT 'REJECT', `spampoint` decimal(5,1) DEFAULT '5.2', `virusaction` enum('REJECT','ACCEPT','REPLACE') DEFAULT 'REJECT', `keepFwdCopy` tinyint(1) DEFAULT '0', `quotaUsed` int(11) DEFAULT '0', `extRef` varchar(12) DEFAULT NULL, `mandate` int(11) DEFAULT '0', `FwdBounceCount` decimal(1,0) DEFAULT '0', `FwdActive` tinyint(1) DEFAULT '0', `lastActive` datetime DEFAULT '0000-00-00 00:00:00', `lastLogin` datetime DEFAULT '0000-00-00 00:00:00', `disabled` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `userid` (`userid`,`domain`) ) ENGINE=InnoDB AUTO_INCREMENT=1808 DEFAULT CHARSET=utf8 First Source: MariaDB [maildb]> insert into user set userid="dummyuser",domain="example.com",passwd="verysecret"; Query OK, 1 row affected (0.00 sec) Second Source: Now we get a user that does not already exist, but for which we do not have a password: MariaDB [maildb]> INSERT IGNORE user set userid="otheruser",domain="example.com",passwd="random1"; Query OK, 1 row affected (0.00 sec) Now that second source provides another user, which already exists in the database, so we do want to ignore that insert: MariaDB [maildb]> INSERT IGNORE user set userid="dummyuser",domain="example.com",passwd="otherrandom1"; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction What happened there? In NDBCluster this works perfectly. Gallera fails, that merge skipt stopps. Google suggested replacing INSERT IGNORE by ON DUPLICATE KEY update id=id to just do a dummy update, but that fails too: MariaDB [maildb]> INSERT user set userid="dummyuser",domain="example.com",passwd="otherrandom1" ON DUPLICATE KEY UPDATE id=id; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction Any suggestions? Benoit Panizzon -- I m p r o W a r e A G - ______________________________________________________ Zurlindenstrasse 29 Tel +41 61 826 93 07 CH-4133 Pratteln Fax +41 61 826 93 02 Schweiz Web http://www.imp.ch ______________________________________________________
Hi Benoit, DELETE bug seems to be present only in MariaDB version of Galera cluster, hopefully it will be fixed soon. If it is of any help, you could upgrade to the latest upstream release from Codership. INSERT bug is confirmed in the upstream and is tracked here: https://bugs.launchpad.net/codership-mysql/5.6/+bug/1255147 Regards, Alex On 2013-11-26 16:12, Benoit Panizzon wrote:
Hello
We have migrated a mailserver project from MySQL NDBCluster to MariaDB Gallera Cluster because we often had sync problems with NDBCluster.
Now we found that various of our tools and also our SOAP API Server do not work anymore because some SQL Statements that used to work on NDBCluster do not work with Gallera anymore.
I wonder if we stumbled over bugs or if this is some kind of expected behaviour and if yes, how to work around them.
We run the most actual version: 5.5.33a
Some examples:
We have a skript, that from time to time wants to completely delete some statistics data. We don't know if there is already data in that table, we just want to get rid of it...
Just some examples how to reproduce the problem:
=== DELETE on EMPTY TABLE ===
MariaDB [maildb]> create table statistics (id int(11) primary key, data varchar(30)); Query OK, 0 rows affected (0.80 sec)
MariaDB [maildb]> insert into statistics set id=1,data="FooBar"; Query OK, 1 row affected (0.04 sec)
MariaDB [maildb]> delete from statistics; Query OK, 1 row affected (0.26 sec)
MariaDB [maildb]> delete from statistics; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Why the heck do we get a severe error when deleting data from an empty table. This causes the perl skipt to die and not continue it's execution.
=== INSERT IGNORE ===
We merge information about users from two sources. One source contains the password. One source might contain additional user which were not added from the first source, but do not contain the passwort. But they also contain users already inserted from the first source, so if a unique key exists, it must not be overwroten. Therefore we can not use REPLACE INTO but would use INSERT IGNORE:
MariaDB [maildb]> show create table user\G *************************** 1. row *************************** Table: user Create Table: CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `userid` varchar(30) DEFAULT NULL, `passwd` varchar(30) DEFAULT NULL, `domain` varchar(30) DEFAULT NULL, `homeserver` varchar(30) DEFAULT NULL, `quotaInMiB` int(11) DEFAULT '100', `password_type` varchar(10) DEFAULT 'plain', `spamaction` enum('REJECT','ACCEPT','TAGGING') DEFAULT 'REJECT', `spampoint` decimal(5,1) DEFAULT '5.2', `virusaction` enum('REJECT','ACCEPT','REPLACE') DEFAULT 'REJECT', `keepFwdCopy` tinyint(1) DEFAULT '0', `quotaUsed` int(11) DEFAULT '0', `extRef` varchar(12) DEFAULT NULL, `mandate` int(11) DEFAULT '0', `FwdBounceCount` decimal(1,0) DEFAULT '0', `FwdActive` tinyint(1) DEFAULT '0', `lastActive` datetime DEFAULT '0000-00-00 00:00:00', `lastLogin` datetime DEFAULT '0000-00-00 00:00:00', `disabled` tinyint(1) DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `userid` (`userid`,`domain`) ) ENGINE=InnoDB AUTO_INCREMENT=1808 DEFAULT CHARSET=utf8
First Source: MariaDB [maildb]> insert into user set userid="dummyuser",domain="example.com",passwd="verysecret"; Query OK, 1 row affected (0.00 sec)
Second Source:
Now we get a user that does not already exist, but for which we do not have a password:
MariaDB [maildb]> INSERT IGNORE user set userid="otheruser",domain="example.com",passwd="random1"; Query OK, 1 row affected (0.00 sec)
Now that second source provides another user, which already exists in the database, so we do want to ignore that insert:
MariaDB [maildb]> INSERT IGNORE user set userid="dummyuser",domain="example.com",passwd="otherrandom1"; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
What happened there? In NDBCluster this works perfectly. Gallera fails, that merge skipt stopps.
Google suggested replacing INSERT IGNORE by ON DUPLICATE KEY update id=id to just do a dummy update, but that fails too:
MariaDB [maildb]> INSERT user set userid="dummyuser",domain="example.com",passwd="otherrandom1" ON DUPLICATE KEY UPDATE id=id; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Any suggestions?
Benoit Panizzon
-- Alexey Yurchenko, Codership Oy, www.codership.com Skype: alexey.yurchenko, Phone: +358-400-516-011
Hi Alex
DELETE bug seems to be present only in MariaDB version of Galera cluster, hopefully it will be fixed soon. If it is of any help, you could upgrade to the latest upstream release from Codership.
INSERT bug is confirmed in the upstream and is tracked here: https://bugs.launchpad.net/codership-mysql/5.6/+bug/1255147
Thank you. I managed to work around those problems. Now a new 'Deadlock' problem arrises from time to time. When a user successfully logs in to the IMAP server, we register the timestamp of that last login: "UPDATE user set lastLogin=Now() where concat(userid,'@',domain)='$USER'" In about 4 of 5 cases, this is successfull. In one of 5 cases we get a Deadlock error. Any hints what causes that problem? Can't we user NOW() because not all of the cluster members might have the same timestamp? Mit freundlichen Grüssen Benoit Panizzon -- I m p r o W a r e A G - ______________________________________________________ Zurlindenstrasse 29 Tel +41 61 826 93 07 CH-4133 Pratteln Fax +41 61 826 93 02 Schweiz Web http://www.imp.ch ______________________________________________________
Hi, That is going to lock ALL of the rows in `user` because index traversal will cover the PRIMARY KEY over all rows looking for a match, and InnoDB locks the rows it traverses. split $USER into userid and domain components and do userid = $USER_ID and domain = $USER_DOMAIN Assuming you have a unique key on (USER_ID,DOMAIN) you will only lock one row and conflicts will be avoided. You can also try using READ COMMITTED instead of REPEATABLE READ if you can't make that change, but you will still get some deadlocks. --Justin On Thu, Dec 12, 2013 at 4:28 AM, Benoit Panizzon <benoit.panizzon@imp.ch>wrote:
Hi Alex
DELETE bug seems to be present only in MariaDB version of Galera cluster, hopefully it will be fixed soon. If it is of any help, you could upgrade to the latest upstream release from Codership.
INSERT bug is confirmed in the upstream and is tracked here: https://bugs.launchpad.net/codership-mysql/5.6/+bug/1255147
Thank you. I managed to work around those problems.
Now a new 'Deadlock' problem arrises from time to time.
When a user successfully logs in to the IMAP server, we register the timestamp of that last login:
"UPDATE user set lastLogin=Now() where concat(userid,'@',domain)='$USER'"
In about 4 of 5 cases, this is successfull. In one of 5 cases we get a Deadlock error.
Any hints what causes that problem? Can't we user NOW() because not all of the cluster members might have the same timestamp?
Mit freundlichen Grüssen
Benoit Panizzon -- I m p r o W a r e A G - ______________________________________________________
Zurlindenstrasse 29 Tel +41 61 826 93 07 CH-4133 Pratteln Fax +41 61 826 93 02 Schweiz Web http://www.imp.ch ______________________________________________________
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
participants (3)
-
Alex Yurchenko -
Benoit Panizzon -
Justin Swanhart