自建数据库迁移云数据库建表失败报错的解决方法

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

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

参数设置请参考:阿里云阿里云数据库参数设置指南

问题现象

阿里云数据库用户在将IDC自建数据库迁移上云时,建表失败报错。

ERROR 1031 (HY000): Table storage engine '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 't_rf' doesn't have this option

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

参数客户IDC阿里云阿里云数据库innodb_strict_modeOFFON
解决方案

更改阿里云数据库全局参数: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;

LevelCodeMessageWarning1478InnoDB: 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 't_01' doesn't have this option

在控制台修改innodb_strict_mode=OFF登陆数据库重新建表则建表成功,并报warning:

LevelCodeMessageWarning1478InnoDB: assuming ROW_FORMAT=COMPACT
总结建议

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