Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.5
-
None
-
Redhat on VMware
Description
We found sequence object generate same number from 2 Galera nodes. The increment setting is 0. After checking, the behavior happens after one DB restart gracefully.
Below is from an example, DB node auto_increment_offset=3 and sequence object uses formula : 3 x N +3. Sample values are 9102 , 9105.
When we restart another DB node. auto_increment_offset of this node got updated to 2. But sequence object still keep using original offset 3 and still generate number 9108 which is using offset 3.
Workaround is setting offset manually
wsrep_auto_increment_control=OFF
auto_increment_increment=3
auto_increment_offset=1|2|3
MariaDB [(none)]> show variables like 'auto_increment%' ;
-------------------------------+
Variable_name | Value |
-------------------------------+
auto_increment_increment | 3 |
auto_increment_offset | 3 |
-------------------------------+
2 rows in set (0.001 sec)
MariaDB [(none)]>
MariaDB [(none)]> select nextval(testdb1.s1) ;
---------------------
nextval(testdb1.s1) |
---------------------
9102 |
---------------------
1 row in set (0.011 sec)
MariaDB [(none)]> select nextval(testdb1.s1) ;
---------------------
nextval(testdb1.s1) |
---------------------
9105 |
---------------------
1 row in set (0.000 sec)
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> – restart one of other DB node
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]>
MariaDB [(none)]> show variables like 'auto_increment%' ;
-------------------------------+
Variable_name | Value |
-------------------------------+
auto_increment_increment | 3 |
auto_increment_offset | 2 |
-------------------------------+
2 rows in set (0.001 sec)
MariaDB [(none)]>
MariaDB [(none)]> select nextval(testdb1.s1) ;
---------------------
nextval(testdb1.s1) |
---------------------
9108 |
---------------------
1 row in set (0.000 sec)
MariaDB [(none)]>