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:
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:5.x OS:Any
Assigned to: CPU Architecture:Any
Tags: 1469, engine, mix of handlers, partition

[3 Nov 2011 0:47] Rolf Martin-Hoster
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!
[14 Nov 2011 16:29] Valeriy Kravchuk
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+.
[29 Nov 2011 14:42] Mattias Jonsson
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).
[29 Nov 2011 16:19] Rolf Martin-Hoster
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.
[30 Nov 2011 16:27] Davi Arnaut
InnoDB supports data compression in MySQL 5.6.
[30 Nov 2011 16:28] Davi Arnaut
Correction, also in MySQL 5.5.