Background

Baidu AI Cloud supports modification to the database's "innodb_strict_mode" setting parameters, and also session-level modification is supported. This parameter is enabled as soon as it is modified without the need to restart the instance.

• "innodb_strict_mode=ON" indicates that strict mode is used. And in this mode, if there is an error in writing when using statements like CREATE TABLE, ALTER TABLE, and CREATE INDEX, there is no warning information, and error directly pops up.
• "innodb_strict_mode=OFF" indicates non-strict mode is used. In other words, a loose syntax check is adopted, and syntax by default is used to replace the wrong syntax.

For parameter setting, see Baidu AI Cloud RDS Parameter Setting Guide .

Failure Phenomenon

One Baidu AI Cloud RDS user fails to create the table when migrating the IDC self-built database to the cloud, and then an error is reported.

ERROR 1031 (HY000): Table storage engine for 'tb_01' doesn't have this option

Analysis of cause

The users view the upstream customers' SQL statement for table creation:

CREATE TABLE `tb_01` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

An error occurs when the downstream cloud database executes this SQL statement. From this, we can judge that the database fails to execute the "drop database" command and then generates a "drop table" action. Since the table is named in lowercase letters, thus deletion to a table containing uppercase letters fails.

CREATE TABLE `tb_01` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
ERROR 1031 (HY000): Table storage engine for 't_rf' doesn't have this option

After being authorized by the users, Baidu AI Cloud engineers make a comparison of the configuration parameters between the self-built database and cloud database, finding the following differences:

Solutions

Modify Baidu Cloud Database's global parameter "innodb_strict_mode ＝OFF" and then perform the test, finding that the table is successfully created, but there is still "Warning" information:

CREATE TABLE tb_01 ( id int(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED; Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show warnings;

Case review

Current parameter environment is "innodb_strict_mode=ON" (strict mode). Create a table in the "baidu_dba" database, of which a table is named in uppercase letters, and two tables are named in lowercase letters. The SQL statement is as follows:

CREATE TABLE tb_01 (
   id int(11) NOT NULL AUTO_INCREMENT,
   name varchar(64) DEFAULT NULL,
   PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;

ERROR 1031 (HY000): Table storage engine for 't_01' doesn't have this option

On the console, modify "innodb_strict_mode=OFF (non-strict mode), log in to the database, create the table anew, finding that the table is successfully created, but there is a warning:

Conclusions & suggestions

• In the migration of a database to a cloud, you are advised to keep consistent the parameter "innodb_strict_mode" before and after the migration.
• In the creation of a database on cloud, you are advised to use strict mode, namely, innodb_strict_mode=ON

For MySQL official documentation, see https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_strict_mode