MySQL Bugs: #69882: Cannot decrease auto_increment value even when table is empty

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:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, regression

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

Looks like the form cut off the text when I submitted this.  Here's the rest of what

How to repeat (continued):

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

Truncating the Inno

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?

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 >

Bug #69984 is marked as duplicate of this

Workaround: TRUNCATE TABLE.

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)

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.

also on 5.6.15

didn't see above comment. Will upgrade.

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.