MySQL Bugs: #34366: Insert into a partitioned MyISAM table gives ER_AUTOINC_READ

Bug #34366	Insert into a partitioned MyISAM table gives ER_AUTOINC_READ_FAILED error
Submitted:	7 Feb 2008 1:02	Modified:	11 Feb 2008 22:25
Reporter:	Taha Yayci
Status:	Duplicate
Category:	Server: Partition	Severity:	S3 (Non-critical)
Version:	5.1.22	OS:	Any
Assigned to:	Mattias Jonsson	Target Version:
Tags:	partition, Auto-increment
Triage:	D2 (Serious)

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...

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>

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...

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...

This is a duplicate of Bug#33479 (there is a simple test case there).