Notes
Building simplicidade.org: notes, projects, and occasional rants
email github facebook twitter flickr vimeo

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 cls_ev_items_fk_evaluation_id constraint.

The best solution I could come up with is a temporary index on evaluation_id. You are then free to mess with the report index.

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.