MySQL无法创建表的问题分析(r12笔记第73天)

今天在下班前帮同事处理了一个看起来很有意思的问题，虽然知道了问题的方向和大体的原因，但是当时因为时间原因还是没想到如何复现这个问题，晚上回到家，收拾收拾，打开电脑，反向推理，求证，测试，重现，于是才有了这个问题的完整解读。

问题背景

问题的描述听起来很简单，就是在部署一个数据变更的时候抛出了错误，我带着好奇心凑了过去，看到了这个错误。

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)