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
