共计 5290 个字符，预计需要花费 14 分钟才能阅读完成。
本篇内容主要讲解“MySQL 无法创建表的原因”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“MySQL 无法创建表的原因”吧!
今天在下班前帮同事处理了一个看起来很有意思的问题，虽然知道了问题的方向和大体的原因，但是当时因为时间原因还是没想到如何复现这个问题，晚上回到家，收拾收拾，打开电脑，反向推理，求证，测试，重现，于是才有了这个问题的完整解读。
问题背景
问题的描述听起来很简单，就是在部署一个数据变更的时候抛出了错误，我带着好奇心凑了过去，看到了这个错误。
ERROR 1005 (HY000): Can t create table xxx.QRTZ_JOB_DETAILS (errno: 150) 这个 create table 的语句是什么样，是不是有什么特别之处呢？这个语句其实没什么特别的，没有用到什么新版本的特性和语法。
DROP TABLE IF EXISTS `QRTZ_JOB_DETAILS`;
CREATE TABLE `QRTZ_JOB_DETAILS` (
`SCHED_NAME` varchar(120) NOT NULL,
`JOB_NAME` varchar(200) NOT NULL,
`JOB_GROUP` varchar(200) NOT NULL,
`DESCRIPTION` varchar(250) DEFAULT NULL,
`JOB_CLASS_NAME` varchar(250) NOT NULL,
`IS_DURABLE` varchar(1) NOT NULL,
`IS_NONCONCURRENT` varchar(1) NOT NULL,
`IS_UPDATE_DATA` varchar(1) NOT NULL,
`REQUESTS_RECOVERY` varchar(1) NOT NULL,
`JOB_DATA` blob,
PRIMARY KEY (`SCHED_NAME`,`JOB_NAME`,`JOB_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 现在的问题是创建 10 多个表，只有 2 个表创建失败了，单独创建就抛出了这个问题，听起来很尴尬啊。
对于这个问题的直觉就是 bug 或者是参数的设置超出了限制，但是仅仅是一个猜测而已，处理问题一定要严谨，带着好奇心查清楚，要么这就是一个无底洞，只会给自己带来更多攻略秘籍，知其所以然不知其然。
问题初步分析
对于这个问题，如此的境况让我有了很大的兴趣，我决定也试试看，能不能找到一个有说服力的证据来。看着这个 create
语句，脑子里像过筛子似的在进行各种的排除，表字段太多，主键字段太多，表属性格式设置，lob 字段影响，数据库的字段个数溢出等等, 可能存在的语法限制等。
我开始做了下面的测试，这个测试让上面的猜测都没有了立足之地，因为我只是创建了一个字段而已，但是还是不行。
CREATE TABLE `QRTZ_JOB_DETAILS` (`SCHED_NAME` varchar(120) NOT NULL);
ERROR 1005 (HY000): Can t create table test.QRTZ_JOB_DETAILS (errno: 150) 有的同学可能在想是不是大小写敏感导致的?
show variables like %case%
+————————+——-+
| Variable_name | Value |
+————————+——-+
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
+————————+——-+ 这个环境中是开启了大小写敏感的设置，但是这个不足以成为问题无法解决的原因。
是不是涉及了什么相关的语法灰色地带了，我在表名后面加了一个 S.
create table QRTZ_JOB_DETAILSS(id int);
Query OK, 0 rows affected (0.13 sec) 这说明这个表的限制和语法陷阱也没有关系，但是创建这个表就这么纠结。
create table QRTZ_JOB_DETAILS(id int);
ERROR 1005 (HY000): Can t create table seal.QRTZ_JOB_DETAILS (errno: 150) 而一个临时的解决方法就是创建了一个小写的表，创建过程是没有问题的，但是开发同学那边是没法推进了，因为他们的应用程序端是第三方的 Quarz 的调度项目，他们识别是按照大写的格式来的。
有的同学可能说，那可能是外键导致的，我查了一圈部署的脚本，里面连一个 REFERENCE 的影子都找不到，部署的脚本里压根就没有外键的字眼。
有的同学可能说有问题看看日志怎么说，mysql 这一点上提供的信息极少，error log 里面的信息只有一行报出的错误，其它更具体的信息就没有了。
同时我也有些犹豫，我排查了数据库版本带来的影响，在 5.1, 5.5 版本中都进行了对比测试，竟然没有发现问题，只是问题依旧存在。
和开发同学进一步沟通
带着疑问，我和开发同学做了进一步沟通，他们引用的脚本是一个第三方的开源项目 Quarz, 里面的脚本是使用 navicat 生成的，而这个变更在他们的测试环境是部署通过的，测试环境是 5.1 版本，而线上环境是 5.5，第三方提供的脚本涉及的表有很多，我拿到了一份脚本，部署在我自己的测试环境中，竟然没有错误。
后来开发同学做了进一步确认，把数据库中 QRTZ 字样的表都删除（前提是有备份），因为这是一批次的变更，要么可用，要么回退，删除了这些表之后，再次尝试创建刚刚失败的表，这次竟然成功了。而这个过程中我也没有做什么特别的操作，开发同学最后无奈的说，是不是和人品有关系啊，如果同事听到，那不得吐血。
蛛丝马迹找到问题的突破口
在技术问题上，很多确实可能是 bug 导致的，但是我们不能把所有看起来奇怪的问题都归类给 bug, 而从我处理的很多问题来看，很多最后虽然可以归类为 bug, 但问题的根因很多还是和一些很基本的错误导致，这一关把好了，很多问题都会扼杀在摇篮之中。
这个问题怎么分析呢，mysql 的 query
log 记录了所有操作的过程，这给我带来很大的便利，这样我就能看到每一步执行的过程中的一个基本情况了。当时做了什么尝试，之前做过什么变更都一目了然。当然这个日志给了我一些很明确的信息，但是还没有找到问题的原因所在。
在清理表结构之前，我下意识做了一个基本的信息备份，这是清理之前的表的情况。
show tables like QRTZ%
+————————–+
| Tables_in_seal (QRTZ%) |
+————————–+
| QRTZ_BLOB_TRIGGERS |
| QRTZ_CALENDARS |
| QRTZ_CRON_TRIGGERS |
| QRTZ_FIRED_TRIGGERS |
| QRTZ_JOB_LISTENERS |
| QRTZ_LOCKS |
| QRTZ_PAUSED_TRIGGER_GRPS |
| QRTZ_SCHEDULER_STATE |
| QRTZ_SIMPLE_TRIGGERS |
| QRTZ_SIMPROP_TRIGGERS |
| QRTZ_TRIGGER_LISTENERS |
+————————–+ 我打开部署的脚本开始认真看起来，脚本里面没有任何的外键信息，但是我感觉问题的方向已经很明确了，只是比较隐蔽，或者是之前分析的时候漏掉了。
当我看到日志里面无意检查倒的信息时，不禁眼前一亮, 创建失败的表是 QRTZ_JOB_DETAILS, 而表名类似的只有 QRTZ_JOB_LISTENERS, 这个表结构定义信息说得很清楚了。
show create table QRTZ_JOB_LISTENERS\G
*************************** 1. row ***************************
Table: QRTZ_JOB_LISTENERS
Create Table: CREATE TABLE `QRTZ_JOB_LISTENERS` (
`JOB_NAME` varchar(200) NOT NULL,
`JOB_GROUP` varchar(200) NOT NULL,
`JOB_LISTENER` varchar(200) NOT NULL,
PRIMARY KEY (`JOB_NAME`,`JOB_GROUP`,`JOB_LISTENER`),
KEY `JOB_NAME` (`JOB_NAME`,`JOB_GROUP`),
CONSTRAINT `QRTZ_JOB_LISTENERS_ibfk_1` FOREIGN KEY (`JOB_NAME`,
`JOB_GROUP`) REFERENCES `QRTZ_JOB_DETAILS` (`JOB_NAME`, `JOB_GROUP
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)QRTZ_JOB_LISTENERS 里是存在外键，是指向了 QRTZ_JOB_DETAILS，而实际上脚本里面没有任何外键的信息，那只有一个可能，那就是 QRTZ_JOB_LISTENERS 不在这个脚本中，很可能是在这次部署之外就创建好的。这一点尤其重要，也是这个问题的突破口。
怎么验证之前的状态呢，我看了下这套环境的备份策略，惊喜的是每天会有一次备份，我简单过滤了一下，问题的原因就开始清晰起来了。
# grep CREATE TABLE \`QRTZ_ *33-7*.sql|sort|uniq
CREATE TABLE `QRTZ_BLOB_TRIGGERS` (
CREATE TABLE `QRTZ_CALENDARS` (
CREATE TABLE `QRTZ_CRON_TRIGGERS` (
CREATE TABLE `QRTZ_FIRED_TRIGGERS` (
CREATE TABLE `QRTZ_JOB_DETAILS` (
CREATE TABLE `QRTZ_JOB_LISTENERS` (
CREATE TABLE `QRTZ_LOCKS` (
CREATE TABLE `QRTZ_PAUSED_TRIGGER_GRPS` (
CREATE TABLE `QRTZ_SCHEDULER_STATE` (
CREATE TABLE `QRTZ_SIMPLE_TRIGGERS` (
CREATE TABLE `QRTZ_SIMPROP_TRIGGERS` (
CREATE TABLE `QRTZ_TRIGGER_LISTENERS` (
CREATE TABLE `QRTZ_TRIGGERS` (
而且这样看来问题比我们想象的还要复杂些，表 QRTZ_JOB_DETAILS 和 QRTZ_JOB_LISTENERS 以前就存在, 而这次的部署变更，开发同学只是提交了 QRTZ_JOB_DETAILS 的变更。
模拟复现问题
有了上面的分析，问题的原因就很清晰了，因为表 QRTZ_JOB_DETAILS 在以前就存在，是 QRTZ_JOB_LISTENERS 的外键关联表，这次做变更只有 QRTZ_JOB_DETAILS, 先删除，再创建的过程中就会因为外键依赖关系的原因而失败。
这里就不得不提到 navicat 这个工具的神助攻，因为正常来说删除一个表，如果存在外键引用是肯定删不掉的，会有下面的错误。
DROP TABLE IF EXISTS `QRTZ_JOB_DETAILS`;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails 但是 navicat 偏偏做了一些工作，它会自动生成一些辅助脚本内容，在脚本执行前会有下面的语句，这样一来，就可以删除这个表了。
SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)
DROP TABLE IF EXISTS `QRTZ_JOB_DETAILS`;
Query OK, 0 rows affected (0.00 sec) 这样一来，问题就很容易复现了。
CREATE TABLE `QRTZ_JOB_DETAILS` (`SCHED_NAME` varchar(120) NOT NULL);
ERROR 1005 (HY000): Can t create table test.QRTZ_JOB_DETAILS (errno: 150)
补充，用这个命令来看看 150 错误的含义
# perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed
到此，相信大家对“MySQL 无法创建表的原因”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
