MySQL Bugs: #69335: Auto increment problem

Bug #69335	Auto increment problem
Submitted:	28 May 2013 10:16	Modified:	28 May 2013 12:31
Reporter:	Iliya Ivanov	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.6.10	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	autoincrement auto_increment

Description:
I tried to alter the auto increment value on a table to lower(and existing) number and the value I used has been accepted. After this I can not insert any other records in the table.

How to repeat:
CREATE TABLE tbl(
id INT NOT NULL AUTO_INCREMENT ,
val INT NOT NULL ,
PRIMARY KEY ( id )
) ENGINE = INNODB;

INSERT INTO tbl VALUES(NULL, 123), (NULL, 234), (101, 5), (102, 8);

#At this point AUTO_INCREMENT is 103

ALTER TABLE tbl AUTO_INCREMENT 101;

#At this point AUTO_INCREMENT is 101 but it must be 103 again.

INSERT INTO tbl VALUES (NULL, 7); #1062 - Duplicate entry '101' for key 'PRIMARY'

#The same have been tested in ubuntu 13.04 with MySQL 5.5.31 and works correctly.

Thank you for the bug report. I couldn't repeat with current release version 5.611:

Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.11 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> CREATE DATABASE d1;
Query OK, 1 row affected (0.02 sec)

mysql> USE d1
Database changed
mysql> CREATE TABLE tbl(
    -> id INT NOT NULL AUTO_INCREMENT ,
    -> val INT NOT NULL ,
    -> PRIMARY KEY ( id )
    -> ) ENGINE = INNODB;
Query OK, 0 rows affected (0.39 sec)

mysql> INSERT INTO tbl VALUES(NULL, 123), (NULL, 234), (101, 5), (102, 8);
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE tbl AUTO_INCREMENT 101;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: tbl
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 4
 Avg_row_length: 4096
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 103
    Create_time: 2013-05-28 09:27:07
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql> INSERT INTO tbl VALUES (NULL, 7); #1062 - Duplicate entry '101' for key 'PRIMARY'
Query OK, 1 row affected (0.06 sec)

mysql> SHOW TABLE STATUS\G
*************************** 1. row ***************************
           Name: tbl
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 5
 Avg_row_length: 3276
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: 104
    Create_time: 2013-05-28 09:27:07
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

mysql>