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: | |
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
[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