Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.4(EOL)
-
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.