Rails error: Index name '...' on table '...' already exists while running rails db:migrate

Question

So I'm working with a colleague who added some additional migration files and per normal procedure once I pulled their version I ran rails db:migrate. I end up getting the following errors:

 Index name 'index_authorizations_on_user_id' on table 'authorizations' already exists

 ArgumentError: Index name 'index_authorizations_on_user_id' on table 'authorizations' already exists

So I went and checked the schema and the table is already present. So why is it failing? Typically in the past it only generates new table entries/updates when doing a migration so why isn't it just ignoring it?

I've tried doing a rollback and get: This migration uses remove_columns, which is not automatically reversible.

I've tried doing bin/rails db:migrate RAILS_ENV=development and I get the same errors.

I've done a db:reset, db:drop, and it all comes back to an issue with pending migrations that I cannot get to run. What am I doing wrong?

They added the following migration: 20171024074328_create_authorizations.rb

 class CreateAuthorizations < ActiveRecord::Migration[5.1]
  def change
    create_table :authorizations do |t|
      t.string :provider
      t.string :uid
      t.references :user, foreign_key: true

      t.timestamps

      add_index :authorizations, :user_id
      add_index :authorizations, [:provider, :uid], unique: true
    end
  end

end

Gardal

Would you show the migration? – Ijssel

Added the migration file in question – Gardal

Dropping the database should delete all pending migrations. Is there an earlier migration that creates the table and index? – Internode

@TomAranda Dropping the database is an absolute last resort and often causes more problems (such as data loss or doing nothing at all if you backup-drop-restore to avoid losing all your data) that it solves. – Immodest

@muistooshort, A fair point. I did not mean to recommend that course of action, and I do not recommend that course of action. However, the user said he already tried that, so I was puzzled why it didn't work. – Internode

Answer 1

This:

t.references :user, foreign_key: true

adds an index on authorizations.user_id for you. If you check the references documentation it will point you at add_reference and that says:

:index
Add an appropriate index. Defaults to true. [...]

So index: true is the default when you call t.references :user and that creates the same index that add_index :authorizations, :user_id creates.

Immodest

Tried to resolve this by removing add_index :authorizations, :user_id add_index :authorizations, [:provider, :uid], unique: true – Gardal

So you removed add_index :authorizations, :user_id and re-ran your migrations? – Immodest

It actually errors with this instead: PG::DuplicateTable: ERROR: relation "authorizations" already exists : CREATE TABLE "authorizations" ("id" bigserial primary key, "provider" character varying, "uid" character varying, "user_id" bigint, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL, CONSTRAINT "fk_rails_4ecef5b8c5" – Gardal

Did you create the table by hand somewhere? Something else out of the ordinary? Each migration should run inside a transaction and PostgreSQL supports DDL transactions (i.e. it can rollback schema changes) so the whole migration should run or not run. That PG::DuplicateTable exception is telling you that something created the authorizations table before you tried to run the updated migration. – Immodest

Answer 2

So the only thing that I've found that "worked" was to actually delete the migration files then run rails db:migrate. Didn't catch on anything, no errors.

Not a fan of the fact that this worked.

Gardal

If this is the only thing that worked for you, then there is an issue with your migrations. In the above comment you stated that you received a different error - had you rolled back the migration, removed the relevant lines, and then rerun it? The correct solution here is to fix the migrations - deleting the files will do nothing for someone who subsequently comes along and checks them out of version control. – Terrilynterrine

So I was able to get it to work after messing up in deployment to Heroku. I recreated the merge files without add_index and it works. – Gardal

Answer 3

Check if it's a table or a changed table, most times you need to drop the table or delete the column then run the migration again and it'll be good to go

Neelon

Answer 4

The solution for me was to drop the indexed column that had a unique constraint and then reindex

Oecology

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Micrococcus