Background

"disabled_storage_engines" is a new parameter that is introduc ed in Version 5.7.8, and used to set the disabled storage engine when creating a table ("CREATE TABLE"). For example, disable MyISAM and MEMORY engines when creating a table.

[mysqld]
disabled_storage_engines="MyISAM,MEMORY"

Failure phenomenon

By "disabled_storage_engines" parameter, you may realize the automatic switch of storage engines when creating a table. For example, execute the following table creation SQL

CREATE TABLE tb_01 (
id int(11) NOT NULL AUTO_INCREMENT,
age int(11) NOT NULL DEFAULT '0',
name varchar(64) NOT NULL DEFAULT '',
PRIMARY KEY (id)
) ENGINE=MyISAM;

After the creation, there are warnings returned, and the table's storage engine automatically switches to the InnoDB engine:

Analysis of cause

When "NO_ENGINE_SUBSTITUTION" is not set in "sql_mode", and the storage engine specified by "ENGINE" in "CREATE TABLE" is disabled by the parameter "disabled_storage_engines", MySQL changes the newly created table's engine into storage engine by default (namely, InnoDB).

Solution

Set parameters according to the three steps below. When creating a table, you may convert the specified storage engine into InnoDB engine:

1. Set the disabled storage engine, for example, Disable MyISAM and MEMORY Engine:

[mysqld]
disabled_storage_engines="MyISAM,MEMORY"

2. In "sql_mode" setting there is no "NO_ENGINE_SUBSTITUTION":

SET sql_mode='';

3. Set the storage engine by default as InnoDB:

SET GLOBAL default_storage_engine=InnoDB;

Conclusions & suggestions

• In terms of stability and performance, we recommend using the InnoDB engine as storage engine in MySQL database.
• You are advised to set the "disabled_storage_engines" parameter, which disables the storage engines like MyISAM and MEMORY and automatically switches to the InnoDB engine upon table creation.
• In previous versions as of MySQL5.7.23 there is a Bug, namely, "disabled_storage_engines" parameter's failure to realize automatic switch after the setting, with the description of Bug as follows:

Bug #27502530
With the NO_ENGINE_SUBSTITUTION SQL mode disabled, an error (rather than substitution) occurred for CREATE TABLE and ALTER TABLE if the desired engine was disabled using the disabled_storage_engines system variable. 

• "Partition table" is unable to realize automatic engine switch, and there is an error report in the creation of partition table for the disabled engine, for MySQL can only switch engine for the partition table's master table rather than for the partition table's child table, thus leading to an error report.

ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL