Bug #79512 MERGE table using partitioned tables fails with ERROR 1168

• Submitted: 4 Dec 2015 1:22
• Modified: 4 Dec 2015 14:17
• Reporter: James Leu
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.7.9
• OS: CentOS (7.0)
• CPU Architecture: Any
• Tags: merge, partition

[4 Dec 2015 1:22] James Leu

Description:
MERGE table built to UNION tables that are partitioned fails when queried.

How to repeat:
Setup
-----

CREATE DATABASE FOO;
USE FOO;
CREATE TABLE `Archive` (
  `event_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `alarmed` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`, `alarmed`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(alarmed))
(PARTITION p_2015_12 VALUES LESS THAN (736329) ENGINE = MyISAM,
PARTITION p_other VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;

CREATE DATABASE BAR;
USE BAR;
CREATE TABLE `Archive` (
  `event_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `alarmed` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`, `alarmed`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (TO_DAYS(alarmed))
(PARTITION p_2015_12 VALUES LESS THAN (736329) ENGINE = MyISAM,
PARTITION p_other VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */;

CREATE DATABASE BAZ;
USE BAZ;

CREATE TABLE `Archive` (
  `event_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `alarmed` datetime DEFAULT NULL,
  PRIMARY KEY (`event_id`, `alarmed`)
) ENGINE=MERGE UNION(`FOO`.`History`,`BAR`.`History`);

Reproduce error
---------------

select * from Archive;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

[4 Dec 2015 13:34] MySQL Verification Team

Thank you for the bug report.

http://dev.mysql.com/doc/refman/5.7/en/merge-table-problems.html

"MERGE tables do not support partitioning. That is, you cannot partition a MERGE table, nor can any of a MERGE table's underlying MyISAM tables be partitioned. "

[4 Dec 2015 14:17] James Leu

Can this be converted into a feature request?