| 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 >
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.