Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5490

"WSREP: SQL statement was ineffective" Causing Deadlock?

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 5.5.34-galera
    • Fix Version/s: None
    • Component/s: None
    • Labels:
    • 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` (
      `id` int(3) NOT NULL,
      `val` int(3) NOT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

      And inserted a single row
      INSERT INTO `test` (`id`, `val`) VALUES
      (1, 1);

      Now, if I run the following query:
      UPDATE `test` SET `val` = 1 WHERE `id` = 1;

      I receive the error of:
      ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

      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
      QUERY: UPDATE `test` SET `val` = 3 WHERE `id` = 1
      => Skipping replication
      131223 14:45:26 [Note] WSREP: cluster conflict due to certification failure for threads:
      131223 14:45:26 [Note] WSREP: Victim thread:
      THD: 5442, mode: local, state: executing, conflict: cert failure, seqno: -1
      SQL: UPDATE `test` SET `val` = 3 WHERE `id` = 1

      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`;
      Query OK, 1 row affected (0.01 sec)

      mysql> USE `test`;
      Database changed
      mysql> CREATE TABLE IF NOT EXISTS `test` (
      -> `id` int(3) NOT NULL,
      -> `val` int(3) NOT NULL
      -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
      Query OK, 0 rows affected (0.07 sec)

      mysql> INSERT INTO `test` (`id`, `val`) VALUES
      -> (1, 1);
      Query OK, 1 row affected (0.02 sec)

      mysql> UPDATE `test` SET `val` = 1 WHERE `id` = 1;
      ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
      mysql> UPDATE `test` SET `val` = 2 WHERE `id` = 1;
      Query OK, 1 row affected (0.01 sec)
      Rows matched: 1 Changed: 1 Warnings: 0

      mysql> UPDATE `test` SET `val` = 2 WHERE `id` = 1;
      ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
      mysql> SELECT * FROM `test`;
      -------+

      id val

      -------+

      1 2

      -------+
      1 row in set (0.00 sec)

      mysql> UPDATE `test` SET `val` = 2 WHERE `id` = 1;
      ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
      mysql> UPDATE `test` SET `val` = 3 WHERE `id` = 1;
      Query OK, 1 row affected (0.02 sec)
      Rows matched: 1 Changed: 1 Warnings: 0

      mysql> SELECT * FROM `test`;
      -------+

      id val

      -------+

      1 3

      -------+
      1 row in set (0.01 sec)

      mysql>

      I've also tested this with a table that does have a primary key set.
      This was originally reported to https://groups.google.com/forum/#!topic/codership-team/wDtm6I_dZ9U , but they've said that it's most likely a MariaDB specific issue.

        Gliffy Diagrams

          Attachments

            Activity

            Hide
            skeletorsue Chris Staley added a comment -

            I was just able to replicate this on a base install of Debian 7.3.

            Basically I installed the mariadb galera package:
            apt-get install mariadb-galera-server mariadb-galera-server-5.5 mariadb-client-5.5 libmariadbclient18 libmysqlclient18=5.5.34+maria-1~wheezy

            Modified the my.cnf file and put in the following:

            1. wsrep provider configuration: basic wsrep options
              wsrep_provider=/usr/lib/galera/libgalera_smm.so
              wsrep_provider_options="gcache.size=512M"
              wsrep_cluster_address=gcomm://
              wsrep_node_address='192.168.0.210'
              wsrep_cluster_name='Test-Cluster'
              wsrep_node_name='DB-r1'
              wsrep_sst_method=rsync
              innodb_autoinc_lock_mode=2
              innodb_locks_unsafe_for_binlog=1

            Then ran the following commands in mysql:

            create database test;
            use test
            show tables;
            CREATE TABLE IF NOT EXISTS `test` (
            `id` int(3) NOT NULL,
            `val` int(3) NOT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
            CREATE TABLE IF NOT EXISTS `test` ( `id` int(3) NOT NULL, `val` int(3) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
            INSERT INTO `test` (`id`, `val`) VALUES
            (1, 1);
            INSERT INTO `test` (`id`, `val`) VALUES (1, 1);
            UPDATE `test` SET `val` = 1 WHERE `id` = 1;

            The last command showed the following error:
            ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

            Show
            skeletorsue Chris Staley added a comment - I was just able to replicate this on a base install of Debian 7.3. Basically I installed the mariadb galera package: apt-get install mariadb-galera-server mariadb-galera-server-5.5 mariadb-client-5.5 libmariadbclient18 libmysqlclient18=5.5.34+maria-1~wheezy Modified the my.cnf file and put in the following: wsrep provider configuration: basic wsrep options wsrep_provider=/usr/lib/galera/libgalera_smm.so wsrep_provider_options="gcache.size=512M" wsrep_cluster_address=gcomm:// wsrep_node_address='192.168.0.210' wsrep_cluster_name='Test-Cluster' wsrep_node_name='DB-r1' wsrep_sst_method=rsync innodb_autoinc_lock_mode=2 innodb_locks_unsafe_for_binlog=1 Then ran the following commands in mysql: create database test; use test show tables; CREATE TABLE IF NOT EXISTS `test` ( `id` int(3) NOT NULL, `val` int(3) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE IF NOT EXISTS `test` ( `id` int(3) NOT NULL, `val` int(3) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; INSERT INTO `test` (`id`, `val`) VALUES (1, 1); INSERT INTO `test` (`id`, `val`) VALUES (1, 1); UPDATE `test` SET `val` = 1 WHERE `id` = 1; The last command showed the following error: ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
            Hide
            nirbhay_c Nirbhay Choubey added a comment -

            Hi Chris,

            What is the current binary log format on the server? (SELECT @@binlog_format

            Show
            nirbhay_c Nirbhay Choubey added a comment - Hi Chris, What is the current binary log format on the server? (SELECT @@binlog_format
            Hide
            skeletorsue Chris Staley added a comment -

            It's showing as being "STATEMENT"

            Show
            skeletorsue Chris Staley added a comment - It's showing as being "STATEMENT"
            Hide
            nirbhay_c Nirbhay Choubey added a comment -

            Ok. Could you please restart the server with --binlog-format=row and retry the queries?

            Galera requires ROW binlog-format to generate the writesets.

            Show
            nirbhay_c Nirbhay Choubey added a comment - Ok. Could you please restart the server with --binlog-format=row and retry the queries? Galera requires ROW binlog-format to generate the writesets.
            Hide
            skeletorsue Chris Staley added a comment -

            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.

            Show
            skeletorsue Chris Staley added a comment - 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.

              People

              • Assignee:
                nirbhay_c Nirbhay Choubey
                Reporter:
                skeletorsue Chris Staley
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: