Rails migration appears completed, but rake still shows migration as pending

Question

I ran a simple rails migration on a large MySql2 db to add a column to a table:

class AddMiddleNameToPerson < ActiveRecord::Migration[5.0]
  def change
    add_column :person, :middle_name, :string
  end
end

I was disconnected from the server running the rails app during the migration. I then reconnected and checked the migration status with bundle exec rake db:migrate:status, which showed it as down:

down    20170424182410  Add middle name to person

I assume it was still running in the background. So I left it for some time, and eventually using the rails console I verified that person.middle_name was accessible on objects. However, db:migrate:status still shows the migration as down, and if I try to run db:migrate again I get an error:

Mysql2::Error: Duplicate column name 'middle_name'

So it seems that the new column is in the database, and accessible through ActiveRecord, but rake db:migrate:status finds the migration as down and rake db:migrate attempts to re-run it, unsuccessfully.

Answer 1

If this is a production database (or other database with important data) then do not rake db:reset as that will drop the database and you'll lose everything; also don't db:migrate:down as that will drop the middle_name column and you'll lose whatever middle names you already have.

First get a backup of the database or at least the table you're working with.

Second, connect to the database with the mysql CLI tool and say describe people;. The information in your question suggests that you will see the middle_name column in there but it doesn't hurt to make sure you're connecting to the right database. If middle_name isn't there then you're almost certainly connecting to the wrong database somewhere, if it is there then you just have a migration issue to clean up.

You say that the database connection was dropped before the migration finished. Migrations work in this sequence:

1. Run the migration to update the database.
2. Record the migration's version number in the schema_migrations table.
3. Regenerate db/schema.rb or db/structure.sql.

If 1 completes but the connection is lost then 2 never happens so the migration will have run but Rails won't know it.

If no other environments need the migration then you can simply delete the migration and rake db:schema:dump or rake db:structure:dump to get a fresh schema.rb or structure.sql. Migrations are just temporary bits of code to get you from A to B so deleting them after they've been run everywhere is fine (and even recommended), all that matters long term is your database's structure (which is in db/schema.rb or db/structure.sql).

If other environments need to run the migration then you can manually patch the schema_migrations table; connect to the database with the mysql CLI tool and say insert into schema_migrations (version) values ('20170424182410');. Rails will now know that the migration was run and future rake db:migrate calls will be happy. Then you'd want to refresh your schema.rb (with rake db:schema:dump) or structure.sql (with rake db:structure:dump).

---

You probably have a db/schema.rb file for tracking your database's structure (including the version numbers of the migrations that have been run). If you do then you'd use rake db:schema:dump to regenerate it. If you have db/structure.sql then you'd use rake db:structure:dump.