[MDEV-32823] Sequences may diverge with Galera Created: 2023-11-16  Updated: 2023-12-18

Status: Open
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 10.4
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Daniele Sciascia Assignee: Daniele Sciascia
Resolution: Unresolved Votes: 0
Labels: None


 Description   

Sequences may diverge when used in a Galera cluster.
The following example:

connection node1;
CREATE SEQUENCE s1 NOCACHE ENGINE=InnoDB;  
BEGIN;          
SELECT NEXTVAL(s1);
NEXTVAL(s1);
1 
ROLLBACK;
 
connection node2;
SELECT NEXTVAL(s1);
NEXTVAL(s1)
1

shows that it is possible to consume a value from a sequence on one node, and that value may end up being be reused on a different node. For this to happen the sequence must be used within a transaction that is rolled back (as shown above).
The issue comes from the fact that Galera does not replicate any writeset on ROLLBACK, and because of the non-transactional nature of sequences. That is, when a value is consumed it will commit even if the transaction is rolled back.

Also, if binlog is enable, they will diverge. On node 1 we have two events, for the corresponding calls to SELECT NEXTVAL(s1):

Gtid    1       564     BEGIN GTID 0-1-2
Annotate_rows   1       605     SELECT NEXTVAL(s1)
Table_map       1       657     table_id: 58 (test.s1)
Write_rows_v1   1       748     table_id: 58 flags: STMT_END_F
Query   1       826     COMMIT
Gtid    2       868     BEGIN GTID 0-2-3
Annotate_rows   2       909     SELECT NEXTVAL(s1)
Table_map       2       961     table_id: 58 (test.s1)
Write_rows_v1   2       1052    table_id: 58 flags: STMT_END_F
Query   2       1121    COMMIT

While node 2, not only misses the events from node 1, it also has a different gtid:

Gtid    2       520     BEGIN GTID 0-2-2
Annotate_rows   2       561     SELECT NEXTVAL(s1)
Table_map       2       613     table_id: 38 (test.s1)
Write_rows_v1   2       704     table_id: 38 flags: STMT_END_F
Query   2       782     COMMIT

In the example above, we used voluntary ROLLBACK. The same issue is likely to happen if a transaction using sequences is rolled back due to cluster wide conflict.


Generated at Thu Feb 08 10:34:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.