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

2. auto_increment_increment and auto_increment_offset reset to 1 in current session after alter table on auto-increment column

Details

    Description

      Hi,

      We encounter sequence object issue as MDEV-27120. Then, trying to set 

      wsrep_auto_increment_control=OFF
      		 
      auto_increment_increment=3
      		 
      auto_increment_offset=1|2|3

      However, got another issue in another application using auto-increment.
      auto_increment_increment and auto_increment_offset reset to 1 in current session after alter table with auto-increment column. Reconnect DB will get back correct setting.

      Below see below example:

      MariaDB [testdb1]> show variables like 'auto_increment%' ;
      		 
      +--------------------------+-------+
      		 
      | Variable_name            | Value |
      		 
      +--------------------------+-------+
      		 
      | auto_increment_increment | 3     |
      		 
      | auto_increment_offset    | 2     |
      		 
      +--------------------------+-------+
      		 
      2 rows in set (0.001 sec)
      		 
       
      		 
      MariaDB [testdb1]>
      		 
      MariaDB [testdb1]>
      		 
      MariaDB [testdb1]> create table t4 (c1 int primary key auto_increment) ;
      		 
      Query OK, 0 rows affected (0.009 sec)
      		 
       
      		 
      MariaDB [testdb1]>
      		 
      MariaDB [testdb1]>
      		 
      MariaDB [testdb1]> show variables like 'auto_increment%' ;
      		 
      +--------------------------+-------+
      		 
      | Variable_name            | Value |
      		 
      +--------------------------+-------+
      		 
      | auto_increment_increment | 3     |
      		 
      | auto_increment_offset    | 2     |
      		 
      +--------------------------+-------+
      		 
      2 rows in set (0.001 sec)
      		 
       
      		 
      MariaDB [testdb1]>
      		 
      MariaDB [testdb1]>
      		 
      MariaDB [testdb1]> alter table t4 add (c2 varchar(50)) ;
      		 
      Query OK, 0 rows affected (0.006 sec)
      		 
      Records: 0  Duplicates: 0  Warnings: 0
      		 
       
      		 
      MariaDB [testdb1]>
      		 
      MariaDB [testdb1]>
      		 
      MariaDB [testdb1]> show variables like 'auto_increment%' ;
      		 
      +--------------------------+-------+
      		 
      | Variable_name            | Value |
      		 
      +--------------------------+-------+
      		 
      | auto_increment_increment | 1     |
      		 
      | auto_increment_offset    | 1     |
      		 
      +--------------------------+-------+
      		 
      2 rows in set (0.001 sec)
      		 
       
      		 
      MariaDB [testdb1]>
      		 
      MariaDB [testdb1]>
      		 
      MariaDB [testdb1]>

      Attachments

        Activity

          People

            jplindst Jan Lindström (Inactive)
            frelist William Wong
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.