MySQL Bugs: #63083: ERROR 1469 (HY000): The mix of handlers in the partitions is not permitted in th

Bug #63083	ERROR 1469 (HY000): The mix of handlers in the partitions is not permitted in th
Submitted:	3 Nov 2011 0:47	Modified:	29 Nov 2011 16:19
Reporter:	Rolf Martin-Hoster	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.x	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	1469, engine, mix of handlers, partition

Description:
Is there a timeline on when PARTITIONS will support multiple handlers?

The partitioning handler accepts a [STORAGE] ENGINE option for both PARTITION and SUBPARTITION. Currently, the only way in which this can be used is to set all partitions or all subpartitions to the same storage engine, and an attempt to set different storage engines for partitions or subpartitions in the same table will give rise to the error ERROR 1469 (HY000): The mix of handlers in the partitions is not permitted in this version of MySQL. We expect to lift this restriction on partitioning in a future MySQL release.

How to repeat:
CREATE TABLE with mixed partition handlers

Suggested fix:
Please please please fix!

I was not able to find definite public plans on this. MySQL 5.6 manual clearly says (http://dev.mysql.com/doc/refman/5.6/en/partitioning-overview.html):

"In MySQL 5.6, all partitions of the same partitioned table must use the same storage engine; for example, you cannot use MyISAM for one partition and InnoDB for another."

So I'd say this is a reasonable feature request for 5.6+.

Different engines have different behaviors when it comes to position and how indexes are handled. For example MyISAM is non pk clustered, and position the rows by offset in the data file, whereas InnoDB is pk clustered and position the rows by the PK (HA_PRIMARY_KEY_REQUIRED_FOR_POSITION).

Also mixing transactional an non transactional partition engines would not be a good idea.

So the only possible benefit I currently see is to mix MyISAM and Archive, which would result in a partitioned table which would not support indexes (since archive does not support them).

I can see where mixing transactional and non transactional databases would be bad, however my needs are row level locking not transactional. It is prohibitively expensive for me to do a straight conversion to innodb for all the partitions, this increased the size of the tables 3 fold and in my case storage is very expensive.

InnoDB supports data compression in MySQL 5.6.

Correction, also in MySQL 5.5.