云数据库RDS

    innodb_strict_mode参数使用详解

    背景

    百度云支持更改数据库innodb_strict_mode设置参数,也可以session级别更改,该参数修改后立即生效,不需要重启实例:

    • innodb_strict_mode=ON,表示使用严格模式,当创建表(CREATE TABLE)、更改表(ALTER TABLE)和创建索引(CREATE INDEX)语句时,如果写法有错误,不会有警告信息,而是直接抛出错误。
    • innodb_strict_mode=OFF,表示使用非严格模式,即使用宽松的语法检查,并且使用默认的语法 替代错误的语法。

    参数设置请参考:百度云RDS参数设置指南

    问题现象

    某百度云RDS用户在将IDC自建数据库迁移上云时,建表失败报错。

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

    原因分析

    用户查看上游客户的建表SQL语句:

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

    下游云数据库执该sql时报错:因此判断数据库执行drop database命令失败,转而生成drop table操作,由于表名都是小写字母,导致删除含大写字母的表失败。

    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

    得到用户授权后,由百度云工程师对比客户自建数据库和云上数据库的配置参数,发现存在如下差异:

    参数 客户IDC 百度云RDS
    innodb_strict_mode OFF ON

    解决方案

    更改百度云数据库全局参数:innodb_strict_mode =OFF后测试,建表成功,但是存在Warning信息:

    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;

    Level Code Message
    Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT

    案例复现

    当前参数环境innodb_strict_mode=ON(严格模式),在baidu_dba库下创建表,其中一个表名大写、两个表名是小写,SQL语句如下:

    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

    在控制台修改innodb_strict_mode=OFF(非严格模式) 登陆数据库重新建表 则建表成功,并报warning:

    Level Code Message
    Warning 1478 InnoDB: assuming ROW_FORMAT=COMPACT

    总结建议

    • 数据库迁移上云,建议参数 innodb_strict_mode 迁移前后保持一致
    • 云上新建数据库,建议使用严格模式,即 innodb_strict_mode=ON

    MySQL官方文档参考:https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_strict_mode

    上一篇
    MySQL之interactive_timeout和wait_timeout
    下一篇
    log_timestamps参数使用详解