Can't remove indexed column, ERROR 1025 (HY000): Error on rename of .. to .. (errno: 150)

Question

Hi guys,

I am having trouble removing a column which is indexed. I dont really understand what is happening as I usually have no problem deleting indexes. When I try to delete the column or index I get the following error

ERROR 1025 (HY000): Error on rename of './db-name/#sql-135e_9ee6d2' to './db-name/table-name' (errno: 150)

If I create a new index on the same column I can remove the old index but not the new one (I guess its just checking if an index is available). Have you guy ever encountered this problem if so any suggestion how I can solve this?

engine: innoDB mysql version: Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (x86_64) using readline 5.0

Thanks for your help, if you need any more information please let me know.

Answer 1

You have a foreign key relation on that index, so InnoDB prevents removing that column to maintain consistency. Remove any foreign keys first that reference that column.

This happens because ALTER TABLE really works by making a copy of the table, then renaming to move the old table out of the way and move the new table into its place. It is certainly one of the less meaningful error messages I’ve seen in MySQL.

http://www.xaprb.com/blog/2006/08/22/mysqls-error-1025-explained/