| Bug #118314 | TRUNCATE TABLE does not reset AUTO_INCREMENT in MySQL 8.0.41 | ||
|---|---|---|---|
| Submitted: | 30 May 11:08 | Modified: | 31 May 13:39 |
| Reporter: | Chandana DA | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 8.0.41 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[31 May 13:39]
MySQL Verification Team
Hi, this is not a bug since the I_S table is cached (which is where SHOW TABLE STATUS gets it's info).
Run ANALYZE TABLE to update it.
--
mysql> create table t(a int auto_increment primary key);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into t values(),(),(),(),();
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> show table status like 't' \G
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 6 <<<<<<<<<<<<<<<<<<
Create_time: 2025-05-31 15:35:58
Update_time: 2025-05-31 15:36:08
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
mysql> show create table t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> truncate t;
Query OK, 0 rows affected (0.03 sec)
mysql> show table status like 't' \G
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 5
Avg_row_length: 3276
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 6 <<<<<<<<<<<<<<<<<<<<<<<<<<<<
Create_time: 2025-05-31 15:35:58
Update_time: 2025-05-31 15:36:08
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> show create table t \G
*************************** 1. row ***************************
Table: t
Create Table: CREATE TABLE `t` (
`a` int NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
mysql> analyze table t;
+--------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------+---------+----------+----------+
| test.t | analyze | status | OK |
+--------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> show table status like 't' \G
*************************** 1. row ***************************
Name: t
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1 <<<<<<<<<<<<<<<<<<<<<<<
Create_time: 2025-05-31 15:35:58
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
--
--
Shane, MySQL Senior Principal Technical Support Engineer
Oracle Corporation
http://dev.mysql.com/
[31 May 13:41]
MySQL Verification Team
you can try setting this variable to zero 0: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_information_sc...
Description: In MySQL 8.0.41 running on Amazon RDS, the TRUNCATE TABLE command does not reset the AUTO_INCREMENT counter as expected. Expected behavior: TRUNCATE TABLE should drop and recreate the table, thereby resetting the AUTO_INCREMENT value to 1. Actual behavior: After truncating the table, the AUTO_INCREMENT value continues from the last inserted value. There are no foreign keys, no triggers, and full privileges are present. ANALYZE TABLE does not fix the issue. This may be an RDS-specific regression or an issue in MySQL 8.0.41. How to repeat: CREATE DATABASE IF NOT EXISTS testing; USE testing; DROP TABLE IF EXISTS test_auto_increment; CREATE TABLE test_auto_increment ( id INT AUTO_INCREMENT PRIMARY KEY, data VARCHAR(100) ) ENGINE=InnoDB; -- Insert some rows INSERT INTO test_auto_increment (data) VALUES ('Row 1'), ('Row 2'), ('Row 3'); -- Check AUTO_INCREMENT value SHOW TABLE STATUS LIKE 'test_auto_increment'\G ======== SHOW TABLE STATUS LIKE 'test_auto_increment'\G *************************** 1. row *************************** Name: test_auto_increment Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 3 Avg_row_length: 5461 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 4 Create_time: 2025-05-30 10:50:12 Update_time: 2025-05-30 10:50:31 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.003 sec) ===================== -- Truncate the table TRUNCATE TABLE test_auto_increment; -- Check AUTO_INCREMENT value again SHOW TABLE STATUS LIKE 'test_auto_increment'\G =========== SHOW TABLE STATUS LIKE 'test_auto_increment'\G *************************** 1. row *************************** Name: test_auto_increment Engine: InnoDB Version: 10 Row_format: Dynamic Rows: 3 Avg_row_length: 5461 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 4 Create_time: 2025-05-30 10:50:12 Update_time: 2025-05-30 10:50:31 Check_time: NULL Collation: utf8mb4_0900_ai_ci Checksum: NULL Create_options: Comment: 1 row in set (0.002 sec) =========== SELECT * FROM information_schema.referential_constraints -> WHERE table_name = 'test_auto_increment'; Empty set (0.002 sec) MySQL [test]> SHOW TRIGGERS LIKE 'test_auto_increment'; Empty set (0.001 sec) -- The AUTO_INCREMENT value should reset to 1, but instead remains at 4.