Rails 5 Db Migration: How to Fix Activerecord::Concurrentmigrationerror

Rails 5 db migration: how to fix ActiveRecord::ConcurrentMigrationError

Advisory locking was added in Rails 5 to prevent unplanned concurrency errors during migration. The fix is to clear the DB lock that was left in place.

Review the locks by running this SQL against your DB:

SELECT DISTINCT age(now(), query_start) AS age, pg_stat_activity.pid,pg_locks.granted,pg_stat_activity.application_name,pg_stat_activity.backend_start, pg_stat_activity.xact_start, pg_stat_activity.state_change, pg_stat_activity.waiting, pg_stat_activity.state, pg_stat_activity.query_start, left(pg_stat_activity.query, 60)
FROM pg_stat_activity, pg_locks
WHERE pg_locks.pid = pg_stat_activity.pid

To clear a lock, run this SQL against your DB:

select pg_advisory_unlock({the pid of the lock you want to release})

How do I cancel a running migration?

Stop and then restart the database again. Then try rails db:migrate again.

If you are using posgresql installed with homebrew on Mac OSX the following stops/restarts the database:

pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start
pg_ctl -D /usr/local/var/postgres stop -s -m fast

Missing pg_locks and pg_stat_activity tables while trying to diagnose ActiveRecord::ConcurrentMigrationError

I never figured out where the two missing tables were, but I was able to remove the advisory_lock by simply running brew services stop postgresql followed by brew services start postgresql. Didn't have to drop my DB or anything. :-)

Rails Migration Error w/ Postgres when pushing to Heroku

From the fine manual:

[ALTER TABLE ... ALTER COLUMN ...]

The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.

There is no implicit conversion from varchar to int in PostgreSQL so it complains that column "number" cannot be cast to type integer and the ALTER TABLE fails. You need to tell PostgreSQL how to convert the old strings to numbers to match the new column type and that means that you need to get a USING clause into your ALTER TABLE. I don't know of any way to make Rails do that for you but you can do it by hand easily enough:

def up
  connection.execute(%q{
    alter table tweets
    alter column number
    type integer using cast(number as integer)
  })
end

You'll want to watch out for values that can't be cast to integers, PostgreSQL will let you know if there are problems and you'll have to fix them before the migration will succeed.

Your existing down-migration should be fine, converting integer to varchar should be handled automatically.

---

Related Topics