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

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