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

Sequence does not follow new auto_increment_offset after another restart

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Not a Bug
    • 10.5
    • N/A
    • Galera
    • 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)]>

      Attachments

        Activity

          People

            jplindst Jan Lindström (Inactive)
            frelist William Wong
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

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