MySQL Bugs: #37488: InnoDB auto-inc and multi-column index docs wrong

Bug #37488	InnoDB auto-inc and multi-column index docs wrong
Submitted:	18 Jun 2008 17:47	Modified:	11 Jul 2008 19:45
Reporter:	Baron Schwartz (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1, 5.0, 5.1, 6.0	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
From

http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

"An AUTO_INCREMENT column must be the first column listed if it is part of a multiple-column index in an InnoDB table."  This isn't correct.  Is it mis-worded?

How to repeat:
doc bug

What's incorrect about it? I find it to be true. Do you have a counterexample?

Sorry, I should have given an example...

mysql> create table t(a int not null auto_increment, b int not null, primary key(a), key(b, a)) engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) NOT NULL,
  PRIMARY KEY  (`a`),
  KEY `b` (`b`,`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Thank you for the report.

Verified as described:

mysql>  create table t(a int not null auto_increment, b int not null, primary key(a),
    -> key(b, a)) engine=innodb;
Query OK, 0 rows affected (0.22 sec)

mysql> show create table t;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `a` int(11) NOT NULL auto_increment,
  `b` int(11) NOT NULL,
  PRIMARY KEY  (`a`),
  KEY `b` (`b`,`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 | 
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.12 sec)

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

mysql> create table t(a int not null auto_increment, b int not null, primary key(b,a)) engine=innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql>

ha_innodb.cc in 5.1:

        if (auto_inc == 0) {
                dict_index_t* index;
                ulint error = DB_SUCCESS;
                const char* autoinc_col_name;

                ut_a(!innodb_table->autoinc_inited);

                index = innobase_get_index(table->s->next_number_index);

                autoinc_col_name = table->found_next_number_field->field_name;

                error = row_search_max_autoinc(
                        index, autoinc_col_name, &auto_inc);

It is MySQL that tells InnoDB which index is associated with the auto-inc column. I believe MySQL tries to choose an index where the column appears as the first column.

Paul, please correct the InnoDB document to say:

"An AUTO_INCREMENT column must appear as the first column in an index on the table."

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Changed to use Heikki's wording.