Bug #69882 Cannot decrease auto_increment value even when table is empty
Submitted: 31 Jul 2013 17:59 Modified: 6 Nov 2013 13:36
Reporter: Keith Dechant Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.12 OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: auto_increment, regression
Triage: Needs Triage: D2 (Serious)

[31 Jul 2013 17:59] Keith Dechant
Description:
With an empty InnoDB table, it's not possible to decrease the auto_increment value using an ALTER TABLE statement, even when the table is empty.

Possibly related to bug #68336: http://bugs.mysql.com/bug.php?id=68336

How to repeat:
Start with an InnoDB table `my_table` containing 3 rows (so the next auto_increment would be 4).

DELETE FROM `my_table`;
ALTER TABLE `my_table` AUTO_INCREMENT=1;

MySQL does not give any errors, but the "ALTER TABLE" query has no effect.  The next row added to the table will have an auto-increment of 4, not 1 as expected.

Setting the auto_increment to a higher value, say 10, works. (But you can't change it to anything lower than 10 afterward.)

This affects InnoDB tables only. On My
[31 Jul 2013 18:05] Keith Dechant
Looks like the form cut off the text when I submitted this.  Here's the rest of what
[31 Jul 2013 18:08] Keith Dechant
How to repeat (continued):

This affects InnoDB tables only. With MyISAM tables, the same queries reset the auto_increment to 1.

Truncating the Inno
[31 Jul 2013 18:26] Valeriy Kravchuk
New 5.6.13 is also affected. This is how it works in 5.5.32:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3312 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.5.32 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 table ti(id int auto_increment primary key, c1 int) engine=InnoDB;

Query OK, 0 rows affected (0.49 sec)

mysql> insert into ti(c1) values (1), (2), (3);
Query OK, 3 rows affected (0.15 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table ti\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.05 sec)

mysql> delete from ti;
Query OK, 3 rows affected (0.09 sec)

mysql> show create table ti\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table ti auto_increment=1;
Query OK, 0 rows affected (0.76 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table ti\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.04 sec)

mysql> insert into ti(c1) values (1);
Query OK, 1 row affected (0.12 sec)

mysql> select * from ti;
+----+------+
| id | c1   |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.03 sec)

mysql> exit
Bye

Now great new 5.6.13:

C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3314 test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.13 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 table ti(id int auto_increment primary key, c1 int) engine=InnoDB;

Query OK, 0 rows affected (0.59 sec)

mysql> create table ti(id int auto_increment primary key, c1 int) engine=InnoDB;

Query OK, 0 rows affected (0.45 sec)

mysql> insert into ti(c1) values (1), (2), (3);
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> show create table ti\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> delete from ti;
Query OK, 3 rows affected (0.10 sec)

mysql> show create table ti\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table ti auto_increment=1;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table ti\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> insert into ti(c1) values (1);
Query OK, 1 row affected (0.08 sec)

mysql> select * from ti;
+----+------+
| id | c1   |
+----+------+
|  4 |    1 |
+----+------+
1 row in set (0.00 sec) 

Looks like a regression bug?
[31 Jul 2013 20:55] Miguel Solorzano
Thank you for the bug report.

C:\dbs>c:\dbs\5.5\bin\mysql -uroot --port=3550 --prompt="mysql 5.5 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.34 Source distribution

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 5.5 > USE test
Database changed
mysql 5.5 > CREATE TABLE my_table (id int auto_increment not null primary key, col1 int);
Query OK, 0 rows affected (0.09 sec)

mysql 5.5 > INSERT INTO my_table (col1) values (1),(2),(3);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.5 > SHOW CREATE TABLE my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 5.5 > DELETE FROM `my_table`;
Query OK, 3 rows affected (0.03 sec)

mysql 5.5 > ALTER TABLE `my_table` AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.5 > SHOW CREATE TABLE my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 5.5 > INSERT INTO my_table (col1) values (1);
Query OK, 1 row affected (0.03 sec)

mysql 5.5 > SELECT * FROM my_table;
+----+------+
| id | col1 |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

***************************************************************************************************
C:\dbs>c:\dbs\5.6\bin\mysql -uroot --port=3560 --prompt="mysql 5.6 > "
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.14 Source distribution

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 5.6 > USE test
Database changed
mysql 5.6 > CREATE TABLE my_table (id int auto_increment not null primary key, col1 int);
Query OK, 0 rows affected (0.33 sec)

mysql 5.6 > INSERT INTO my_table (col1) values (1),(2),(3);
Query OK, 3 rows affected (0.05 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql 5.6 > SHOW CREATE TABLE my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 5.6 > DELETE FROM `my_table`;
Query OK, 3 rows affected (0.03 sec)

mysql 5.6 > ALTER TABLE `my_table` AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.6 > SHOW CREATE TABLE my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 5.6 > INSERT INTO my_table (col1) values (1);
Query OK, 1 row affected (0.03 sec)

mysql 5.6 > SELECT * FROM my_table;
+----+------+
| id | col1 |
+----+------+
|  4 |    1 |
+----+------+
1 row in set (0.00 sec)

mysql 5.6 >
[12 Aug 2013 14:25] Umesh Shastry
Bug #69984 is marked as duplicate of this
[5 Sep 2013 19:15] Sveta Smirnova
Workaround: TRUNCATE TABLE.
[17 Sep 2013 9:02] tsubasa tanaka
Another Workaound: ALTER TABLE my_table ALGORITHM= COPY, AUTO_INCREMENT= 1;

mysql56> SHOW CREATE TABLE my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql56> SELECT * FROM my_table;
Empty set (0.00 sec)

mysql56> ALTER TABLE my_table ALGORITHM= COPY, AUTO_INCREMENT= 1;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql56> SHOW CREATE TABLE my_table\G
*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `col1` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql56> INSERT INTO my_table (col1) VALUES (1);
Query OK, 1 row affected (0.01 sec)

mysql56> SELECT * FROM my_table;
+----+------+
| id | col1 |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)
[6 Nov 2013 13:35] Daniel Price
Fixed as of 5.6.16 and 5.7.4, and here's the changelog entry:

The "ALTER TABLE" "INPLACE" algorithm would fail to decrease the
auto-increment value. 

Thank you for the bug report.
[23 Dec 2013 21:04] Kyle McNally
also on 5.6.15
[23 Dec 2013 21:13] Kyle McNally
didn't see above comment. Will upgrade.
[3 Feb 2014 10:17] Laurynas Biveinis
5.6$ bzr log -r 5593
------------------------------------------------------------
revno: 5593
committer: Thirunarayanan B<thirunarayanan.balathandayuth@oracle.com>
branch nick: mysql-5.6
timestamp: Wed 2013-11-06 14:01:09 +0530
message:
  Bug #17250787 CANNOT DECREASE AUTO_INCREMENT VALUE EVEN WHEN TABLE IS EMPTY
  Problem:
    There is a difference in behaviour between copy algorithm and inplace
    algorithm in alter statement for decreasing the auto increment value.
    In alter table statement, inplace algorithm is not able to decrease 
    the auto increment value.
  
  Solution:
    Inplace algorithm will first check the maximum of existing auto_increment
    value from table. If this value is greater than or equal to user-defined
    value then maximum of existing auto_increment value + 1 will be set as
    autoincrement value, else user-defined value will be set as autoincrement
    value.