[MDEV-5490] "WSREP: SQL statement was ineffective" Causing Deadlock? Created: 2013-12-26 Updated: 2013-12-27 Resolved: 2013-12-27 |
|
| Status: | Closed |
| Project: | MariaDB Server |
| Component/s: | None |
| Affects Version/s: | 5.5.34-galera |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major |
| Reporter: | Chris Staley | Assignee: | Nirbhay Choubey (Inactive) |
| Resolution: | Not a Bug | Votes: | 0 |
| Labels: | galera | ||
| Environment: |
Debian 7 64bit |
||
| Description |
|
I'm not sure what's going on here, and I may be a little crazy, but I've been able to replicate this in a very simple manor, and I just hope I'm missing something obvious. I've created a table with the structure of: CREATE TABLE IF NOT EXISTS `test` ( And inserted a single row Now, if I run the following query: I receive the error of: Originally we have a galera cluster set up with 5 nodes on it. For the sake of ruling out collisions I've stopped all but one node. The cluster does not receive any traffic yet, as we're prepping to move our production sites to it. I will copy / paste my command line output showing the structure and queries that work / don't work below. I have mysqld running in the console of the one node, and the console is showing: 131223 14:45:26 [Warning] WSREP: SQL statement was ineffective, THD: 5442, buf: 105 Every time I receive the deadlock error. Which that error in itself doesn't really seem to be an error. However, because it is for some reason thinking it's a deadlock, it's trying to run it 4 times, because I have wsrep_retry_autocommit = 3. mysql> CREATE DATABASE `test`; mysql> USE `test`; mysql> INSERT INTO `test` (`id`, `val`) VALUES mysql> UPDATE `test` SET `val` = 1 WHERE `id` = 1; mysql> UPDATE `test` SET `val` = 2 WHERE `id` = 1;
---
--- mysql> UPDATE `test` SET `val` = 2 WHERE `id` = 1; mysql> SELECT * FROM `test`;
---
--- mysql> I've also tested this with a table that does have a primary key set. |
| Comments |
| Comment by Chris Staley [ 2013-12-26 ] |
|
I was just able to replicate this on a base install of Debian 7.3. Basically I installed the mariadb galera package: Modified the my.cnf file and put in the following:
Then ran the following commands in mysql: create database test; The last command showed the following error: |
| Comment by Nirbhay Choubey (Inactive) [ 2013-12-26 ] |
|
Hi Chris, What is the current binary log format on the server? (SELECT @@binlog_format |
| Comment by Chris Staley [ 2013-12-26 ] |
|
It's showing as being "STATEMENT" |
| Comment by Nirbhay Choubey (Inactive) [ 2013-12-26 ] |
|
Ok. Could you please restart the server with --binlog-format=row and retry the queries? Galera requires ROW binlog-format to generate the writesets. |
| Comment by Chris Staley [ 2013-12-27 ] |
|
Okay, now I feel like a fool. Something so simple, and upon reviewing the documentation it's right there saying that it has to be set. Oh the hair I've lost because of this. Thank you for your assistance. |