Bug #94875 MySQL is performing Engine substitution with partitions

Submitted: 2 Apr 2019 19:21 Modified: 3 Apr 2019 5:51
Reporter: Marcelo Altmann (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:8.0.15 OS:Any
Assigned to: CPU Architecture:Any

[2 Apr 2019 19:21] Marcelo Altmann
Description:
MySQL is incorrectly performing engine substitution if you omit table engine and set all partitions to the same engine.

default_storage_engine should be set when you omit table engine.

How to repeat:
mysql> SHOW VARIABLES LIKE 'default_storage_engine';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | MyISAM |
+------------------------+--------+
1 row in set (0.19 sec)

mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                 |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+---------------+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

#test 1 - omit table engine but set a different engine than default_storage_engine on both partitions

mysql> CREATE TABLE t1 ( id BIGINT UNSIGNED NOT NULL, a VARCHAR(250) )  PARTITION BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (100) ENGINE=InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE=InnoDB );
Query OK, 0 rows affected (0.19 sec)

mysql> SHOW CREATE TABLE t1;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                     |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL,
  `a` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50100 PARTITION BY RANGE (`id`)
(PARTITION p1 VALUES LESS THAN (100) ENGINE = InnoDB,
 PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

Expected result: Failure as I'm mixing MyISAM

#test2 - omit table engine set one partition as different engine than default_storage_engine and other partition with default_storage_engine
mysql> CREATE TABLE t2 ( id BIGINT UNSIGNED NOT NULL, a VARCHAR(250) )  PARTITION BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (100) ENGINE=InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE );
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL

Expected result: Result is correct, as I'm mixing storage engines

#test3 - set table engine same as default_storage_engine and set both partitions with different engine than default
mysql> CREATE TABLE t2 ( id BIGINT UNSIGNED NOT NULL, a VARCHAR(250) ) ENGINE=MyISAM PARTITION BY RANGE (id) ( PARTITION p1 VALUES LESS THAN (100) ENGINE=InnoDB, PARTITION p2 VALUES LESS THAN MAXVALUE ENGINE=InnoDB );
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL

Expected result: Result is correct, as I'm mixing storage engines

Suggested fix:
#test 1 should return - ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL - instead of silently creating the table with an engine different from default_storage_engine.
[3 Apr 2019 5:51] MySQL Verification Team
Hello Marcelo,

Thank you for the report and feedback.
Verified as described.

regards,
Umesh

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.049 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.