| Bug #72315 | ALTER TABLE doesn't throw error while setting auto_increment value to the column | ||
|---|---|---|---|
| Submitted: | 11 Apr 2014 6:06 | Modified: | 11 Apr 2014 14:37 |
| Reporter: | Nilnandan Joshi | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 5.5/5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | ALTER TABLE, auto_increment, MySQL 5.5.35 | ||
[11 Apr 2014 14:37]
MySQL Verification Team
Thank you for the bug report. C:\dbs>net start mysqld56 The MySQLD56 service is starting. The MySQLD56 service was started successfully. C:\dbs>56 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.18 Source distribution 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 5.6 > use test Database changed mysql 5.6 > create table test (id int, name varchar(10)); Query OK, 0 rows affected (0.62 sec) mysql 5.6 > alter table test auto_increment = 2; Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql 5.6 > alter table test auto_increment = -2; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-2' at line 1 mysql 5.6 >
[13 Apr 2014 7:57]
Justin Bennett
Also note this triggers a table rebuild. A table rebuild shouldn't be triggered for a command that has no effect.
[16 Apr 2014 14:17]
Peter Schwaller
I disagree that there should be a warning/error. The statement: "ALTER TABLE tbl AUTO_INCREMENT = 100;" sets a property for the table which is used for any auto-increment columns created now OR IN THE FUTURE. Thus, it does have an effect, although that effect might not be apparent until you create a column with the auto_increment property. Furthermore, let's consider this use-case: you want to add an auto-increment column to a table that is in use, and you want to ensure that it is always auto_increment=100. If you create the auto increment column first, then it will use whatever the current value of auto_increment is, and any rows that get created before you can "alter table" won't be correct. Thus, you MUST set auto_increment before you add an auto increment column.
[16 Apr 2014 16:59]
Valeriy Kravchuk
We can not "save" auto_increment for the future for InnoDB table:
mysql> select version();
+------------+
| version() |
+------------+
| 5.6.16-log |
+------------+
1 row in set (0.09 sec)
mysql> create table test (id int, name varchar(10));
Query OK, 0 rows affected (0.54 sec)
mysql> alter table test auto_increment = 100;
Query OK, 0 rows affected (0.22 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
First of all, the output above shows that it just was NOT saved anywhere for the future. To resolve any doubts let's try to add auto_increment property for the column:
mysql> alter table test modify column id int auto_increment primary key;
Query OK, 0 rows affected (1.74 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.02 sec)
mysql> insert into test (name) values ('a');
Query OK, 1 row affected (0.03 sec)
mysql> select * from test;
+----+------+
| id | name |
+----+------+
| 1 | a |
+----+------+
1 row in set (0.03 sec)
100 was not saved anywhere and is not used. Only with auto_increment column in place it works:
mysql> alter table test auto_increment = 100;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into test (name) values ('b');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+-----+------+
| id | name |
+-----+------+
| 1 | a |
| 100 | b |
+-----+------+
2 rows in set (0.00 sec)
So, IMHO, it's a valid bug/feature request to produce warning/error for ALTER that can not do anything useful.
[16 Apr 2014 23:13]
Justin Bennett
Just to repeat my earlier comment in case it was missed, the statement also triggers a table rebuild - which shouldn't happen if the statement doesn't achieve anything.
[24 Feb 2015 0:14]
Mike Griffin
Same in 5.7 preview:
mysql[sandbox]> alter table t1 auto_increment=42; -- Should this be considered a bug that I get no warning/error?
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql[sandbox]> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql[sandbox]> select auto_increment from information_schema.tables where table_schema='test' and table_name='t1';
+----------------+
| auto_increment |
+----------------+
| NULL |
+----------------+
1 row in set (0.00 sec)
mysql[sandbox]> alter table t1 add id int unsigned not null auto_increment primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql[sandbox]> select auto_increment from information_schema.tables where table_schema='test' and table_name='t1';
+----------------+
| auto_increment |
+----------------+
| 1 |
+----------------+
1 row in set (0.00 sec)
mysql[sandbox]> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` varchar(255) DEFAULT NULL,
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql[sandbox]>
mysql[sandbox]> select @@version, @@version_comment;
+------------------------+------------------------------+
| @@version | @@version_comment |
+------------------------+------------------------------+
| 5.7.5-labs-preview-log | MySQL Community Server (GPL) |
+------------------------+------------------------------+
1 row in set (0.00 sec)
[6 Sep 2016 6:00]
Justin Bennett
Confirmed behavior still present in 5.7.11:
[test] create table t1 (col1 varchar(10));
Query OK, 0 rows affected (0.04 sec)
[test] alter table t1 auto_increment = 100;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
[test] show create table t1;
+-------+----------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`col1` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
[test] insert into t1 values ('aaa');
Query OK, 1 row affected (0.27 sec)
[test] insert into t1 values ('bbb');
Query OK, 1 row affected (0.01 sec)
[test] alter table t1 add id int unsigned not null auto_increment primary key;
Query OK, 0 rows affected (0.24 sec)
Records: 0 Duplicates: 0 Warnings: 0
[test] show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`col1` varchar(10) DEFAULT NULL,
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[test] select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.11 |
+-----------+
1 row in set (0.03 sec)
[test]
However the 'alter table ... auto_increment = ...' no longer seems to trigger a rebuild, which is something.
Description: 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 How to repeat: mysql> create table test (id int, name varchar(10)); Query OK, 0 rows affected (0.01 sec) mysql> alter table test auto_increment = 2; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table test \G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int(11) DEFAULT NULL, `name` varchar(10) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql>