ALTER TABLE doesn't throw error while setting auto_increment value to the column

[Nilnandan Joshi]

If there is no auto_increment column in table and we run "ALTER TABLE <tablename> auto_increment=<next value>" then it doesn't give any error/warning about that.

mysql> show create table inc_test \G
*************************** 1. row ***************************
       Table: inc_test
Create Table: CREATE TABLE `inc_test` (
  `id` bigint(11) NOT NULL DEFAULT '0',
  `animal` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table inc_test auto_increment=7;
Query OK, 6 rows affected (0.04 sec)
Records: 6 Duplicates: 0 Warnings: 0

[Valerii Kravchuk (valerii-kravchuk)]

This is easy to confirm:

[openxs@chief p5.6]$ bin/mysql --no-defaults -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.16-64.2 MySQL Community Server (GPL)

Copyright (c) 2009-2014 Percona LLC and/or its affiliates
Copyright (c) 2000, 2014, 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 `inc_test` (
    -> `id` bigint(11) NOT NULL DEFAULT '0',
    -> `animal` varchar(10) DEFAULT NULL,
    -> PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.43 sec)

mysql> alter table inc_test auto_increment=7;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table inc_test\G
*************************** 1. row ***************************
       Table: inc_test
Create Table: CREATE TABLE `inc_test` (
  `id` bigint(11) NOT NULL DEFAULT '0',
  `animal` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Manual does not explain that option may be silently ignored when does not apply:

" To change the value of the AUTO_INCREMENT counter to be used for new rows, do this:

ALTER TABLE t2 AUTO_INCREMENT = value;

You cannot reset the counter to a value less than or equal to to the value that is currently in use. For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one. "

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1493