Bug #93409 disabled_storage_engines does not catch partition engines
Submitted: 29 Nov 2018 16:43 Modified: 30 Nov 2018 5:20
Reporter: Matthew Van Dijk Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7.24 OS:Any
Assigned to: CPU Architecture:Any

[29 Nov 2018 16:43] Matthew Van Dijk
Description:
The sys var "disabled_storage_engines" does not block tables that use a disabled engine in its PARTITION definitions. Note that it does block if the partitioned table's default engine is set to a disabled engine.

Variables:

disabled_storage_engines = myisam
default_storage_engine = InnoDB
sql_mode = NO_ENGINE_SUBSTITUTION

Table engine is blocked correctly:

mysql> create table t (c int) engine=myisam partition by range(c) (PARTITION p0 VALUES LESS THAN(10), PARTITION p1 VALUES LESS THAN(20));
ERROR 3161 (HY000): Storage engine MyISAM is disabled (Table creation is disallowed).

Partition engine is not blocked:

mysql> create table t (c int) partition by range(c) (PARTITION p0 VALUES LESS THAN(10) ENGINE=myisam, PARTITION p1 VALUES LESS THAN(20) ENGINE=myisam);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `c` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (c)
(PARTITION p0 VALUES LESS THAN (10) ENGINE = MyISAM,
 PARTITION p1 VALUES LESS THAN (20) ENGINE = MyISAM) */
1 row in set, 1 warning (0.00 sec)

How to repeat:
mysql> create table t (c int) partition by range(c) (PARTITION p0 VALUES LESS THAN(10) ENGINE=myisam, PARTITION p1 VALUES LESS THAN(20) ENGINE=myisam);

Suggested fix:
Check the disabled_storage_engines variable during something like partition_info::check_partition_info(). Also try substituting the default_storage_engine if sql_mode is not set to NO_ENGINE_SUBSTITUTION.
[30 Nov 2018 5:20] MySQL Verification Team
Hello Matthew,

Thank you for the report and test case.

regards,
Umesh