Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Not a Bug
-
Affects Version/s: 5.5.35-galera, 5.5.36-galera
-
Fix Version/s: 5.5.38-galera
-
Component/s: None
-
Labels:
-
Environment:Main system:
- CentOS 6.3 x64
- MariaDB-Galera-server-5.5.36-1.el6.x86_64
- galera-25.3.2-1.rhel6.x86_64
Tested with multiple systems and versions (see description for details).
Description
We have a Galera cluster containing two MariaDB servers with multi-master replication.
When I try to update a field inside a table using the value the field already has (e. g. update value "test" with "test") I get the following error, no matter if integer, boolean or string.
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
Below you can find the steps how to reproduce this. I also tried that on a MariaDB server without Galera and there it was executed without any errors:
Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0
I've already posted this inside the Galera bug list and received the following answer:
Can't be reproduced with the reference Codership binaries. This must be a MariaDB-specific bug and should be filed with them.
Used systems:
- CentOS 6.3 x64
- MariaDB-Galera-server-5.5.35-1.el6.x86_64
- galera-25.3.2-1.rhel6.x86_64
- CentOS 6.3 x64
- MariaDB-Galera-server-5.5.36-1.el6.x86_64
- galera-25.3.2-1.rhel6.x86_64
- CentOS 6.5 x64
- MariaDB-server-5.5.36-1.el6.x86_64
- no Galera packages installed (the case where no error occurred)
Edit: This bug seems to be already fixed in MariaDB version 10.
Successfully tested with:
- CentOS 6.3 x64
- MariaDB-Galera-server-10.0.7-1.x86_64
- galera-25.3.2-1.rhel6.x86_64
How to reproduce:
CREATE DATABASE mytest;
USE mytest;
CREATE TABLE mytable (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
myint INT DEFAULT 0,
mybool BOOL DEFAULT 0,
mystr VARCHAR(100) DEFAULT ""
);
INSERT INTO `mytest`.`mytable` (`myint`, `mystr`) VALUES ('0', 'test');
UPDATE mytable SET myint = 0 WHERE id = 1;
UPDATE mytable SET mybool = 0 WHERE id = 1;
UPDATE mytable SET mystr = "test" WHERE id = 1;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Hello, Nirbhay!
It looks like binlog-format has not been set?
Thanks a lot for the information! It was not set via command line, but after setting the binlog-format to row (inside the config) it works.
However, same works on 10.0-galera due to a minor (and possibly harmless) difference
Should I explicitly set the binlog-format there anyway?
Thanks in advance!
> Should I explicitly set the binlog-format there anyway?
Yes, you should.
Yes, you should.
Thanks a lot. Would mixed format be okay does it have to be row format for some reason?
Thanks in advance!
Hi Ralf,
Galera uses certification based replication which requires PK of all rows
changed in a transaction alongside the actual change (collectively known
as writeset) in order to determine if the changes can be applied (conflict-free).
The PK information won't be available if the binary logging is statement based
or mixed (which is mostly statement based).
HTH
Thanks!
I see. Thanks again for your help!
Hi Ralf,
I tried to reproduce this issue on a variety of platforms : precise (32 & 64), CentOS 6.5 (32 & 64), different
server versions : 5.5.35, 5.5.36, 5.5.37 and galera version 25.3.2, 25.3.5. But couldn't!
Node 1
=====
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 5
Server version: 5.5.37-MariaDB-wsrep-debug Source distribution, wsrep_25.10.r3980
Copyright (c) 2000, 2014, Oracle, SkySQL Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [test]> CREATE DATABASE mytest;
Query OK, 1 row affected (0.00 sec)
MariaDB [test]> USE mytest;
Database changed
MariaDB [mytest]> CREATE TABLE mytable (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> myint INT DEFAULT 0,
-> mybool BOOL DEFAULT 0,
-> mystr VARCHAR(100) DEFAULT ""
-> );
Query OK, 0 rows affected (0.05 sec)
MariaDB [mytest]> INSERT INTO `mytest`.`mytable` (`myint`, `mystr`) VALUES ('0', 'test');
Query OK, 1 row affected (0.00 sec)
MariaDB [mytest]> UPDATE mytable SET myint = 0 WHERE id = 1;
Query OK, 0 rows affected (0.03 sec)
Rows matched: 1 Changed: 0 Warnings: 0
MariaDB [mytest]> UPDATE mytable SET mybool = 0 WHERE id = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0
MariaDB [mytest]> UPDATE mytable SET mybool = 0 WHERE id = 1;
Query OK, 0 rows affected (0.05 sec)
Rows matched: 1 Changed: 0 Warnings: 0
MariaDB [mytest]> select * from mytable;
---
-----------------+---
-----------------+---
-----------------+1 row in set (0.01 sec)
Node 2
=====
MariaDB [test]> select * from mytest.mytable;
---
-----------------+---
-----------------+---
-----------------+1 row in set (0.00 sec)
Is there something specific that I am missing (besides CentOS 6.3 as I presume
it shouldn't make much difference)?