MySQL error on rename when dropping column with a constraint

a nasty MySQL bug

Recently I wanted to remove a column from phpmyadmin that is covered with a constraint. Impossible to do so, MySQL throws following error:

ERROR 1025 (HY000): Error on rename of './optiner_prestadmin/#sql-6bc_3e6' to './optiner_prestadmin/order_history' (errno: 150)

You may get above error when executing a query removing index, e.g.

ALTER TABLE order_history DROP INDEX user_id;

Fortunately, there is a solution, not very easy to find in the internet (I used comments from this article).

1. execute the following in MySQL:
   

   SHOW CREATE TABLE order_history;

   You shall get something like:
   

   order_history | CREATE TABLE `order_history` (
     `order_id` bigint(20) NOT NULL,
     `user_id` bigint(20) NOT NULL,
     `status_id` bigint(20) NOT NULL,
     `informed` tinyint(1) DEFAULT '0',
     `comment` text,
     `created_at` datetime NOT NULL,
     `created_by` bigint(20) NOT NULL DEFAULT '1',
     PRIMARY KEY (`id`),
     KEY `order_id_idx` (`order_id`),
     KEY `user_id_idx` (`user_id`),
     KEY `status_id_idx` (`status_id`),
     KEY `created_by_idx` (`created_by`),
     CONSTRAINT `order_history_created_by_sf_guard_user_id`
       FOREIGN KEY (`created_by`) REFERENCES `sf_guard_user` (`id`)
         ON DELETE CASCADE,
     CONSTRAINT `order_history_order_id_order_info_id`
       FOREIGN KEY (`order_id`) REFERENCES `order_info` (`id`),
     CONSTRAINT `order_history_status_id_order_status_id`
       FOREIGN KEY (`status_id`) REFERENCES `order_status` (`id`),
     CONSTRAINT `order_history_user_id_sf_guard_user_id`
       FOREIGN KEY (`user_id`) REFERENCES `sf_guard_user` (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=28305 DEFAULT CHARSET=utf8

2. we want to delete the whole column user_id (with all corresponding indices, constraints, etc.) - need to drop the FOREIGN KEY first:
   

   ALTER TABLE order_history
     DROP FOREIGN KEY `order_history_user_id_sf_guard_user_id`;

   you'll find the name of the foreign key in the last constraint clause.
   
3. finally, drop the column:
   

   ALTER TABLE `order_history` DROP `user_id`;

   and that's it!
   

   Query OK, 28065 rows affected (3,99 sec)
   Records: 28065  Duplicates: 0  Warnings: 0