Bug #94875 MySQL is performing Engine substitution with partitions
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.
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.