Bug #68336 bug with ALTER TABLE AUTO_INCREMENT
| Submitted: | 11 Feb 2013 9:41 | Modified: | 3 Apr 2013 15:35 |
|---|---|---|---|
| Reporter: | James Ostrowick | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Command-line Clients | Severity: | S2 (Serious) |
| Version: | 5.6.10 | OS: | MacOS |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | alter, auto_increment, regression, table | ||
[11 Feb 2013 12:18]
MySQL Verification Team
Hello James, Thank you for the report. Verified as described on reported version.
[11 Feb 2013 12:18]
MySQL Verification Team
## 5.1.68 - is not affected
mysql> create table users (
-> user_id int(11) NOT NULL AUTO_INCREMENT,
-> user_name varchar(250) NOT NULL DEFAULT 'None',
-> PRIMARY KEY (user_id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql>
mysql> insert into users(user_name) values('umesh'),('umesh1'),('umesh2');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from users;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 1 | umesh |
| 2 | umesh1 |
| 3 | umesh2 |
+---------+-----------+
3 rows in set (0.00 sec)
mysql> show table status from test where name like 'users'\G
*************************** 1. row ***************************
Name: users
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 3
Avg_row_length: 20
Data_length: 60
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 0
Auto_increment: 4
Create_time: 2013-02-11 17:10:13
Update_time: 2013-02-11 17:11:23
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> delete from users where user_id > 1;
Query OK, 2 rows affected (0.00 sec)
mysql> show table status from test where name like 'users'\G
*************************** 1. row ***************************
Name: users
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 20
Data_length: 60
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 40
Auto_increment: 4
Create_time: 2013-02-11 17:10:13
Update_time: 2013-02-11 17:11:23
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql>
mysql> ALTER TABLE users AUTO_INCREMENT = 1;
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show table status from test where name like 'users'\G
*************************** 1. row ***************************
Name: users
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1
Avg_row_length: 20
Data_length: 20
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 0
Auto_increment: 2
Create_time: 2013-02-11 17:14:34
Update_time: 2013-02-11 17:14:34
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> insert into users(user_name) values('umesh');
Query OK, 1 row affected (0.00 sec)
mysql> show table status from test where name like 'users'\G
*************************** 1. row ***************************
Name: users
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 20
Data_length: 40
Max_data_length: 281474976710655
Index_length: 2048
Data_free: 0
Auto_increment: 3
Create_time: 2013-02-11 17:43:43
Update_time: 2013-02-11 17:44:00
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
[11 Feb 2013 12:19]
MySQL Verification Team
## 5.5.30 - is not affected
D:\ushastry\mysql-5.1.68-win32\bin>cd ../../mysql-5.5.30-winx64/bin
D:\ushastry\mysql-5.5.30-winx64\bin>mysql -u root -p --port=1111
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql>
mysql> create table users (
-> user_id int(11) NOT NULL AUTO_INCREMENT,
-> user_name varchar(250) NOT NULL DEFAULT 'None',
-> PRIMARY KEY (user_id)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> insert into users(user_name) values('umesh'),('umesh1'),('umesh2');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from users;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 1 | umesh |
| 2 | umesh1 |
| 3 | umesh2 |
+---------+-----------+
3 rows in set (0.00 sec)
mysql> delete from users where user_id > 1;
Query OK, 2 rows affected (0.00 sec)
mysql> show table status from test where name like 'users'\G
*************************** 1. row ***************************
Name: users
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: 4
Create_time: 2013-02-11 17:18:48
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
mysql> ALTER TABLE users AUTO_INCREMENT = 1;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show table status from test where name like 'users'\G
*************************** 1. row ***************************
Name: users
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 10485760
Auto_increment: 2
Create_time: 2013-02-11 17:18:48
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql>
mysql> insert into users(user_name) values('umesh');
Query OK, 1 row affected (0.00 sec)
mysql> show table status from test where name like 'users'\G
*************************** 1. row ***************************
Name: users
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 9437184
Auto_increment: 3
Create_time: 2013-02-11 17:18:48
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql>
[11 Feb 2013 12:19]
MySQL Verification Team
# 5.6.10 - Affected
D:\ushastry\mysql-5.6.10-winx64\bin>mysql -u root -p --port=3333
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Database changed
mysql>
mysql> create table users (
-> user_id int(11) NOT NULL AUTO_INCREMENT,
-> user_name varchar(250) NOT NULL DEFAULT 'None',
-> PRIMARY KEY (user_id)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into users(user_name) values('umesh'),('umesh1'),('umesh2');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql>
mysql> select * from users;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 1 | umesh |
| 2 | umesh1 |
| 3 | umesh2 |
+---------+-----------+
3 rows in set (0.00 sec)
mysql>
mysql> delete from users where user_id > 1;
Query OK, 2 rows affected (0.00 sec)
mysql> show table status from test where name like 'users'\G
*************************** 1. row ***************************
Name: users
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 4
Create_time: 2013-02-11 17:22:39
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql> ALTER TABLE users AUTO_INCREMENT = 1;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show table status from test where name like 'users'\G
*************************** 1. row ***************************
Name: users
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 1
Create_time: 2013-02-11 17:24:04
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
## Next insert will cause the duplicate entry error but this will set Auto_increment to 2. Subsequent entries will not have any issues there after.
mysql> insert into users(user_name) values('umesh');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> select * from users;
+---------+-----------+
| user_id | user_name |
+---------+-----------+
| 1 | umesh |
+---------+-----------+
1 row in set (0.00 sec)
mysql> show table status from test where name like 'users'\G
*************************** 1. row ***************************
Name: users
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 1
Avg_row_length: 16384
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 2
Create_time: 2013-02-11 17:33:39
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
[20 Feb 2013 8:40]
James Ostrowick
Hi Umesh, Thanks for your contribution, but as can be seen from my bug report this only affects the 5.6.10 release. I did state that this worked on earlier releases.
[3 Apr 2013 15:35]
Bugs System
Added changelog entry for 5.6.11, 5.7.1: "When using "ALTER TABLE" to set an "AUTO_INCREMENT" column value to a user-specified value, InnoDB would set the "AUTO_INCREMENT" value to the user-specified value even when the "AUTO_INCREMENT" value is greater than the user-specified value. This fix ensures that the "AUTO_INCREMENT" value is set to the maximum of the user-specified value and MAX(auto_increment_column)+1, which is the expected behaviour."
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.044 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 removing rows from a table and attempting to change the auto_increment value back to the maximum+1 , the following command resets the auto_increment value back to 1 example: alter table users auto_increment = 1; this should set the auto_increment value to max(id) + 1 with 5.5 and earlier, this was the case, it now sets it to exactly 1. How to repeat: if we have a user table with primary key on user_id and set to auto_increment for example : create table users ( user_id int(11) NOT NULL AUTO_INCREMENT, user_name varchar(250) NOT NULL DEFAULT 'None', PRIMARY KEY (user_id) ); insert some data... delete from users where user_id > 1; ALTER TABLE users AUTO_INCREMENT = 1; this should now set the AUTO_INCREMENT value to 2 (as we have deleted everything beyond 1) check this by doing: mysql> show table status from databasename where name like 'users'; *************************** 1. row *************************** Name: users Engine: InnoDB Version: 10 Row_format: Compact Rows: 8 Avg_row_length: 2048 Data_length: 16384 Max_data_length: 0 Index_length: 0 Data_free: 12582912 Auto_increment: 1 Create_time: 2013-02-11 11:26:47 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: Suggested fix: the ALTER TABLE statement must be max(primary key) + 1