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