Bug #75400 Add support in partitions of mix InnoDB and Memory engines

• Submitted: 3 Jan 2015 15:06
• Reporter: Дмитрий Логов
• Status: Open
• Category: MySQL Server: Partitions
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any

[3 Jan 2015 15:06] Дмитрий Логов

Description:
Mixing InnoDB and Memory engines partitions in table allows to realize a controlled caching in memory INSERT and UPDATE querys and stretch the life of SSD storage. For example, I create a table of two partitions:
CREATE TABLE `NewTable` (
`id`  mediumint UNSIGNED NOT NULL AUTO_INCREMENT ,
`data`  blob NOT NULL ,
`inmemory`  smallint UNSIGNED NOT NULL ,
PRIMARY KEY (`id`, `inmemory`)
)
PARTITION BY LIST(inmemory) PARTITIONS 2 (PARTITION `hdd` VALUES IN (0) ENGINE=InnoDB , PARTITION `memory` VALUES IN (1) ENGINE=MEMORY );
Now I can make INSERT and UPDATE in to memory (`inmemory` = 1) partition and (for example) UPDATE `inmemrory` field to 0 each hour and all data will move to INNODB partition (on hdd). This will reduce the amount of data written on SSD in the necessary number of times. Modern SSDs have technologies that increase the efficiency of writing and reduce wear on its storage cells. But still they are usually has raw wear-out in the 500 to 3000 write cycle range and fail very often. And this problem is not solved in MySQL.
You can also make inmemory flag for Innodb, and then there will be no need to mix partition of different engines in one table.

How to repeat:
CREATE TABLE `NewTable` (
`id`  mediumint UNSIGNED NOT NULL AUTO_INCREMENT ,
`data`  blob NOT NULL ,
`inmemory`  smallint UNSIGNED NOT NULL ,
PRIMARY KEY (`id`, `inmemory`)
)
PARTITION BY LIST(inmemory) PARTITIONS 2 (PARTITION `hdd` VALUES IN (0) ENGINE=InnoDB , PARTITION `memory` VALUES IN (1) ENGINE=MEMORY );
Return: ERROR 1497: The mix of handlers in the partitions is not allowed in this version of MySQL

Suggested fix:
Add support in partitions of mix InnoDB and Memory engines