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