MySQL無法創建表的原因

本篇內容主要講解“MySQL無法創建表的原因”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學習“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無法創建表的原因”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！