What does mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) mean?

Question

I tried this in mysql:

mysql> alter table region drop column country_id;

And got this:

ERROR 1025 (HY000): Error on rename of './product/#sql-14ae_81' to
'./product/region' (errno: 150)

Any ideas? Foreign key stuff?

Answer 1

I'd guess foreign key constraint problem. Is country_id used as a foreign key in another table?

I'm not DB guru but I think I solved a problem like this (where there was a fk constraint) by removing the fk, doing my alter table stuff and then redoing the fk stuff.

I'll be interested to hear what the outcome is - sometime mysql is pretty cryptic.

Answer 2

It is indeed a foreign key error, you can find out using perror:

shell$ perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

To find out more details about what failed, you can use SHOW INNODB STATUS and look for the LATEST FOREIGN KEY ERROR section which should contain details about what is wrong.

In your case, it is most likely cause something is referencing the country_id column.

Answer 3

I have an instance of this error message despite there being no foreign key statements at all... very confusing.

Answer 4

I'm getting this error when trying to drop the foreign key itself

Answer 5

You can get also get this error trying to drop a non-existing foreign key. So when dropping foreign keys, always make sure they actually exist.

If the foreign key does exist, and you are still getting this error try the following:

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

// Drop the foreign key here!

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

This always does the trick for me :)

Answer 6

Take a look in error file for your mysql database. According to Bug #26305 my sql do not give you the cause. This bug exists since MySQL 4.1 ;-)