MySQL ERROR 1005 (HY000): Can’t create table ‘schema_name.table_name’ (errno: 150)

今天在按照書上的例子學習Yii的時候發現在建立一個有外鍵的MySQL表的時候總是報以下錯誤

ERROR 1005 (HY000): Can't create table '<schema>.<table>' (errno: 150)

但是僅僅從這個錯誤訊息根本得不到任何對排查錯誤有意義的資訊，找了一些文章大多數是說外鍵表和原表裡聲明的欄位類型不一致，但是都沒有給出怎麼查出是這個錯誤的，後來發現原來可以通過運行"SHOW ENGINE INNODB STATUS;"命令，然後在命令結果中的“LATEST FOREIGN KEY ERROR”部分得到具體的錯誤訊息。如下：

...<br />mysql> SHOW ENGINE INNODB STATUS\G<br />*************************** 1. row ***************************<br /> Type: InnoDB<br /> Name:<br />Status:<br />=====================================<br />110920 23:26:39 INNODB MONITOR OUTPUT<br />=====================================<br />Per second averages calculated from the last 19 seconds<br />----------<br />SEMAPHORES<br />----------<br />OS WAIT ARRAY INFO: reservation count 46, signal count 46<br />Mutex spin waits 0, rounds 260, OS waits 13<br />RW-shared spins 58, OS waits 29; RW-excl spins 5, OS waits 4<br />------------------------<br />LATEST FOREIGN KEY ERROR<br />------------------------<br />110920 23:26:33 Error in foreign key constraint of table trackstar_test/tbl_project_user_role:<br />foreign key (`project_id`) references `tbl_project` (`id`) on delete cascade on update cascade<br />) engine InnoDB:<br />Cannot resolve table name close to:<br /> (`id`) on delete cascade on update cascade<br />) engine InnoDB<br />------------<br />TRANSACTIONS<br />------------<br />Trx id counter 0 6375<br />Purge done for trx's n:o < 0 6353 undo n:o < 0 0<br />History list length 13<br />LIST OF TRANSACTIONS FOR EACH SESSION:<br />---TRANSACTION 0 0, not started, process no 755, OS thread id 2991618928<br />MySQL thread id 42, query id 383 localhost root<br />SHOW ENGINE INNODB STATUS<br />--------<br />FILE I/O<br />--------<br />I/O thread 0 state: waiting for i/o request (insert buffer thread)<br />I/O thread 1 state: waiting for i/o request (log thread)<br />I/O thread 2 state: waiting for i/o request (read thread)<br />I/O thread 3 state: waiting for i/o request (write thread)<br />Pending normal aio reads: 0, aio writes: 0,<br /> ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0<br />Pending flushes (fsync) log: 0; buffer pool: 0<br />36 OS file reads, 281 OS file writes, 177 OS fsyncs<br />0.00 reads/s, 0 avg bytes/read, 0.05 writes/s, 0.05 fsyncs/s<br />-------------------------------------<br />INSERT BUFFER AND ADAPTIVE HASH INDEX<br />-------------------------------------<br />Ibuf: size 1, free list len 0, seg size 2,<br />0 inserts, 0 merged recs, 0 merges<br />Hash table size 34679, node heap has 1 buffer(s)<br />0.00 hash searches/s, 1.16 non-hash searches/s<br />---<br />LOG<br />---<br />Log sequence number 0 1774918<br />Log flushed up to 0 1774918<br />Last checkpoint at 0 1772796<br />0 pending log writes, 0 pending chkp writes<br />128 log i/o's done, 0.05 log i/o's/second<br />----------------------<br />BUFFER POOL AND MEMORY<br />----------------------<br />Total memory allocated 17596386; in additional pool allocated 960512<br />Dictionary memory allocated 58096<br />Buffer pool size 512<br />Free buffers 468<br />Database pages 43<br />Modified db pages 10<br />Pending reads 0<br />Pending writes: LRU 0, flush list 0, single page 0<br />Pages read 35, created 8, written 296<br />0.00 reads/s, 0.00 creates/s, 0.00 writes/s<br />Buffer pool hit rate 1000 / 1000<br />--------------<br />ROW OPERATIONS<br />--------------<br />0 queries inside InnoDB, 0 queries in queue<br />1 read views open inside InnoDB<br />Main thread process no. 755, id 2955570032, state: sleeping<br />Number of rows inserted 60, updated 0, deleted 0, read 159<br />0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s<br />----------------------------<br />END OF INNODB MONITOR OUTPUT<br />============================<br />