gotcha: running `rake db:migrate:redo` and testing

So... rails *tells* me that rake db:test:prepare is deprecated, and that just running the specs will keep the db up-to-date... but it isn't always so.

If you jigger with a brand new migration and run a rake db:migate:redo - the test db can't deal with that by itself. You either have to run rake db:migate:redo RAILS_ENV=test or rake db:test:prepare to propagate those changes.

Gotcha: migrations with trailing commas

Trailing commas are bad m'kay?

I had a pretty standard migration, which included something like the following two lines:

    create_table :my_widgets do |t|
      t.string :name, :colour, :flavour
      t.integer :supplier_id
      t.integer :height, :width, :depth,    # note this extra comma...

      t.timestamps
    end

Looked fine at a casual glance, ran just fine - no hiccups. Been using it on dev for a week or so now... no problems... Only noticed an issue when I glanced at db/schema.rb

  create_table "service_report_stats", :force => true do |t|
    t.string   "name"
    t.string   "colour"
    t.string   "flavour"
    t.integer  "supplier_id"
    t.integer  "height"
    t.integer  "width"
    t.integer  "depth"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "#<ActiveRecord::ConnectionAdapters::TableDefinition:0xb5c35504>" # WTF???
  end

Something seriously wrong with that last "integer" column... mysql shows that yes, that is actually the column's real name:

+-----------------------------------------------------------------+----------+------+-----+---------+----------------+
| Field                                                           | Type     | Null | Key | Default | Extra          |
+-----------------------------------------------------------------+----------+------+-----+---------+----------------+
| id                                                              | int(11)  | NO   | PRI | NULL    | auto_increment |
| supplier_id                                                     | int(11)  | YES  |     | NULL    |                |
.... etc ...
| created_at                                                      | datetime | YES  |     | NULL    |                |
| updated_at                                                      | datetime | YES  |     | NULL    |                |
| #<ActiveRecord::ConnectionAdapters::TableDefinition:0xb5c35504> | int(11)  | YES  |     | NULL    |                |
+-----------------------------------------------------------------+----------+------+-----+---------+----------------+

Right now I have NFI how to actually remove the column... because trying:
alter table my_widgets drop column #<ActiveRecord::ConnectionAdapters::TableDefinition:0xb5c35504>;
Just results in: ERROR 1064 (42000): You have an error in your SQL syntax; , and it won't accept it if I put it in quotes either... so the column currently remains as is.

Luckily I was playing with a branch of the real system - so it's only my dev-box's code that has been mucked about... and I can change the migration before anybody else uses it... but this is seriously weird behaviour on Rails' part. Not a raised exception or even a raised eyebrow... it sailed right through and is continuing on regardless.

To actually fix it, I had to create a migration with the following:

    remove_column :my_widgets,  "#<ActiveRecord::ConnectionAdapters::TableDefinition:0xb5c35504>"

Seems the Rails itself can tell mysql about a weirdly-named column somehow. I'd still like to know the actual SQL involved...

Gotcha: db index name must be a string

Might just be Rails 2.3.8 - an exception is thrown whenever I use a symbol for an index name. I get:

