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

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?

Solution 1:

You usually get this error if your tables use the InnoDB engine. In that case you would have to drop the foreign key, and then do the alter table and drop the column.

But the tricky part is that you can't drop the foreign key using the column name, but instead you would have to find the name used to index it. To find that, issue the following select:

SHOW CREATE TABLE region;

This should show you the name of the index, something like this:

CONSTRAINT region_ibfk_1 FOREIGN KEY (country_id) REFERENCES country (id) ON DELETE NO ACTION ON UPDATE NO ACTION

Now simply issue an:

alter table region drop foreign key region_ibfk_1;

And finally an:

alter table region drop column country_id;

And you are good to go!

Solution 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 ENGINE INNODB STATUS and look for the LATEST FOREIGN KEY ERROR section it contains details about what is wrong.

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

Solution 3:

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 :)