MySQL Bugs: #22564: auto_increment column gets automatically incremented

Bug #22564	auto_increment column gets automatically incremented
Submitted:	21 Sep 2006 20:04	Modified:	3 Dec 2007 14:11
Reporter:	Peter Gulutzan	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	5.1.12-beta-debug	OS:	Linux (SUSE 10.0 / 64-bit)
Assigned to:	Christopher Powers	CPU Architecture:	Any

Description:
A column which is defined as auto_increment, and which is not indexed as
the first column in a multi-column index, should not increase in value
unless the first column changes. It's a complex MyISAM rule, described here:
http://dev.mysql.com/doc/refman/5.1/en/example-auto-increment.html
(Start with the words "For MyISAM tables you can specify AUTO_INCREMENT
on a secondary column in a multiple-column index".)

Falcon should either follow the rules described for MyISAM, or reject
the CREATE TABLE statement as InnoDB does.

How to repeat:
mysql> create table tk (s1 int, s2 int auto_increment, key (s1,s2)) engine=myisam;
Query OK, 0 rows affected (0.00 sec)

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

mysql> insert into tk (s1) values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tk;
+------+----+
| s1   | s2 |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    2 |  1 |
|    3 |  1 |
+------+----+
4 rows in set (0.00 sec)

mysql> drop table tk;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tk (s1 int, s2 int auto_increment, key (s1,s2)) engine=falcon;
Query OK, 0 rows affected (0.01 sec)

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

mysql> insert into tk (s1) values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tk;
+------+----+
| s1   | s2 |
+------+----+
|    1 |  1 |
|    1 |  2 |
|    2 |  3 |
|    3 |  4 |
+------+----+
4 rows in set (0.00 sec)

Verified on Linux 32-bit, change set 1.2364, 2006-10-03.
Added test case falcon_bug_22564.test.

Regards, Hakan

Chris,  We need to reject this create statement like innodb does, since we are not going to implement the MYISAM hack of using multiple autoincrement counters.  Talk to Ann for more info.  (low priority, but its a low hanging fruit!)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36923

ChangeSet@1.2657, 2007-11-01 15:43:39-05:00, chris@xeno.mysql.com +4 -0
  Bug#22564, "auto_increment column gets automatically incremented"
  
  Removed HA_AUTO_PART_KEY from StorageInterface::table_flags().
  This will allow autoincrement columns to be defined in multipart
  indexes, but only as the first column of the index.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37021

ChangeSet@1.2682, 2007-11-02 16:12:40-05:00, chris@xeno.mysql.com +2 -0
  Bug #22564, "auto_increment column gets automatically incremented"
  - Adjusted testcase falcon_bug_28052.test to conform to the restriction that 
    autoincrement columns must be the first part of a multi-part key.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/37031

ChangeSet@1.2683, 2007-11-02 17:34:23-05:00, chris@xeno.mysql.com +3 -0
  Bug #22564, "auto_increment column gets automatically incremented"
  - Changed testcases using multipart keys with autoincrement columns to put the autoincrement column first.

falcon_bug_22564 passes now.

Pushed into 6.0.4-alpha

A note has been added to the 6.0.4 changelog: 

Creating a Falcon table with an auto-increment column that is not indexed as the first column in a multi-column index would auto0increment. This behavior was different to the behavior in both MyISAM and InnoDB. Falcon now rejects such tables during creation in the same way InnoDB does