MySQL Bugs: #33661: Falcon insert into partition fails if starting auto

Bug #33661	Falcon insert into partition fails if starting auto_increment is set
Submitted:	3 Jan 2008 17:00	Modified:	16 Mar 2008 11:21
Reporter:	Philip Stoev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.4-p2	OS:	Any
Assigned to:	Kevin Lewis	CPU Architecture:	Any

Description:
The test case for bug #19281 fails on Falcon, albeit with a different error message.

Steps are as follows:
1. CREATE TABLE with auto_increment option and partitions
2. CREATE INDEX on table
3. INSERT NULL into the auto_increment column

Please note that even if the user does not use the auto_increment option, this option may be added by backup tools and such.

How to repeat:
mysql> create table t1 (a int auto_increment primary key)
    -> auto_increment=100 engine=falcon
    -> partition by list (a) (partition p0 values in (1, 100));
Query OK, 0 rows affected (0.00 sec)

mysql> create index inx on t1 (a);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into t1 values (null);
ERROR 1526 (HY000): Table has no partition for value 99

This is not a problem with the partition engine.  The partition phrase just helped to identify the bug.

The problem is actually that each successive index created on an autoincrement field lowers the starting value of the associated sequence for that index. This is only a problem when the extra indexes on the autoincrement field are created before any inserts.  And the new starting value does not go below zero.

So the testcase can be reduced to this;

mysql> create table t1 (a int auto_increment primary key) auto_increment=100 engine=falcon;
Query OK, 0 rows affected (2.80 sec)

mysql> create index inx1 on t1 (a);
Query OK, 0 rows affected (9.36 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index inx2 on t1 (a);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index inx3 on t1 (a);
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> select * from t1;
+----+
| a  |
+----+
| 97 |
+----+
1 row in set (0.00 sec)

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/40542

ChangeSet@1.2771, 2008-01-03 16:32:13-06:00, klewis@klewis-mysql. +1 -0
  Bug#33661 - Return the next autoincrement number to use to the server.

There is a field called auto_increment_value in struct st_ha_create_information, handler.h, that is used as a starting point for new indexes of autoincrement fields.  But this value should be the next value to use.  Falcon internally stores the last sequence used.  This must be incremented by one when it is put into auto_increment_value.

Patch is in mysql-6.0-release version 6.0.4

Pushed into 6.0.4-alpha

A note has been added to the 6.0.4 changelog: 

An INSERT into a Falcon table with partitions and an auto-increment column would fail.