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

Recently I have been working on an existing database that was migrated from one server to another, for the most part things were going on at a very uneventful pace. Up until the point where i wanted to add another table to the database.

The new table was to be created using the following syntax:

CREATE TABLE IF NOT EXISTS rd_6(
	sid INT,
	position INT NOT NULL,
	value INT NOT NULL,
	INDEX (sid),
	FOREIGN KEY (sid)
	REFERENCES study(id)
	ON UPDATE CASCADE ON DELETE RESTRICT
)

This seemed like a fairly straight forward operation since the previous tables rd_1 to rd_5 had already been created, with identical syntax. But in this case the following error would be generated.

ERROR 1005 (HY000): Can't create table 'cnv.rd_6' (errno: 150)

With a quick google search I quickly realized that there wasn’t much helpful informaiton, except for references stating that this was an error generated when a FOREIGN KEY was specified to be UNIQUE. But in my case this was not the issue.

Further Google hunting led me to MacLochlainns Weblog which stated some helpful hints on how to check the status of the innodb engine using:

mysql < SHOW engine innodb STATUS;

This will dump a log of the InnoDB’s activity allowing me to see the most recent FOREIGN KEY ERROR.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
110111 9:42:54 Error in foreign key constraint of table cnv/rd_8:
FOREIGN KEY (sid)
REFERENCES study(id)
):
Cannot resolve table name close to:
(id)
)

Once again I can see that this is indeed a foreign key error, but seeing that it is trying to lookup the table (id) instead of study(id) seemed to be very strange.

To get a better look at this problem I quickly installed phpMyAdmin on my machine and connected to the database, where I could easily see where the problem was coming from.

phpMyAdmin Table Information

Now I could see that the tables when they were imported where setup to use the MyISAM engine while everything else used InnoDB, no idea how or when that happened. Now it’s time to switch the engine on all tables over to InnoDB, this can be done using the phpMyAdmin interface under the Operations tab of each table.

Or you can alter each table using this command:

ALTER TABLE ... ENGINE=InnoDB

ERROR 1005 (HY000): Can’t create table ‘<table>’ (errno: 150)

Categories