MySQL無法創建表的原因

130次閱讀

共計 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) 有的同學可能在想是不是大小寫敏感導致的?

是不是涉及了什么相關的語法灰色地帶了，我在表名后面加了一個 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 記錄了所有操作的過程，這給我帶來很大的便利，這樣我就能看到每一步執行的過程中的一個基本情況了。當時做了什么嘗試，之前做過什么變更都一目了然。當然這個日志給了我一些很明確的信息，但是還沒有找到問題的原因所在。

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

當我看到日志里面無意檢查倒的信息時，不禁眼前一亮, 創建失敗的表是 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 網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！

正文完