Bug #69984 Cannot set back AI Value to 1
Submitted: 11 Aug 2013 16:54 Modified: 12 Aug 2013 14:23
Reporter: Christian Benner Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.6.12, 5.6.13, 5.7.2-m12 OS:Windows
Assigned to: CPU Architecture:Any
Tags: regression

[11 Aug 2013 16:54] Christian Benner
Description:
Alter Table -> Options 
Setting Autoincrement Value back to 1 works fine (without error) but gets set back to it's value.

How to repeat:
InnoDB Engine, insert some rows to have a AI Value > than 1 
Delete all Rows and try to set back the AI Value to 1...
[11 Aug 2013 17:03] Christian Benner
mysql> ALTER TABLE `database`.`table` AUTO_INCREMENT = 1 ;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

CLI Command Returns OK but the AI isn't really set back to "1" ;-(
[12 Aug 2013 8:09] Peter Laursen
This is not a Workbench problem. it is reproducible with any client on MySQL 5.6.13. On MySQL 5.5.33 it works as expected. An example wiht 5.6.13

SELECT COUNT(*) FROM bugxx; -- returns 3
DELETE FROM bugxx; -- success
ALTER TABLE bugxx AUTO_INCREMENT = 1; -- success
SHOW CREATE TABLE bugxx
/* returns

CREATE TABLE `bugxx` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(10) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4
 DEFAULT CHARSET=utf8
*/
[12 Aug 2013 11:40] MySQL Verification Team
Hello Christian,

Thank you for the bug report. 
Verified as described.

Thanks,
Umesh
[12 Aug 2013 12:31] Peter Laursen
There is a workaround.  Execute a TRUNCATE TABLE: 

SELECT COUNT(*) FROM bugxx; -- returns 3
TRUNCATE TABLE bugxx;
ALTER TABLE bugxx AUTO_INCREMENT = 1;
SHOW CREATE TABLE bugxx;
/* returns

CREATE TABLE `bugxx` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(10) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/
[12 Aug 2013 12:33] Peter Laursen
Acutally it seems there is need for an ALTER TABLE if TRUNCATE TABLE is executed.

SELECT COUNT(*) FROM bugxx; -- returns 3
TRUNCATE TABLE bugxx;
SHOW CREATE TABLE bugxx;
/* returns

CREATE TABLE `bugxx` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account` varchar(10) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*/
[12 Aug 2013 14:23] MySQL Verification Team
Marking as duplicate of Bug #69882