MySQL Bugs: #65901: AUTO_INCREMENT option on InnoDB table is ignored if added before autoinc column

Bug #65901	AUTO_INCREMENT option on InnoDB table is ignored if added before autoinc column
Submitted:	15 Jul 2012 1:56	Modified:	15 Jul 2012 7:48
Reporter:	Elena Stepanova	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1, 5.5, 5.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
AUTO_INCREMENT table option on an InnoDB table is only taken into account when it is added together with or after an auto-increment column. If the option is set first, and the column is either added or modified to be auto-increment afterwards, the table option is ignored, and the counter starts from 1.

It becomes a problem e.g when an auto-inc pk is needed for a table which already contains some data (in this case we cannot switch the statements because values are inserted as soon as the column is added).

Works fine with MyISAM.

How to repeat:
# Test case:
--source include/have_innodb.inc
create table t (i int) engine=innodb auto_increment=100;
alter table t modify column i int auto_increment key;
insert into t values (null);
select * from t;

# End of the 1st test case
# result:
# i
# 1

# A slightly longer test case showing why it might be a problem:

--source include/have_innodb.inc
# created a table without a PK
create table t (f char(8)) engine=innodb;
# inserted some data (possibly a lot) 
insert into t values ('foo'),('bar'); 
# realized that we need a PK, but need it to start from 100000
alter table t auto_increment = 100000;
# added the pk
alter table t add column pk int not null auto_increment key first;
select * from t;
# End of the 2nd test case

# result:
# pk  f
# 1   foo
# 2   bar

Thank you for the bug report. Verified with 5.5.26 on Mac OS X:

macbook-pro:5.5 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.5.26-debug Source distribution

Copyright (c) 2000, 2011, 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 t (i int) engine=innodb auto_increment=100;
Query OK, 0 rows affected (0.17 sec)

mysql> alter table t modify column i int auto_increment key;
Query OK, 0 rows affected (1.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t values (null);
Query OK, 1 row affected (0.06 sec)

mysql> select * from t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0.01 sec)

With MyISAM it works as expected:

mysql> drop table t;
Query OK, 0 rows affected (0.09 sec)

mysql> create table t (i int) engine=myisam auto_increment=100;
Query OK, 0 rows affected (0.09 sec)

mysql> alter table t modify column i int auto_increment primary key;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t values (null);
Query OK, 1 row affected (0.04 sec)

mysql> select * from t;
+-----+
| i   |
+-----+
| 100 |
+-----+
1 row in set (0.05 sec)

This is because of 

=========
	if (((create_info->used_fields & HA_CREATE_USED_AUTO)
	    || thd_sql_command(thd) == SQLCOM_ALTER_TABLE
	    || thd_sql_command(thd) == SQLCOM_OPTIMIZE
	    || thd_sql_command(thd) == SQLCOM_CREATE_INDEX)
	    && create_info->auto_increment_value > 0) {

		auto_inc_value = create_info->auto_increment_value;

		dict_table_autoinc_lock(innobase_table);
		dict_table_autoinc_initialize(innobase_table, auto_inc_value);
		dict_table_autoinc_unlock(innobase_table);
	}
=========

For the "alter table t modify column i int auto_increment key;" -- even though 'thd_sql_command(thd) == SQLCOM_ALTER_TABLE' is true, create_info->auto_increment_value is not, so the auto-increment value is lost. 

The fix will be either to remove the condition 'create_info->auto_increment_value > 0' or move SQLCOM_ALTER_TABLE condition outside. Latter is better since former condition may be required for other SQLCOMs.