Description:
When creating a table with a specified `AUTO_INCREMENT` value, the first inserted record still starts from `1` instead of the defined starting value.
This issue persists even when running an explicit `ALTER TABLE ... AUTO_INCREMENT = <value>` before inserting any rows.
This behavior occurs consistently in MySQL 8.0.41 (Source distribution, InnoDB engine).
The same SQL works as expected on earlier MySQL 5.7 installations.
It seems that InnoDB ignores the `AUTO_INCREMENT` metadata until a dummy record with a higher `id` is inserted — only then the value is honored.
This appears to be either an **InnoDB internal cache bug** or a **missing initialization step** after table creation.
How to repeat:
```sql
CREATE TABLE shop_of (
shop_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
shop_name VARCHAR(50),
PRIMARY KEY (shop_id)
) ENGINE=InnoDB AUTO_INCREMENT=2000000 COMMENT='official shops';
-- Confirm that AUTO_INCREMENT is correctly set
SHOW TABLE STATUS LIKE 'shop_of';
-- Shows Auto_increment = 2000000
-- Explicitly set again just to be sure
ALTER TABLE shop_of AUTO_INCREMENT = 2000000;
-- Insert the first record (without specifying shop_id)
INSERT INTO shop_of (shop_name) VALUES ('dummy');
-- Check the result
SELECT shop_id FROM shop_of;
-- Returns: 1 (Expected: 2000000)
```
---
### **Expected result**
The first inserted record should have:
```
shop_id = 2000000
```
---
### **Actual result**
The inserted record has:
```
shop_id = 1
```
---
Suggested fix:
### **Workaround**
If a dummy record is inserted manually with a high ID before normal inserts,
the AUTO_INCREMENT value is then respected:
```sql
INSERT INTO shop_of (shop_id, shop_name) VALUES (1999999, 'init');
ALTER TABLE shop_of AUTO_INCREMENT = 2000000;
DELETE FROM shop_of WHERE shop_id = 1999999;
```
After this, subsequent inserts correctly start from `2000000`.
© 2025, Oracle Corporation and/or its affiliates
Description: When creating a table with a specified `AUTO_INCREMENT` value, the first inserted record still starts from `1` instead of the defined starting value. This issue persists even when running an explicit `ALTER TABLE ... AUTO_INCREMENT = <value>` before inserting any rows. This behavior occurs consistently in MySQL 8.0.41 (Source distribution, InnoDB engine). The same SQL works as expected on earlier MySQL 5.7 installations. It seems that InnoDB ignores the `AUTO_INCREMENT` metadata until a dummy record with a higher `id` is inserted — only then the value is honored. This appears to be either an **InnoDB internal cache bug** or a **missing initialization step** after table creation. How to repeat: ```sql CREATE TABLE shop_of ( shop_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, shop_name VARCHAR(50), PRIMARY KEY (shop_id) ) ENGINE=InnoDB AUTO_INCREMENT=2000000 COMMENT='official shops'; -- Confirm that AUTO_INCREMENT is correctly set SHOW TABLE STATUS LIKE 'shop_of'; -- Shows Auto_increment = 2000000 -- Explicitly set again just to be sure ALTER TABLE shop_of AUTO_INCREMENT = 2000000; -- Insert the first record (without specifying shop_id) INSERT INTO shop_of (shop_name) VALUES ('dummy'); -- Check the result SELECT shop_id FROM shop_of; -- Returns: 1 (Expected: 2000000) ``` --- ### **Expected result** The first inserted record should have: ``` shop_id = 2000000 ``` --- ### **Actual result** The inserted record has: ``` shop_id = 1 ``` --- Suggested fix: ### **Workaround** If a dummy record is inserted manually with a high ID before normal inserts, the AUTO_INCREMENT value is then respected: ```sql INSERT INTO shop_of (shop_id, shop_name) VALUES (1999999, 'init'); ALTER TABLE shop_of AUTO_INCREMENT = 2000000; DELETE FROM shop_of WHERE shop_id = 1999999; ``` After this, subsequent inserts correctly start from `2000000`.