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.
