Duplicate column name error when running migration

Question

Whenever I run a migration in my Rails app, I get an error from SQLite3:

SQLite3::SQLException: duplicate column name: photo_file_name: ALTER TABLE "users" ADD "photo_file_name" varchar(255)

I already have a "Add Photo to User" migration. Here it is:

class AddAttachmentPhotoToUsers < ActiveRecord::Migration
   def self.up
     change_table :users do |t|
     t.has_attached_file :photo
    end
   end

  def self.down
   drop_attached_file :users, :photo
  end
end

And here is the user migration:

class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t|
    t.string :name
    t.string :title
    t.string :department
    t.text :skills
    t.boolean :available

    t.timestamps
   end
 end
end

I'm a bit confused by it because it's telling me there is a duplicate column name "photo_file_name" but that I need to add it to the Users table? That doesn't make sense. Shouldn't I need to remove it?

Let me know if you need any other details about my app.

Clarkclarke

Answer 1

That happens if you migrations are not in sync with the database schema. This could happen if

• you modified the database schema "by hand"
• you changed a migration file being run
• migrations have not been updated in the schema_migrations table

If you are not relying on the data in the database, a rake db:reset would re-run all migrations from scratch. Otherwise you have to make the conflicting migration recognized as already-run by adding to the schema_migrations table.

See RailsGuides of migrations as well.

Roundhouse

Ok. I tried to reset the db and then I got this message: "You have 1 pending migrations: 20121105002856 AddAttachmentPhotoToUsers". It then asked me to run db:migrate and I received the same error as before. – Clarkclarke

The I bet you are adding the photo_file_name column in two migration files. Remove or modify one. Are you using Paperclip btw? – Roundhouse

I've added my migration files. There's no photo_file_name there. It's in the schema.rb file. Yes I am using paperclip. Should I try added photo_file_name to one of the migrations? – Clarkclarke

Another more brute force approach: Try removing the development database file and run rake db:create and rake db:migrate again. Disclaimer: You will lose you development database data. – Roundhouse

That's true if you change a migration file by hand, but sometimes you accidentally add a duplicate in migration file, then run, get the error, and then remove duplicate and then run again and all problems arise. So you are forced to delete migration file and add a new one. – Conners

mine was the third option, i changed the migration file being run. after run MIX_ENV=test mix ecto.reset and migrate it worked – Smoky

Answer 2

I've also solved this problem by logging into the heroku database, and then dropping only the offending column. I think this is a less-destructive solution.

Heighttopaper

Answer 3

• drop the development schema from your workbench
• run rails db:create db:migrate

Cretan