Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-32823

Sequences may diverge with Galera

    XMLWordPrintable

Details

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

      Attachments

        Activity

          People

            seppo Seppo Jaakola
            sciascid Daniele Sciascia (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.