How do I solve "index unique_schema_migrations already exists" in Rails?

Question

Running rake db:migrate followed by rake test:units yields the following:

rake test:functionals
(in /projects/my_project)
rake aborted!
SQLite3::SQLException: index unique_schema_migrations already exists: CREATE UNIQUE INDEX "unique_schema_migrations" ON "ts_schema_migrations" ("version")

The relevant part of db/schema.rb is as follows:

create_table "ts_schema_migrations", :id => false, :force => true do |t|
  t.string "version", :null => false
end

add_index "ts_schema_migrations", ["version"], :name => "unique_schema_migrations", :unique => true

I'm not manually changing this index anywhere, and I'm using Rails' default SQLite3 adapter with a brand new database. (That is, running rm db/*sqlite3 before rake db:migrate doesn't help.)

Is the test:units task perhaps trying to re-load the schema? If so, why? Shouldn't it recognize the schema is already up to date?

Answer 1

Try to search if your schema.rb file does not contain other declarations that create an index with the same name: unique_schema_migrations

Answer 2

In your database.yml file are your environments setup up to connect to different databases for Development and Test?

IE:

development:
  adapter: sqlite3
  database: db/dev.sqlite3
  timeout: 5000

test:
  adapter: sqlite3
  database: db/test.sqlite3
  timeout: 5000

Answer 3

In SQLite, index name uniqueness is enforced at the database level. In MySQL, uniqueness is enforced only at the table level. That's why your migrations work in the latter and not the former: you have two indexes with the same name on different tables.

Rename the index, or find and rename the other unique_schema_migrations index, and your migrations should work.