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

Sequences not replicating between Galera nodes

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.5.8
    • Fix Version/s: 10.5
    • Component/s: Galera, Sequences
    • Labels:
      None
    • Environment:
      CentOS 7.7, Rocky 8.3 RC1

      Description

      In time order:

      Node 1

      MariaDB [test]> CREATE SEQUENCE `account_service_seq` start with 1 minvalue 1 maxvalue 9223372036854775806 increment by 1 cache 2 nocycle ENGINE=InnoDB;
      Query OK, 0 rows affected (0.143 sec)
       
      MariaDB [test]> show create table account_service_seq\G
      *************************** 1. row ***************************
             Table: account_service_seq
      Create Table: CREATE TABLE `account_service_seq` (
        `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.000 sec)
       
      MariaDB [test]> select now(), NEXTVAL(account_service_seq) ;
      +---------------------+------------------------------+
      | now()               | NEXTVAL(account_service_seq) |
      +---------------------+------------------------------+
      | 2021-05-20 12:22:42 |                            1 |
      +---------------------+------------------------------+
      1 row in set (0.001 sec)
      

      Node 2

      MariaDB [test]> select now(), NEXTVAL(account_service_seq) ;
      +---------------------+------------------------------+
      | now()               | NEXTVAL(account_service_seq) |
      +---------------------+------------------------------+
      | 2021-05-20 12:24:19 |                            1 |
      +---------------------+------------------------------+
      1 row in set (0.003 sec)
      

      Node 3

      MariaDB [test]> select now(), NEXTVAL(account_service_seq) ;
      +---------------------+------------------------------+
      | now()               | NEXTVAL(account_service_seq) |
      +---------------------+------------------------------+
      | 2021-05-20 12:24:23 |                            1 |
      +---------------------+------------------------------+
      1 row in set (0.004 sec)
       
      MariaDB [test]> select now(), NEXTVAL(account_service_seq) ;
      +---------------------+------------------------------+
      | now()               | NEXTVAL(account_service_seq) |
      +---------------------+------------------------------+
      | 2021-05-20 12:24:24 |                            2 |
      +---------------------+------------------------------+
      1 row in set (0.001 sec)
      

      Node 2

      MariaDB [test]> select now(), NEXTVAL(account_service_seq) ;
      +---------------------+------------------------------+
      | now()               | NEXTVAL(account_service_seq) |
      +---------------------+------------------------------+
      | 2021-05-20 12:24:29 |                            2 |
      +---------------------+------------------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> select now(), NEXTVAL(account_service_seq) ;
      +---------------------+------------------------------+
      | now()               | NEXTVAL(account_service_seq) |
      +---------------------+------------------------------+
      | 2021-05-20 12:24:44 |                            3 |
      +---------------------+------------------------------+
      1 row in set (0.003 sec)
      

      Node 1

      MariaDB [test]> select now(), NEXTVAL(account_service_seq) ;
      +---------------------+------------------------------+
      | now()               | NEXTVAL(account_service_seq) |
      +---------------------+------------------------------+
      | 2021-05-20 12:24:47 |                            2 |
      +---------------------+------------------------------+
      1 row in set (0.000 sec)
      

      ...so even with InnoDB sequences, the sequence does not appear to replicate at all between nodes.

        Attachments

          Activity

            People

            Assignee:
            jplindst Jan Lindström
            Reporter:
            juan.vera Juan
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration