MySQL無(wú)法創(chuàng )建表的原因

本篇內容主要講解“無(wú)法創(chuàng )建表的原因”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強。下面就讓小編來(lái)帶大家學(xué)習“MySQL無(wú)法創(chuàng )建表的原因”吧!

  今天在下班前幫同事處理了一個(gè)看起來(lái)很有意思的問(wèn)題，雖然知道了問(wèn)題的方向和大體的原因，但是當時(shí)因為時(shí)間原因還是沒(méi)想到如何復現這個(gè)問(wèn)題，晚上回到家，收拾收拾，打開(kāi)電腦，反向推理，求證，測試，重現，于是才有了這個(gè)問(wèn)題的完整解讀。

問(wèn)題背景

   問(wèn)題的描述聽(tīng)起來(lái)很簡(jiǎn)單，就是在部署一個(gè)數據變更的時(shí)候拋出了錯誤，我帶著(zhù)好奇心湊了過(guò)去，看到了這個(gè)錯誤。

ERROR 1005 (HY000): Can't create table 'xxx.QRTZ_JOB_DETAILS' (errno: 150)這個(gè)create table的語(yǔ)句是什么樣，是不是有什么特別之處呢？這個(gè)語(yǔ)句其實(shí)沒(méi)什么特別的，沒(méi)有用到什么新版本的特性和語(yǔ)法。

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   現在的問(wèn)題是創(chuàng )建10多個(gè)表，只有2個(gè)表創(chuàng )建失敗了，單獨創(chuàng )建就拋出了這個(gè)問(wèn)題，聽(tīng)起來(lái)很尷尬啊。

  對于這個(gè)問(wèn)題的直覺(jué)就是bug或者是參數的設置超出了限制，但是僅僅是一個(gè)猜測而已，處理問(wèn)題一定要嚴謹，帶著(zhù)好奇心查清楚，要么這就是一個(gè)無(wú)底洞，只會(huì )給自己帶來(lái)更多攻略秘籍，知其所以然不知其然。  

問(wèn)題初步分析

   對于這個(gè)問(wèn)題，如此的境況讓我有了很大的興趣，我決定也試試看，能不能找到一個(gè)有說(shuō)服力的證據來(lái)?？粗?zhù)這個(gè)create 語(yǔ)句，腦子里像過(guò)篩子似的在進(jìn)行各種的排除，表字段太多，主鍵字段太多，表屬性格式設置，lob字段影響，數據庫的字段個(gè)數溢出等等,可能存在的語(yǔ)法限制等。

    我開(kāi)始做了下面的測試，這個(gè)測試讓上面的猜測都沒(méi)有了立足之地，因為我只是創(chuàng )建了一個(gè)字段而已，但是還是不行。

CREATE TABLE `QRTZ_JOB_DETAILS` (`SCHED_NAME` varchar(120) NOT NULL);
ERROR 1005 (HY000): Can't create table 'test.QRTZ_JOB_DETAILS' (errno: 150)有的同學(xué)可能在想是不是大小寫(xiě)敏感導致的?

show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+這個(gè)環(huán)境中是開(kāi)啟了大小寫(xiě)敏感的設置，但是這個(gè)不足以成為問(wèn)題無(wú)法解決的原因。

  是不是涉及了什么相關(guān)的語(yǔ)法灰色地帶了，我在表名后面加了一個(gè)S.

> create table QRTZ_JOB_DETAILSS(id int);
Query OK, 0 rows affected (0.13 sec)這說(shuō)明這個(gè)表的限制和語(yǔ)法陷阱也沒(méi)有關(guān)系，但是創(chuàng )建這個(gè)表就這么糾結。

> create table QRTZ_JOB_DETAILS(id int);
ERROR 1005 (HY000): Can't create table 'seal.QRTZ_JOB_DETAILS' (errno: 150)而一個(gè)臨時(shí)的解決方法就是創(chuàng )建了一個(gè)小寫(xiě)的表，創(chuàng )建過(guò)程是沒(méi)有問(wèn)題的，但是開(kāi)發(fā)同學(xué)那邊是沒(méi)法推進(jìn)了，因為他們的應用程序端是第三方的Quarz的調度項目，他們識別是按照大寫(xiě)的格式來(lái)的。

 有的同學(xué)可能說(shuō)，那可能是外鍵導致的，我查了一圈部署的腳本，里面連一個(gè)REFERENCE的影子都找不到，部署的腳本里壓根就沒(méi)有外鍵的字眼。

  有的同學(xué)可能說(shuō)有問(wèn)題看看日志怎么說(shuō)，mysql這一點(diǎn)上提供的信息極少，error log里面的信息只有一行報出的錯誤，其它更具體的信息就沒(méi)有了。

   同時(shí)我也有些猶豫，我排查了數據庫版本帶來(lái)的影響，在5.1, 5.5版本中都進(jìn)行了對比測試，竟然沒(méi)有發(fā)現問(wèn)題，只是問(wèn)題依舊存在。  

