Rails, ActiveRecord, MySQL, GUIDs and the rename_column bug

Since I wasted over 4 hours of my life today working my way through this problem I feel the need to share.

Since it seems to be the in thing in the Web 2.0 space, just to be cool, we use GUIDs to identify different objects in our URLs at Vquence. For example my randomly created vquence on on Rails has a GUID of

cDuIhGWb8r3lDxaby-aaea

Andy Singleton has written a rails plugin called funnily enough guid. This allows you to do the following in your model.

class Vquence < ActiveRecord::Base
  usesguid :column => 'guid'
end

Once you do this you will automatically get GUID looking identifiers in the db and your application. The guid column in the DB gets mapped to Vquence.id so you can do things like

Vquence.find('cDuIhGWb8r3lDxaby-aaea');

We used to use Sphinx as our search index, we now use Lucene. Sphinx requires that you have an integer id for each document in your index. This is to make your SQL queries much faster. The dumb way to create your index is to use queries like the following.

SELECT * FROM videos LIMIT 0,10000
SELECT * FROM videos LIMIT 10000,10000
...
SELECT * FROM videos LIMIT 990000,10000

I know this as its what we originally used with Lucene. This works fine until you reach about 1,000,000 rows. The problem is that since there is no implicit ordering or range in the above query it means that for the final query MySQL needs to workout what the first 1,000,000 rows are and then return you the last 10,000.

A much better way to do it is the following

SELECT * FROM videos WHERE integer_id >= 1 and integer_id < = 10000
SELECT * FROM videos WHERE integer_id >= 10001 and integer_id < = 20000
...
SELECT * FROM videos WHERE integer_id >= 990000 and integer_id < = 1000000

This is fast as long as integer_id is indexed.

So to accommodate this in Rails we began using migrations like the following.

class Videos < ActiveRecord::Migration
  def self.up
    create_table :videos do |t|
      t.column :uuid, :string, :limit =>22, :null => false
      ...

      t.timestamps
    end
    add_index :videos, :uuid, :unique => true
    rename_column :videos, :id, :integer_id
  end

  def self.down
    drop_table :videos
  end
end

This was all done months ago and the repercussions didn’t rear their ugly head until today. Previously everything in the videos table had been created by our external crawler and Rails never needed to insert into the table. Today I wrote some code that inserted into the videos table and everything broke horribly.

The problem is that ActiveRecord can still see the integer_id field and tries to insert a 0 value into it. It isn’t clever enough to realise that it is an auto increment field and to leave it alone. After some help from bitsweat on #RoR I implemented a dirty hack to hide the integer_id column from ActiveRecord. Thanks to Ruby overriding the ActiveRecord internals is really easy and I added the following to our guid plugin.

  # HACK (JF) - This is too evil to even blog about
  # When we use guid as a primary key we usually rename the original 'id'
  # field to 'integer_id'. We need to hide this from rails so it doesn't
  # mess with it. WARNING: This means once you use usesguid anywhere you can
  # never access a column in any table anywhere called 'integer_id'

class ActiveRecord::Base
  private
    alias :original_attributes_with_quotes :attributes_with_quotes

    def attributes_with_quotes(include_primary_key = true, include_readonly_attributes = true)
      quoted = original_attributes_with_quotes(include_primary_key = true, include_readonly_attributes = true)
      quoted.delete('integer_id')
      quoted
    end
end

So this worked like a charm and after 4 hours I thought my pain was over, but then I tried to add second row to my test database. This resulted in the following.

 Mysql::Error: Duplicate entry '0' for key 1: INSERT INTO `videos` (`updated_at`, `sort_order`, `guid`, `description`,
 `user_id`, `created_at`) VALUES('2008-01-11 16:45:05', NULL, 'bcOMPqWaGr3k5CabxfFyeK', '', 5, '2008-01-11 16:44:28');

I ran the same SQL with MySQL client and got the same error. I then looked at the table and saw the following

mysql> show columns from moo;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| integer_id | int(11)     | NO   | PRI | 0       |       |
| guid       | varchar(22) | NO   | UNI |         |       |
+------------+-------------+------+-----+---------+-------+

What I expected to see was

mysql> show columns from moo;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| integer_id | int(11)     | NO   | PRI | NULL    | auto_increment |
| guid       | varchar(22) | NO   | UNI |         |                |
+------------+-------------+------+-----+---------+----------------+

The difference is that when the column was renamed it seems to have lost its auto increment and NOT NULL properties. Some investigation showed that the SQL being used to rename the column was

ALTER TABLE `videos` CHANGE `id` `integer_id` int(11)

when it should be

ALTER TABLE `videos` CHANGE `id` `integer_id` int(11) NOT NULL AUTO_INCREMENT

It seems that this is already filled as a bug on the rails site, including a patch.

Funnily enough that bug is owned by bitsweat. It seems he’s managed to help me out twice in one day 🙂 It doesn’t seem that it made it into Rails 2.0 though so until then be careful about renaming columns using migrations.