Decoding 1005 on MySQL
If you’re using referential integrity on MySQL, you can easily run into this error with the InnoDB engine. It frequently seems to occur with an ALTER TABLE statement. It can mean many things but typically it means the data types don’t match between the foreign key and primary key column. Likewise, it can mean one of the column data types disagrees in a multiple-column foreign to multiple-column primary key constraint.
The error by itself isn’t very handy. This is a sample:
ERROR 1005 (HY000): Can't create table 'sampledb.#sql-4a0_2' (errno: 150) |
You can try SHOW WARNINGS but you’ll discover more about the error by running the following command as the root superuser:
mysql> SHOW engine innodb STATUS; |
It returns a dump of the InnoDB’s activity. You can see it by unfolding the complete log, if you’re interested in the details. The significant part of the log to solve this type of problem is:
------------------------ LATEST FOREIGN KEY ERROR ------------------------ 100130 17:16:57 Error IN FOREIGN KEY CONSTRAINT OF TABLE sampledb/#sql-4a0_2: FOREIGN KEY(member_type) REFERENCES common_lookup(common_lookup_id): Cannot find an INDEX IN the referenced TABLE WHERE the referenced COLUMNS appear AS the FIRST COLUMNS, OR COLUMN types IN the TABLE AND the referenced TABLE do NOT MATCH FOR CONSTRAINT. |
While this is more information, it isn’t necessarily enough to solve the problem when you’re new to MySQL. The first place you should look is whether the data types agree between the foreign key and primary key columns.
The most common variation that I’ve run into is where the primary key column uses a int unsigned data type and the foreign key column uses an int data type. It’s quite nice that the InnoDB Engine stops this cold. Naturally, you fix it by changing the foreign key data type to match the int unsigned data type.
Complete Log File ↓
If you want to see what the complete dump of the InnoDB status is, click on the title above.
===================================== 100130 17:34:04 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 0 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 11, signal count 11 Mutex spin waits 0, rounds 80, OS waits 2 RW-shared spins 18, OS waits 9; RW-excl spins 1, OS waits 0 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 100130 17:16:57 Error in foreign key constraint of table sampledb/#sql-4a0_2: FOREIGN KEY(member_type) REFERENCES common_lookup(common_lookup_id): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with >= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html for correct foreign key definition. ------------ TRANSACTIONS ------------ Trx id counter 0 12958 Purge done for trx's n:o < 0 12956 undo n:o < 0 0 History list length 7 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 3924 MySQL thread id 13, query id 800 localhost 127.0.0.1 root show engine innodb status ---TRANSACTION 0 12957, not started, OS thread id 3276 MySQL thread id 2, query id 797 localhost 127.0.0.1 student -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 136 OS file reads, 1112 OS file writes, 704 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 195193, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 9122680 Log flushed up to 0 9122680 Last checkpoint at 0 9122680 0 pending log writes, 0 pending chkp writes 678 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 58341818; in additional pool allocated 1481088 Dictionary memory allocated 78248 Buffer pool size 3008 Free buffers 2879 Database pages 128 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 126, created 2, written 425 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread id 3408, state: waiting for server activity Number of rows inserted 454, updated 6, deleted 0, read 6026 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ |
Subscribe via RSS