My rails migrations won't run, and I can't deploy my rails app. How can I start over?

Question

At some point in my rails development I started making database changes (e.g. dropping or altering columns/tables) without using rails migrations. So now I get errors when I try to deploy my rails app from scratch.

blaine@blaine-laptop ~/tmp/rbjacolyte $ rake db:migrate
(in /home/blaine/tmp/rbjacolyte)
==  AddHashToTrack: migrating =================================================
-- add_column(:tracks, :hash, :string)
rake aborted!
An error has occurred, all later migrations canceled:

Mysql::Error: Table 'jacolyte_dev_tmp.tracks' doesn't exist: ALTER TABLE `tracks` ADD `hash` varchar(255)

(See full trace by running task with --trace)

How can I sync my production and development environments with migrations after I've mucked it up by using raw SQL? I want to deploy my rails application without database errors, and I don't want to start from scratch.

---

The data in the production and development environments match, but the migrations fail. I want a way to 'start from scratch.'

Could I simply delete all of the migrations that I have, and then just start using migrations from now on?

Answer 1

If the existing production data is compatible with the development database schema, then I would:

1. Dump the production data to a file using a program such as mysqldump
2. Drop the production database
3. Recreate the production database
4. Run the migrations against the production database, specifying VERSION=0
5. Import the production data from the file created at step one

If the schemas aren't compatible then you might be able to follow this process but you'll have to edit the SQL in the file created in the first step to take account of the schema differences.

Answer 2

Maybe you could just get rid of all your current migrations, and use rake db:schema:dump to create a new schema.rb file, and manually edit your production database to reflect the changes you've made so far?

Answer 3

I like Veeti's suggestion, with a modification: rake db:schema:dump, then move that file to your development machine. Flatten your Rails migrations so far (see this SO thread on that), get rid of most of your migrations, and re-work your migrations to work, given your new schema.

Get this working on your dev machine, commit and deploy.

Answer 4

The shortcut way: manually add an entry to schema_migrations for a timestamp that represents a baseline. You can add migrations after that and as long as they don't make any bad assumptions about the db schema they should be able to run just fine. You won't be able to migrate backwards, but that's not a huge problem.

The bigger problem is that you won't be able to make a DB from scratch, which gets to be a pain longer term.

The fix for that is to delete all your existing migrations and create a new one that creates the existing schema. Manually delete everything from the schema_migrations table and put in an entry for this one new migration. After that, you can create new migrations that build on this new baseline and they should apply just fine. You should be able to bootstrap new databases in the normal fashion.

As long as your direct SQL is contained in Rails migrations, there's no problem with using it. Just make sure you implement both the #up and #down methods and you should be good. We've actually taken to using raw SQL as a best practice to avoid problems when models are changed later on. Something like

Foo.create(:name => 'bar')

seems innocuous, until the User model is modified to have

validates_presence_of :baz

At which point the new migration will run against an existing database, but that earlier migration that created the table and added the dummy entry will fail because User fails validation. Just using

execute("insert into foos (name) values ('bar')")

will work fine as long as the later migrations properly populate any new columns they add.