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

Sequences with INCREMENT=0 produce wrong values with some combinations of START WITH and auto_increment_* options

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Not a Bug
    • Affects Version/s: 10.3.7
    • Fix Version/s: N/A
    • Component/s: Sequences
    • Labels:
      None
    • Environment:
      CentOS Linux release 7.4.1708 (Core)

      Description

      Sequences produce duplicate initial values in multiple nodes after restarting if sequence has been used on node 1 while node 2 was off-line.

      On a 2 node cluster with a sequence created with

      MariaDB [test]> create sequence s start with 100 increment 0;
      

      as recommended in the documentation

      resulting (on node 1) in:

      MariaDB [test]> show global variables like 'auto_increment_%';
      +--------------------------+-------+
      | Variable_name            | Value |
      +--------------------------+-------+
      | auto_increment_increment | 2     |
      | auto_increment_offset    | 1     |
      +--------------------------+-------+
      2 rows in set (0.003 sec)
       
      MariaDB [test]> select * from s;
      +-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
      | next_not_cached_value | minimum_value | maximum_value       | start_value | increment | cache_size | cycle_option | cycle_count |
      +-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
      |                   100 |             1 | 9223372036854775806 |         100 |         0 |       1000 |            0 |           0 |
      +-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
      1 row in set (0.001 sec)
      

      Now get one value from the sequence:

      MariaDB [test]> select next value for s;
      +------------------+
      | next value for s |
      +------------------+
      |              101 |
      +------------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> select * from s;
      +-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
      | next_not_cached_value | minimum_value | maximum_value       | start_value | increment | cache_size | cycle_option | cycle_count |
      +-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
      |                  2100 |             1 | 9223372036854775806 |         100 |         0 |       1000 |            0 |           0 |
      +-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
      1 row in set (0.000 sec)
      

      So in the case of this 2-node cluster, the server has reserved 999 numbers starting from next_not_cached_value + auto_increment_offset with an interval of auto_increment_increment, resulting in the next available non-cached value being 2100.

      If we now bring down the cluster (first node 2, then node 1), bootstrap node 1 again, we have a problem:

      Node 1:

      ~# galera_new_cluster
      ~# mysql -A test
      MariaDB [test]> select * from s;
      +-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
      | next_not_cached_value | minimum_value | maximum_value       | start_value | increment | cache_size | cycle_option | cycle_count |
      +-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
      |                  4100 |             1 | 9223372036854775806 |         100 |         0 |       1000 |            0 |           0 |
      +-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> select nextval(s);
      +------------+
      | nextval(s) |
      +------------+
      |       4100 |
      +------------+
      1 row in set (0.000 sec)
      

      For some reason, on restart in the bootstrap node the offset is not applied to the next_not_cached_value for the first invocation of "next value".

      now start
      Node 2:

      ~# systemctl start mariadb
      ~# mysql -A test
      MariaDB [test]> select * from s;
      +-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
      | next_not_cached_value | minimum_value | maximum_value       | start_value | increment | cache_size | cycle_option | cycle_count |
      +-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
      |                  4100 |             1 | 9223372036854775806 |         100 |         0 |       1000 |            0 |           0 |
      +-----------------------+---------------+---------------------+-------------+-----------+------------+--------------+-------------+
      1 row in set (0.000 sec)
       
      MariaDB [test]> select next value for s;
      +------------------+
      | next value for s |
      +------------------+
      |             4100 |
      +------------------+
      1 row in set (0.000 sec)
      

      QED

        Attachments

          Activity

            People

            Assignee:
            monty Michael Widenius
            Reporter:
            juan.vera Juan
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: