Bug #41116 mysql server resets AUTO_INCREMENT upon server restart for empty tables
| Submitted: | 28 Nov 2008 23:20 | Modified: | 29 Nov 2008 22:27 |
|---|---|---|---|
| Reporter: | David Anderson | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: General | Severity: | S2 (Serious) |
| Version: | 5.0.51b-community-nt | OS: | Windows (Vista) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | auto_increment, empty table | ||
[29 Nov 2008 1:33]
David Anderson
The error also occurs if I’ve created a non-null record, then deleted it (table now empty of rows), stop, restart server. The error even occurs if I’ve created a non-null record, stop/started server, verified record exists, then delete it (table now empty of rows), stop, restart server.
[29 Nov 2008 22:27]
MySQL Verification Team
Thank you for the bug report. That behavior is a restriction of InnoDB tables, please read: http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html. Below you can notice MyISAM table isn't affected by this behavior: c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.0.74-nt-debug-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql 5.0 > use test Database changed mysql 5.0 > create table i (id serial) engine InnoDB; Query OK, 0 rows affected (0.09 sec) mysql 5.0 > create table m (id serial) engine MyISAM; Query OK, 0 rows affected (0.06 sec) mysql 5.0 > alter table i AUTO_INCREMENT = 16777217; Query OK, 0 rows affected (0.33 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.0 > alter table m AUTO_INCREMENT = 16777217; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.0 > SHOW TABLE STATUS\G *************************** 1. row *************************** Name: i Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 16777217 Create_time: 2008-11-29 19:20:18 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: InnoDB free: 12288 kB *************************** 2. row *************************** Name: m Engine: MyISAM Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 2533274790395903 Index_length: 1024 Data_free: 0 Auto_increment: 16777217 Create_time: 2008-11-29 19:21:09 Update_time: 2008-11-29 19:21:09 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 2 rows in set (0.09 sec) mysql 5.0 > exit Bye c:\dbs>50s c:\dbs>c:\dbs\5.0\bin\mysqladmin -uroot --port=3500 shutdown c:\dbs>50c c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.0.74-nt-debug-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql 5.0 > use test Database changed mysql 5.0 > SHOW TABLE STATUS\G *************************** 1. row *************************** Name: i Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: 1 Create_time: 2008-11-29 19:20:18 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: InnoDB free: 12288 kB *************************** 2. row *************************** Name: m Engine: MyISAM Version: 10 Row_format: Fixed Rows: 0 Avg_row_length: 0 Data_length: 0 Max_data_length: 2533274790395903 Index_length: 1024 Data_free: 0 Auto_increment: 16777217 Create_time: 2008-11-29 19:21:09 Update_time: 2008-11-29 19:21:09 Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 2 rows in set (0.03 sec) mysql 5.0 >
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: When started, mysql server is automatically resetting the AUTO_INCREMENT value for tables which are empty (does not reset the value for non-empty tables). Note: mysql is ONLY doing this for tables which are empty; tables which have records in them are not having their AUTO_INCREMENT value reset. See example below. Is their any way to disable server's resetting this value? How to repeat: CREATE TABLE zbugtest LIKE descriptions; DESCRIBE zbugtest; +------------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------+------+-----+---------+----------------+ | id | bigint(20) unsigned | NO | PRI | NULL | auto_increment | | info | text | YES | | NULL | | | created_at | datetime | YES | | NULL | | | updated_at | datetime | YES | | NULL | | +------------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec) ALTER TABLE zbugtest AUTO_INCREMENT = 16777217; SHOW TABLE STATUS\G Name: zbugtest Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 16384 Data_free: 0 Auto_increment: 16777217 <<------------------- Create_time: 2008-11-28 14:50:15 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: InnoDB free: 7168 kB Exit mysql client Restart computer (which shuts down, then restarts mysql server) Start mysql client SHOW TABLE STATUS\G Name: zbugtest Engine: InnoDB Version: 10 Row_format: Compact Rows: 0 Avg_row_length: 0 Data_length: 16384 Max_data_length: 0 Index_length: 16384 Data_free: 0 Auto_increment: 1 <<-------------------- Create_time: 2008-11-28 14:50:15 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: InnoDB free: 7168 kB Suggested fix: unacceptable work around: insert at least one row of dummy data in every new table.