Bug #34366 Insert into a partitioned MyISAM table gives ER_AUTOINC_READ_FAILED error
Submitted: 7 Feb 2008 0:02 Modified: 11 Feb 2008 21:25
Reporter: Taha Yayci Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.22 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: Auto-increment, partition
Triage: D2 (Serious)

[7 Feb 2008 0:02] Taha Yayci
Description:
I have a MYISAM table with an auto=increment column and partitioning on another column. while inserting into table the first two statements work correctly but the third one gives the error :
Error Code : 1467
Failed to read auto-increment value from storage engine

I found that the problem arises when primary key columns order is first partitioning column and then auto-increment column. When i change the order by first auto-increment column then the partitioning column  it works..

In previous versions from 5.1.18 to 5.1.21, it was working but now it isn't..

The order of the primary key is very important because of my selects but a temporary solution can be :
create primary key as (auto-increment, partitioning column) then create an index as (partitioning column, auto-increment)... But it is still a temporary solution..

How to repeat:
This works :
===================================
drop table `test01`;
create table `test01` (
`rowid` bigint UNSIGNED NOT NULL AUTO_INCREMENT, 
`userid` smallint UNSIGNED NOT NULL, 
`username` varchar (20) NOT NULL , 
PRIMARY KEY (`rowid`, `userid`))
PARTITION BY HASH (userid) PARTITIONS 2;

insert into `test01`(`rowid`,`userid`,`username`) values ( NULL,'1','name');
insert into `test01`(`rowid`,`userid`,`username`) values ( NULL,'1','name');
insert into `test01`(`rowid`,`userid`,`username`) values ( NULL,'1','name');

This doesnt work :
===================================
drop table `test01`;
create table `test01` (
`rowid` bigint UNSIGNED NOT NULL AUTO_INCREMENT, 
`userid` smallint UNSIGNED NOT NULL, 
`username` varchar (20) NOT NULL , 
PRIMARY KEY (`userid`, `rowid`))
PARTITION BY HASH (userid) PARTITIONS 2;

insert into `test01`(`rowid`,`userid`,`username`) values ( NULL,'1','name');
insert into `test01`(`rowid`,`userid`,`username`) values ( NULL,'1','name');
insert into `test01`(`rowid`,`userid`,`username`) values ( NULL,'1','name');

Suggested fix:
When the primary key is (userid, rowid) then rowid is calculated based on the userid.. You can see that rowid doesnt get unique value, its value is unique with respect to the userid...
1	1	name
2	1	name
1	2	22

As in the condition that the primary key is (rowid, userid) auto-increment column rowid can get unique values, so the problem can be resolved...
[7 Feb 2008 0:11] Miguel Solorzano
Thank you for the bug report. Verified as described:

mysql> create table `test01` (
    -> `rowid` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
    -> `userid` smallint UNSIGNED NOT NULL,
    -> `username` varchar (20) NOT NULL ,
    -> PRIMARY KEY (`userid`, `rowid`))
    -> PARTITION BY HASH (userid) PARTITIONS 2;
Query OK, 0 rows affected (0.08 sec)

mysql>
mysql> insert into `test01`(`rowid`,`userid`,`username`) values ( NULL,'1','name');
Query OK, 1 row affected (0.01 sec)

mysql> insert into `test01`(`rowid`,`userid`,`username`) values ( NULL,'1','name');
Query OK, 1 row affected (0.00 sec)

mysql> insert into `test01`(`rowid`,`userid`,`username`) values ( NULL,'1','name');
ERROR 1467 (HY000): Failed to read auto-increment value from storage engine
mysql>
[7 Feb 2008 2:54] Taha Yayci
thanks for your quick reply..

i found that primary key (rowid, userid) and (userid, rowid) have same effects in my situation. All the selects use indexes and partitions correctly. But for other situations it can be a big problem such as another column in the index. so for my situation it is not severe now, but i am still waiting for the patch. thanks again...
[7 Feb 2008 2:55] Taha Yayci
i found that primary key (rowid, userid) and (userid, rowid) have same effects in my situation. All the selects use indexes and partitions correctly. But for other situations it can be a big problem such as another column in the index. so for my situation it is not severe now, but i am still waiting for the patch. thanks again...
[11 Feb 2008 21:25] Mattias Jonsson
This is a duplicate of Bug#33479 (there is a simple test case there).