MySQL errno 150 - ERROR 1025
I get the 150 errno a lot when upgrading schemas. Usually this is a indication that the action I was trying to perform is not possible given the current foreign key constraints in place.
Last night I got a variation on the theme:
ERROR 1025 (HY000): Error on rename of './e3/#sql-17f3_f894' to './e3/cls_ev_items' (errno: 150)
The table was this:
CREATE TABLE `cls_ev_items` ( `id` int(11) NOT NULL auto_increment, `evaluation_id` int(11) NOT NULL, `criteria_id` int(11) NOT NULL, `value` varchar(50) default NULL, `modified_at` datetime NOT NULL, `rank` int(11) NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `report` (`evaluation_id`,`criteria_id`), KEY `criteria_id` (`criteria_id`), CONSTRAINT `cls_ev_items_fk_evaluation_id` FOREIGN KEY (`evaluation_id`) REFERENCES `cls_evaluations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `cls_ev_items_fk_criteria_id` FOREIGN KEY (`criteria_id`) REFERENCES `cls_ev_criteria` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8
I was trying to drop the
report index to replace it with a new one.
ALTER TABLE cls_ev_items DROP INDEX `report`
The error message is not clear enough, but in this case, the problem is that each
CONSTRAINT requires an index on the foreign key field. If MySQL allowed the removal of the
report index, he had no way to efficiently check the
The best solution I could come up with is a temporary index on
evaluation_id. You are then free to mess with the
After you finish, if the new
report index begins with the
evaluation_id field, you can drop the temporary index. And everything is back to normal.
It clear the MySQL checks this chaining of indexes and constraints, so I hope to see better error messages in the future.
Update: the error log gives a bit more info, btw:
080326 10:51:32 InnoDB: Error: in ALTER TABLE `e3/cls_ev_items` InnoDB: has or is referenced in foreign key constraints InnoDB: which are not compatible with the new table definition.
So in case of a 150 error, check the error log for better pointers.
Update 2: as pointed out by NiN in the comments, run
SHOW INNODB STATUS\G (the
\G will make the report easier to read) and look for a section labeled
LATEST FOREIGN KEY ERROR. The message is pretty good there.