Background

MySQL database can control the database behaviors via the parameter "sql_mode". The following introduces "NO_ENGINE_SUBSTITUTION", one of "sql_mode" values. Usage: when statements like "CREATE TABLE" or "ALTER TABLE" specify one disabled or "Not Compiled" storage engine, it controls the automatic switch of the storage engine by default.

Failure Phenomenon

Why is the InnoDB engine displayed in the creation of a table with the "FEDERATED" engine?

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=FEDERATED;

Execute the command. Then, the result below is displayed:

Analysis of cause

Three factors lead to the occurrence of the problem above:

1. Current RDS instance does not support "FEDERATED" engine, log in to the database to execute the following SQL command:

SHOW ENGINES;

2. In "sql_mode" setting there is no "NO_ENGINE_SUBSTITUTION", log in to the database to execute the following SQL command:

SHOW VARIABLES LIKE 'sql_mode';

3. Current RDS instance's storage engine by default is InnoDB, log in to the database to execute the following SQL command:

SHOW VARIABLES LIKE 'default_storage_engine';

Note: If "NO_ENGINE_SUBSTITUTION" is not set in "sql_mode", and storage engine specified by "ENGINE" in "CREATE TABLE" is not supported by the current database instance, MySQL changes the table's engine into the storage engine by default (InnoDB).

Solution

• Solution I: install "FEDERATED" engine for the database instance.
• Solution II: set "sql_mode" with "NO_ENGINE_SUBSTITUTION", to give definite error prompt and avoid the misunderstanding, for example:

Conclusions & suggestions

In terms of stability and performance, we recommend using the InnoDB engine as MySQL database storage engine, and you are not advised to use other engines like MyISAM and MEMORY.