和開(kāi)發(fā)同學(xué)進(jìn)一步溝通

    帶著(zhù)疑問(wèn)，我和開(kāi)發(fā)同學(xué)做了進(jìn)一步溝通，他們引用的腳本是一個(gè)第三方的開(kāi)源項目Quarz,里面的腳本是使用navicat生成的，而這個(gè)變更在他們的測試環(huán)境是部署通過(guò)的，測試環(huán)境是5.1版本，而線(xiàn)上環(huán)境是5.5，第三方提供的腳本涉及的表有很多，我拿到了一份腳本，部署在我自己的測試環(huán)境中，竟然沒(méi)有錯誤。

    后來(lái)開(kāi)發(fā)同學(xué)做了進(jìn)一步確認，把數據庫中QRTZ字樣的表都刪除（前提是有備份），因為這是一批次的變更，要么可用，要么回退，刪除了這些表之后，再次嘗試創(chuàng )建剛剛失敗的表，這次竟然成功了。而這個(gè)過(guò)程中我也沒(méi)有做什么特別的操作，開(kāi)發(fā)同學(xué)最后無(wú)奈的說(shuō)，是不是和人品有關(guān)系啊，如果同事聽(tīng)到，那不得吐血。  

蛛絲馬跡找到問(wèn)題的突破口

   在技術(shù)問(wèn)題上，很多確實(shí)可能是bug導致的，但是我們不能把所有看起來(lái)奇怪的問(wèn)題都歸類(lèi)給bug,而從我處理的很多問(wèn)題來(lái)看，很多最后雖然可以歸類(lèi)為bug,但問(wèn)題的根因很多還是和一些很基本的錯誤導致，這一關(guān)把好了，很多問(wèn)題都會(huì )扼殺在搖籃之中。

   這個(gè)問(wèn)題怎么分析呢，mysql的query log記錄了所有操作的過(guò)程，這給我帶來(lái)很大的便利，這樣我就能看到每一步執行的過(guò)程中的一個(gè)基本情況了。當時(shí)做了什么嘗試，之前做過(guò)什么變更都一目了然。當然這個(gè)日志給了我一些很明確的信息，但是還沒(méi)有找到問(wèn)題的原因所在。

  在清理表結構之前，我下意識做了一個(gè)基本的信息備份，這是清理之前的表的情況。

 > 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   |
+--------------------------+    我打開(kāi)部署的腳本開(kāi)始認真看起來(lái)，腳本里面沒(méi)有任何的外鍵信息，但是我感覺(jué)問(wèn)題的方向已經(jīng)很明確了，只是比較隱蔽，或者是之前分析的時(shí)候漏掉了。

    當我看到日志里面無(wú)意檢查倒的信息時(shí)，不禁眼前一亮,創(chuàng )建失敗的表是QRTZ_JOB_DETAILS,而表名類(lèi)似的只有QRTZ_JOB_LISTENERS,這個(gè)表結構定義信息說(shuō)得很清楚了。

> 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，而實(shí)際上腳本里面沒(méi)有任何外鍵的信息，那只有一個(gè)可能，那就是QRTZ_JOB_LISTENERS不在這個(gè)腳本中，很可能是在這次部署之外就創(chuàng )建好的。這一點(diǎn)尤其重要，也是這個(gè)問(wèn)題的突破口。

  怎么驗證之前的狀態(tài)呢，我看了下這套環(huán)境的備份策略，驚喜的是每天會(huì )有一次備份，我簡(jiǎn)單過(guò)濾了一下，問(wèn)題的原因就開(kāi)始清晰起來(lái)了。

# 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` (

而且這樣看來(lái)問(wèn)題比我們想象的還要復雜些，表QRTZ_JOB_DETAILS和QRTZ_JOB_LISTENERS以前就存在,而這次的部署變更，開(kāi)發(fā)同學(xué)只是提交了QRTZ_JOB_DETAILS的變更。

模擬復現問(wèn)題

  有了上面的分析，問(wèn)題的原因就很清晰了，因為表QRTZ_JOB_DETAILS在以前就存在，是QRTZ_JOB_LISTENERS的外鍵關(guān)聯(lián)表，這次做變更只有QRTZ_JOB_DETAILS,先刪除，再創(chuàng )建的過(guò)程中就會(huì )因為外鍵依賴(lài)關(guān)系的原因而失敗。

 這里就不得不提到navicat這個(gè)工具的神助攻，因為正常來(lái)說(shuō)刪除一個(gè)表，如果存在外鍵引用是肯定刪不掉的，會(huì )有下面的錯誤。

> DROP TABLE IF EXISTS `QRTZ_JOB_DETAILS`;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails但是navicat偏偏做了一些工作，它會(huì )自動(dòng)生成一些輔助腳本內容，在腳本執行前會(huì )有下面的語(yǔ)句，這樣一來(lái)，就可以刪除這個(gè)表了。

> 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)這樣一來(lái)，問(wèn)題就很容易復現了。

> CREATE TABLE `QRTZ_JOB_DETAILS` (`SCHED_NAME` varchar(120) NOT NULL);
ERROR 1005 (HY000): Can't create table 'test.QRTZ_JOB_DETAILS' (errno: 150)

 
補充，用這個(gè)命令來(lái)看看150錯誤的含義
# perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed