[MDEV-27120] Sequence does not follow new auto_increment_offset after another restart Created: 2021-11-24  Updated: 2022-10-04  Resolved: 2022-10-03

Status: Closed
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 10.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: William Wong Assignee: Jan Lindström (Inactive)
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

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)]>

 Comments   
Comment by William Wong [ 2021-11-25 ]

Additional comment of DB parameters when issue happens

wsrep_auto_increment_control=ON
innodb_autoinc_lock_mode = 2

Comment by Jan Lindström (Inactive) [ 2022-10-03 ]

Works as designed. If wsrep_auto_increment_control is ON it means that increment is same as number of nodes in the cluster and offset is node wsrep_node_index + 1. If you do not want wsrep to handle these automatically set wsrep_auto_increment_control=OFF;

Comment by William Wong [ 2022-10-04 ]

Hi Jan,

The problem is below :
"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."

DB sessions keep using old offset while auto_increment_offset is changed already. As a result, the whole cluster cannot guarantee unique sequence among DB nodes.

Regards,
William Wong

Comment by Jan Lindström (Inactive) [ 2022-10-04 ]

frelist auto_increment_offset problem will be fixed on MDEV-29142.

Generated at Thu Feb 08 09:50:29 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.