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

Galera should replicate nextval()-related changes in sequences with INCREMENT <> 0, at least NOCACHE ones with engine=InnoDB

    XMLWordPrintable

    Details

      Description

      Consider the following text case (on a local 2-node Galera cluster):

      ...
      MariaDB [test]> create sequence s3 increment 1 nocache engine=InnoDB;
      Query OK, 0 rows affected (0.375 sec)
       
      MariaDB [test]> select nextval(s3), @@wsrep_node_name;
      +-------------+-------------------+
      | nextval(s3) | @@wsrep_node_name |
      +-------------+-------------------+
      |           1 | node2             |
      +-------------+-------------------+
      1 row in set (0.035 sec)
       
      MariaDB [test]> select nextval(s3), @@wsrep_node_name;
      +-------------+-------------------+
      | nextval(s3) | @@wsrep_node_name |
      +-------------+-------------------+
      |           2 | node2             |
      +-------------+-------------------+
      1 row in set (0.002 sec)
       
      MariaDB [test]> exit
      Bye
      openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mysql-node1.sock test
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 11
      Server version: 10.5.14-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> select nextval(s3), @@wsrep_node_name;
      +-------------+-------------------+
      | nextval(s3) | @@wsrep_node_name |
      +-------------+-------------------+
      |           1 | node1             |
      +-------------+-------------------+
      1 row in set (0.002 sec)
       
      MariaDB [test]> select nextval(s3), @@wsrep_node_name;
      +-------------+-------------------+
      | nextval(s3) | @@wsrep_node_name |
      +-------------+-------------------+
      |           2 | node1             |
      +-------------+-------------------+
      1 row in set (0.002 sec)
       
      MariaDB [test]> create table t1(id int);
      Query OK, 0 rows affected (0.340 sec)
       
      MariaDB [test]> insert into t1 select nextval(s3);
      Query OK, 1 row affected (0.015 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from t1;
      +------+
      | id   |
      +------+
      |    3 |
      +------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> exit
      Bye
      openxs@ao756:~/dbs/maria10.5$ bin/mysql --socket=/tmp/mysql-node2.sock test
      Reading table information for completion of table and column names
      You can turn off this feature to get a quicker startup with -A
       
      Welcome to the MariaDB monitor.  Commands end with ; or \g.
      Your MariaDB connection id is 11
      Server version: 10.5.14-MariaDB-log MariaDB Server
       
      Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
       
      Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
       
      MariaDB [test]> select * from t1;
      +------+
      | id   |
      +------+
      |    3 |
      +------+
      1 row in set (0.001 sec)
       
      MariaDB [test]> insert into t1 select nextval(s3);
      Query OK, 1 row affected (0.037 sec)
      Records: 1  Duplicates: 0  Warnings: 0
       
      MariaDB [test]> select * from t1;
      +------+
      | id   |
      +------+
      |    3 |
      |    3 |
      +------+
      2 rows in set (0.001 sec)
       
      MariaDB [test]> show create sequence s3\G
      *************************** 1. row ***************************
             Table: s3
      Create Table: CREATE SEQUENCE `s3` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 nocache nocycle ENGINE=InnoDB
      1 row in set (0.000 sec)
       
      MariaDB [test]> show create table s3\G
      *************************** 1. row ***************************
             Table: s3
      Create Table: CREATE TABLE `s3` (
        `next_not_cached_value` bigint(21) NOT NULL,
        `minimum_value` bigint(21) NOT NULL,
        `maximum_value` bigint(21) NOT NULL,
        `start_value` bigint(21) NOT NULL COMMENT 'start value when sequences is created or value if RESTART is used',
        `increment` bigint(21) NOT NULL COMMENT 'increment value',
        `cache_size` bigint(21) unsigned NOT NULL,
        `cycle_option` tinyint(1) unsigned NOT NULL COMMENT '0 if no cycles are allowed, 1 if the sequence should begin a new cycle when maximum_value is passed',
        `cycle_count` bigint(21) NOT NULL COMMENT 'How many cycles have been done'
      ) ENGINE=InnoDB SEQUENCE=1
      1 row in set (0.001 sec)
      

      Normally Galera replicates changes in InnoDB table, that must happen for NOCACHE sequence with engine=InnoDB. In the test above we see that changes to back end InnoDB table seem NOT to be replicated neither for SELEXT (kind of expected) nor for INSERT (which must produce a write set to replicate).

      As a result we can not use sequences with INCREMENT<>0 in Galera the same way as on standalone node. As I fail to find where is it documented as a limitation I'd say it's a bug actually.

        Attachments

          Activity

            People

            Assignee:
            mkaruza Mario Karuza
            Reporter:
            valerii Valerii Kravchuk
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.