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:
None 
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 9:41] James Ostrowick
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
[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.