Symfony World blog is not maintained anymore. Check new sys.exit() programming blog.

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