Bug #94875 MySQL is performing Engine substitution with partitions
Submitted: 2 Apr 19:21 Modified: 3 Apr 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 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 5:51] Umesh Shastry
Hello Marcelo,

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

regards,
Umesh