Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.3.7
-
None
-
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