MySQL中表创建失败的原因之一
来源:互联网 发布:jquery数组删除空元素 编辑:程序博客网 时间:2024/05/21 11:36
报错信息:
ERROR 1005 (HY000): Can't create table 'example_db.example_table' (errno: 150)
故事背景:从生产环境example_db数据库往测试环境对应的库导入恢复数据。因为前一天使用mysqldump工具对生产环境整个实例中所有数据库做过一次备份,考虑到再做一次针对example_db库的备份比较耗时,于是决定直接使用sed工具从原来的完整备份中截取出example_db对应的部分,然后使用该部分逻辑备份文件恢复测试环境的example_db数据库.所使用的命令如下:
<pre name="code" class="plain">sed -n '/-- Current Database: `example_db`/,/-- Current Database: `example_db2`/p' fullbakcup.sql > /data/tmp/example_db.sql其中fullbakcup.sql为已经存在的使用mysqldump做的全备份文件,example_db2为备份文件中紧随example_db对应的备份内容之后的数据库,该命令的作用是从备份文件中截取出example_db对应的备份内容。至此,一切看似没有问题。但是通过如下命令执行时,报出如下错误:
mysql -uuser -p -htest.db.service.com example_db < /data/tmp/example_db.sqlERROR 1005 (HY000): Can't create table 'example_db.example_table' (errno: 150)打开截取生成的备份文件找到创建example_db.example_table的语句,发现创建表的语句如下,表中包含外键约束,而外键引用了来自example_table_2表的列,而在创建当前表时外键引用的表尚未创建,这应该就是导致表创建失败的原因。
<pre name="code" class="sql">DROP TABLE IF EXISTS `example_table`;/*!40101 SET @saved_cs_client = @@character_set_client */;/*!40101 SET character_set_client = utf8 */;CREATE TABLE `example_table` ( `TRIGGER_NAME` varchar(200) NOT NULL, `TRIGGER_GROUP` varchar(200) NOT NULL, `BLOB_DATA` blob, `datachange_lasttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '行更新时间', PRIMARY KEY (`TRIGGER_NAME`,`TRIGGER_GROUP`), CONSTRAINT `ibfk_1` FOREIGN KEY (`TRIGGER_NAME`, `TRIGGER_GROUP`) REFERENCES `example_table_2` (`TRIGGER_NAME`, `TRIGGER_GROUP`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;/*!40101 SET character_set_client = @saved_cs_client */;那么如何避开呢?可以通过两种方法:
其一,手工调整备份文件中创建表的先后顺序,保证后边的表定义中要引用的表先被创建;
其二,在创建各个表之前先关闭外键约束检查,在所有的表创建完成之后重新打开外键约束检查;
第一种方案理论上可行,但实际操作起来较为毛麻烦,而第二种方案只需简单的在备份文件的开始和末尾添加关闭/打开外键约束检查的命令即可,具体的命令为:
<pre name="code" class="sql">--关闭SET FOREIGN_KEY_CHECKS=0--打开SET FOREIGN_KEY_CHECKS=1按照这个思路在截取出的备份文件首尾分别添加以上两条命令,再次执行
mysql -uuser -p -htest.db.service.com example_db < /data/tmp/example_db.sql发现可以成功运行备份文件中的命令了。
我们平时使用mysqldump工具备份数据库时,也没有明确的指明在备份文件首尾添加控制外键约束的检查啊,为什么不会出现创建数据库失败的情形呢?其实仔细查看完整的备份文件会发现,备份文件的首尾分别存在如下内容,其中分别包含了关闭和开启外键约束检查的命令,不仅如此,还包含进了其他一些设置命令如设置客户端字符集、结果字符集、时区、唯一索引检查、SQL Mode等信息,也就是说mysqldump工具已经默认帮我们做好了这些。所以在从完整备份文件中截取内容片断是最好在截取结果的首尾添加如下部分的内容以避免错误并提高性能。
--头部/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;/*!40103 SET TIME_ZONE='+00:00' */;/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;--尾部/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
0 0
- MySQL中表创建失败的原因之一
- MySQL中表的创建
- mysql外键创建失败的原因
- 网狐报服务组件加载失败,创建游戏房间失败的原因之一
- APK安装失败的原因之一
- VC断点失败的原因之一
- LaunchImage设置失败的可能原因之一
- Win10激活失败的原因之一
- 创建线程出错的原因之一
- COM组件实例创建失败的原因
- 创建空白文档失败的原因
- 创建ffdshow Audio Decoder失败的原因
- clucene创建IndexSearcher时失败的原因
- qt_ros package 创建失败的原因
- fopen自动创建文件失败的原因
- pthread_create函数创建线程失败的原因
- mysql中表的应用
- MySQL中表的操作
- Java泛型
- JSP和servlet之间的通信
- poj3614
- 释放WinPE所占用的U盘空间
- 线程冲突整理
- MySQL中表创建失败的原因之一
- 安卓Activity的四种加载模式
- 【DB】mysql 字段类型
- OC解析XML文件
- iOS 系统声音服务与震动
- DBCP连接池配置参数说明
- windows编译x264
- pc前端开发小结
- 无法嵌入互操作类型