Bug #105871 auto_increment_offset and auto_increment_increment not effiect

• Submitted: 12 Dec 2021 13:58
• Modified: 13 Dec 2021 13:18
• Reporter: HULONG CUI
• Status: Verified
• Category: MySQL Server: InnoDB storage engine
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[12 Dec 2021 13:58] HULONG CUI

Description:
when i setting mysql auto_increment_offset and auto_increment_increment  not default  value。
the table not match the values。
i think this a bug

How to repeat:
1.set my.cnf auto values
[mysqld]
innodb_autoinc_lock_mode =2
auto_increment_increment=1
auto_increment_offset=5

2.restart mysql server
root@localhost: 21:51:  [(none)]> use db8;
Database changed
root@localhost: 21:52:  [db8]> drop table t_user;
Query OK, 0 rows affected (0.03 sec)

root@localhost: 21:52:  [db8]> CREATE TABLE `t_user` (
    ->   `id` int  unsigned  NOT NULL AUTO_INCREMENT,
    ->   `name` varchar(30)  NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

root@localhost: 21:52:  [db8]> 
root@localhost: 21:52:  [db8]> SHOW VARIABLES WHERE  variable_name LIKE 'auto_incre%' OR variable_name LIKE 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 5     |
| innodb_autoinc_lock_mode | 2     |
+--------------------------+-------+
3 rows in set (0.01 sec)

root@localhost: 21:52:  [db8]> 
root@localhost: 21:52:  [db8]> 
root@localhost: 21:52:  [db8]> insert into t_user(name)values('A');
Query OK, 1 row affected (0.00 sec)

root@localhost: 21:52:  [db8]> insert into t_user(name)values('B');
Query OK, 1 row affected (0.00 sec)

root@localhost: 21:52:  [db8]> select * from t_user;
+----+------+
| id | name |
+----+------+
|  1 | A    |
|  2 | B    |
+----+------+
2 rows in set (0.00 sec)

[13 Dec 2021 13:18] MySQL Verification Team

Hi Mr. CUI,

Thank you for your bug report.

We managed to repeat it fully, although we tried many different approaches:

Variable_name	Value
auto_increment_increment	1
auto_increment_offset	5
innodb_autoinc_lock_mode	2
id	name
1	A
2	B
3	C

Simply, auto_increment_offset does not work any more with InnoDB.

Severity is reduced, because of the very easy workaround, which is to set only first value explicitly. 

Verified as reported.