rake aborted!
An error has occurred, all later migrations canceled:
undefined method `length' for :my_index_name

The code causing this would be something like:

add_index :widgets, [:colour, :weight], :name => :index_by_colour_weight

it’s solved by just using a string instead:

add_index :widgets, [:colour, :weight], :name => 'index_by_colour_weight'

Stored Procedure-fu

So, I've been tuning my db performance on our heaviest database call... and this involved adding a quickie stored procedure to make life a little simpler.

It's nothing complex, just does a really simple distance calculation (using pythagorus)... which we needed because most versions of MySql don't come fully-loaded with all those nice functions that you can find in the manual.

Now the problem with stored procedures, is how to add them to the db without too much hassle. I have heard there are gems out there - but they seem a little like overkill, and the ones I've seen have been "build it on the fly"... I just want to put it into the db and leave it there.

So what's the quickest and easiest way?

A migration:

class AddStoredProcedures < ActiveRecord::Migration
  def self.up
    # function distance(lat1,lng1,lat2,lng2)
    #   sqrt((lat2 - lat1)^2 + (lng2 - lng1)^2)
    # end
    execute <<EOS 
      CREATE FUNCTION Distance(lat1 FLOAT, lng1 FLOAT, lat2 FLOAT, lng2 FLOAT) 
      RETURNS FLOAT
      DETERMINISTIC
      BEGIN
        DECLARE dist FLOAT;
        SET dist = Sqrt(Pow((lat2 - lat1),2) + Pow((lng2 - lng1),2));
        RETURN dist;
      END
EOS
  end

  def self.down
    execute "DROP FUNCTION IF EXISTS `Distance`" 
  end
end

Down sides?

I'm told that using migrations for stored procedures has one major drawback - that if you want to change the procedure later then it's annoying to maintain - and can be the wrong version etc... In this case that's not a problem. Pythagorus isn't likely the change any time soon, so I'm unlikely to need to update it.

But there is one annoyance about putting it as a migration, and that's the fact that it doesn't show up as part of the schema... which means our Test environment can't find it. :P

This is a bit annoying, and I haven't found an elegant solution to the problem, but to get the test suite running, I have found a hack...

Re-run the migration:

class ActiveSupport::TestCase
  # ... lots of guff

  # Run stored procedures to add to the test db
  require File.expand_path(File.dirname(__FILE__) + "/../db/migrate/20100528190204_add_stored_procedures.rb")
  AddStoredProcedures.down # clear it out first, to be sure
  AddStoredProcedures.up
end

It's ugly, but it works. You may note I have to run the "down" then the "up" - this is because ActiveSupport ::TestCase actually gets loaded for each type of test-case you are running (eg once for Unit tests and again for Functional etc) and the "up" barfs if you try to create a procedure that has already been created in the db.

Otherwise, it runs and lets you get on with your work...

if anybody has a more elegant, but unheavy solution to the whole issue of Stored Procedures in test dbs... please do let me know.

Rails gotchas: Kamikaze data migrations

This is a pernicious little gotcha I've encountered a couple of times, generally by using somebody else's plugins. The problem with it is that it usually doesn't actually affect the developer - just anybody else that uses the system. This can lead to the developer refusing to believe that there is a problem.[1]

So what am I talking about here?

I'm talking about a certain type of data migration that explodes as you run it, sometimes even causing the database to get stuck in a nasty halfway-inbetween state where you can neither continue migrating up... or roll back to a previous stable state.

"But migrations are transactional" I hear you cry. Sure they are. That doesn't stop this particular error from occurring... and *still* leaving the db in a nasty stuck state - honest, just gimme a chance to explain why.

Picture this: You've been happily developing away building the next generation of your category-killer Web 2.0 Widget application when you suddenly realise that your widgets need to refer to pieces instead of parts. So, like a good agile developer you:

• Create a new Piece resource with all the bells and whistles.
• Create and run the migration that a) creates the pieces table b) data migrates widgets so they point at pieces instead of parts c) drops the parts table.
• Update all references in your code from pieces to parts.
• Remove all the part code from controllers/views etc.

All goes smoothly. rake test runs flawlessly and you check in your code. You continue on your merry way, adding more functionality and reflecting on your brilliance...

...until a user of your code emails you to say that your migration breaks and has left their db in some wierd state that doesn't let them migrate up or down.

The problem is that when you (the developer) ran the migration, you still had the Part class in app/models and the Widget class still had references like "has_one :part" in them. When your user downloads your system, however, it may be several changes later... and the system has no recollection of a Part class or how they relate to Widgets. So when you have a data migration such as:

   Widget.find_all.each { |w| make_piece_from_part(w.part) }

It won't know what a part is - or how you get one off your widget class. The nasty part is that often you will find this kind of data migration stuffed in the middle of the schema migrations thus:

def self.up
  create_table :pieces do |t|
     #... creates a table here
  end
  add_column :widgets, :piece_id, :integer, :default => nil
  Widget.find_all.each { |w| make_piece_from_part(w.part) }
  remove_column :widgets, :part_id
end
def self.up
  add_column :widgets, :part_id, :integer, :default => nil
  Widget.find_all.each { |w| make_part_from_piece(w.piece) }
  remove_column :widgets, :piece_id
  drop_table :pieces
end

I'm told that migrations are transactional - ie if it fails, then everything rolls back to the state before the migration... this doesn't seem to work when data migrations are involved. If the data part of the migration falls over, it stops halfway through the migration with a nasty backtrace, but doesn't perform any rollback. So at that point it's already added the pieces table and the piece_id column to the widgets table, but it hasn't removed the part_id column and the schema is still pointing at the migration number before this one. So if you try to run the migration again, it will fail on the first line, saying something like:

== MovePartsToPieces: migrating ============================================
-- create_table(:pieces)
rake aborted!
Mysql::Error: Table 'pieces' already exists: CREATE TABLE pieces (<piece-table insert statement here>) ENGINE=InnoDB

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

Unfortunately it also won't migrate you back. The bad data migration failed before it had a chance to update the current migration level - so migrating back gives you entirely unhelpful "finished in 0.000183" seconds message (or similar), without doing anything at all.

Recovering from the stuck state

Recovering is possible, but it takes some small amount of hacking in the migration. Basically you need to force Rails to run an empty migration so that it does nothing except to put the version number up. Then you can force it to run the bits of the down migration that cancel the bits that it did the first time you tried running the up migration. In detail:

1. Comment out every line of code in self.up
2. Comment out the appropriate lines in self.down that correspond with stuff that never got run when the migration fell over the first time - ie leave anything that cancels out actions that were *successfully* done when you did the up migration. in the example above, you'd comment out the downward data migration and the addition of the piece_id column - leaving only the removal of the part_id column and the drop table - which would leave you back in the state you started in.
3. Save the file
4. Now you can run rake db:migrate. This should should leave you with a database that at least has the right version number for you to be able to migrate back down again.
5. Now run the down migration with rake db:migrate VERSION=<one before this one>.
6. Now go fix your migration so this doesn't happen to anybody else.

Fixing this kind of data-migration nightmare is the only time that I recommend you go back and edit old migrations rather than creating a new one.

• [1]Ok, so the real reason I'm writing this post is partly so that I can point said unbelievers at it the next time they swear that nothing's wrong and that I should just quit fussing about.