Tuesday, February 03, 2009

How Rails/Migrations Almost Cost Me My Job (*)

(*) Well, yeah, it’s my own damned fault, but still!

We are not a Rails shop, we’re a typical LAMP house.  But we’re trying to do the right thing and bring more Rails into the mix, with the intention of rearchitecting completely that way.  So I had a project that I was helping somebody launch – just a simple maintenance tool, which would live on an internal machine but point to the production database.  Up until this point the project has been pure Rails, developed with sqlite3 and staged with mysql, all moving around with migrations. There’s really only a single table, it just so happens that once it gets to production, this table will live in a legacy database of 50 or so tables that were not produced by Rails migrations.

So, trying to do everything right, we create a special database user just for this case, who can remotely access the production boxes, just from this IP.  We even limit his rights to nothing but create, so I can run the migration.

Migration errors out – we did not give him SELECT privilege, so it cannot execute “show fields”.  Darnit.  I go in, give him SELECT, and run it again.

Now it fails telling me the table already exists.  I go in as root, drop the table, and try again.

Now it fails wondering where the table went.

A whole bunch of stuff happened here, with me trying to figure out how to get free of the “SQL does not jive with what migrations thinks happened” trap.  In the process I grant my user drop privileges, thinking that perhaps if I unwind the migration, dropping the table, that I will then get everything back in sync.  Still does not work.

At this point I find the db:migrate:reset task, which according to the doc “Resets your database using your migrations” something something, it gets cut off.  This translates in my brain as “Gets my SQL and migrations back in line with each other”, so I run it.

And it promptly drops my entire production database.

I am guessing, now that I have a chance to breathe, that there’s some assumption in there that basically says, “The whole database is controlled with migrations, therefore we will drop it all and start over, executing each migration from the beginning.”  Small problem for those of us who only wanted to use migrations to add new tables, not muck with the legacy stuff!  I thought at best it would drop the one table I had asked it to create.

Like I said, it was my mistake – that user should not have had drop privileges, and I should not have been effing around applying commands against production data that I had never used before.  Luckily it is not the end of the world and we’ve resurrected just about everything that was lost.

I am still confused, however, about how I would have properly solved my problem – what do you do when the state of the database is out of line with what the migrations think should be there?  Does the task I was hoping for, that brings the two in line, exist?  Maybe I could make hand modifications to bring the database up to a certain state and then somehow single to Rails, “Ok, assume you have executed migrations up to version X, whether or not you actually did.” 


steveo said...

Rails is never the "right thing". Only rails needs to make such large db changes so that it can work. You should have been preemptively fired when you suggested rails rather than when it broke everything.

Duane said...

Don't fear change, SteveO. Change is good. I'm sure your Java skills will continue to serve you for a long time to come. Heck, I'm pretty sure there's still the occasional COBOL programmer out there too.

jason said...

RoR is the way to go steveo.

This is funny stuff... sorry it came at your expense, Duane. Hope you had a good backup.