maria-discuss team mailing list archive

[Benoit Panizzon <benoit.panizzon@xxxxxx>]

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
______________________________________________________