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

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

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

問(wèn)題背景

   問(wèn)題的描述聽(tīng)起來(lái)很簡(jiǎn)單，就是在部署一個(gè)數(shù)據(jù)變更的時(shí)候拋出了錯(cuò)誤，我?guī)е闷嫘臏惲诉^(guò)去，看到了這個(gè)錯(cuò)誤。

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

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

問(wèn)題初步分析

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

    我開(kāi)始做了下面的測(cè)試，這個(gè)測(cè)試讓上面的猜測(cè)都沒(méi)有了立足之地，因?yàn)槲抑皇莿?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ě)敏感導(dǎo)致的?

show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+這個(gè)環(huán)境中是開(kāi)啟了大小寫(xiě)敏感的設(shè)置，但是這個(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è)表就這么糾結(jié)。

> 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)了，因?yàn)樗麄兊膽?yīng)用程序端是第三方的Quarz的調(diào)度項(xiàng)目，他們識(shí)別是按照大寫(xiě)的格式來(lái)的。

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

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

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

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

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

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

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

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

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

  在清理表結(jié)構(gòu)之前，我下意識(shí)做了一個(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)始認(rèn)真看起來(lái)，腳本里面沒(méi)有任何的外鍵信息，但是我感覺(jué)問(wèn)題的方向已經(jīng)很明確了，只是比較隱蔽，或者是之前分析的時(shí)候漏掉了。

    當(dāng)我看到日志里面無(wú)意檢查倒的信息時(shí)，不禁眼前一亮,創(chuàng)建失敗的表是QRTZ_JOB_DETAILS,而表名類(lèi)似的只有QRTZ_JOB_LISTENERS,這個(gè)表結(jié)構(gòu)定義信息說(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)題的突破口。

  怎么驗(yà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)題比我們想象的還要復(fù)雜些，表QRTZ_JOB_DETAILS和QRTZ_JOB_LISTENERS以前就存在,而這次的部署變更，開(kāi)發(fā)同學(xué)只是提交了QRTZ_JOB_DETAILS的變更。

模擬復(fù)現(xiàn)問(wèn)題

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

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

> 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)生成一些輔助腳本內(nèi)容，在腳本執(zhí)行前會(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)題就很容易復(fù)現(xià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)

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

到此，相信大家對(duì)“MySQL無(wú)法創(chuàng)建表的原因”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢(xún)，關(guān)注我們，繼續(xù)學(xué)習(xí)！