Duplicate column name error after aborting alter

Question

I created a Rails database migration to add a new column to a table in my database, but after I noticed it was taking much longer than expected (15 minutes plus), I manually killed the ruby process running the migration. Then I realized that the runtime wasn't out of the ordinary because of the amount of data in the table, so I tried started it again. Now I get the error:

Mysql::Error: Duplicate column name 'new_column': ALTER TABLE `table_name` ADD `new_column` varchar(255)

However, when I manually go into MySQL, desc table_name shows that the column doesn't exist, and trying to drop it manually (alter table table_name drop new_column) tells me:

ERROR 1091 (42000): Can't DROP 'new_column'; check that column/key exists

It looks like aborting the initial add process caused things to get into an inconsistent state. What do I need to do to resolve this?

Edit: I tried manually adding (copying exactly the DDL from the error above), then dropping the column at the MySQL prompt, and both worked fine, but rake db:migrate continues to give me the error above.

Answer 1

you can try this go to this path

/var/lib/mysql

and take a backup of your database (as a folder)

then go inside the database folder and try to delete the table file

this maybe work

Answer 2

It looks like I was accidentally running the manual MySQL commands against the wrong database (I wasn't switching to the "development" version of the database). Using the correct database, I was able to manually remove the offending column.