Using 64 Bit Primary Keys With Migrations

• Tweet
• 

        I've got an application which may need more IDs than the standard 32 bit ones typically provide.  MySQL has a BIGINT variable type, but it is a little tricky to convince Rails and Migrations to use it.  The instructions are for MySQL, but should be easily transferable to other DBs.

1.  Monkeypatch ActiveRecord::ConnectionAdapters::MysqlAdapter#native_database_types by appending it to config/environment.rb and add two new types.  I've named them int64 and int64_pk

class ActiveRecord::ConnectionAdapters::MysqlAdapter
  def native_database_types #:nodoc:
    {
      :primary_key => "int(11) DEFAULT NULL auto_increment PRIMARY KEY",
      :int64_pk    => "bigint DEFAULT NULL auto_increment PRIMARY KEY",
      :int64       => { :name => "bigint" },
      :string      => { :name => "varchar", :limit => 255 },
      :text        => { :name => "text" },
      :integer     => { :name => "int", :limit => 11 },
      :float       => { :name => "float" },
      :decimal     => { :name => "decimal" },
      :datetime    => { :name => "datetime" },
      :timestamp   => { :name => "datetime" },
      :time        => { :name => "time" },
      :date        => { :name => "date" },
      :binary      => { :name => "blob" },
      :boolean     => { :name => "tinyint", :limit => 1 },
    }
  end
end

2.  In the table creation migration, create the table WITHOUT a primary key column, and then add the column manually, referencing int64_pk above:

create_table :slices, :id => false do |t|
       t.column :cheese_id, :integer
       t.column :plant, :string
       t.column :date, :datetime
     end
    add_column :events, :id, :int64_pk
end

3.  In tables referencing this one, mark the foreign_id fields as int64:

create_table :fondues do |t|
      t.column :cheese_id, :int64
      t.column :party_id, :integer
      t.column :kirsch_id, :integer
end

4.  It turns out that the c-based mysql bindings do NOT do let you set primary keys via the sequence because it coerces the
insert_id into 32 bits at mysql.c:532.  There are two way to fix this:

a.  Use the pure-ruby gem (ie, remove the c-gem)

b.  Patch and rebuild the c-gem by changing line 352 (in version 2.7):

   return INT2NUM(mysql_insert_id(GetHandler(obj)));

Extending this to use LL2NUM solves the issue:

   return LL2NUM(mysql_insert_id(GetHandler(obj)));

5.  One last thing, in environment.rb:

  # Use SQL instead of Active Record's schema dumper when creating the test database.
  # This is necessary if your schema can't be completely dumped by the schema dumper,
  # like if you have constraints or database-specific column types
  config.active_record.schema_format = :sql

We need to deal with sql, rather than ruby, schemas.

----------------------

Note that I've decided that ONLY the cheese table needs 64bit IDs -- other references are still integers.  If you wanted ALL primary keys to be 64 bits, you could have the monkeypatch look like:

class ActiveRecord::ConnectionAdapters::MysqlAdapter
  def native_database_types #:nodoc:
    {
      :primary_key    => "bigint DEFAULT NULL auto_increment PRIMARY KEY",
.
.
.

Tags:
• key
• Migrations
• MySQL
• primary
• Rails
• Rake

Comments

Jeremy Weiskotten replied on Wed, 2007/04/25 - 4:45pm

There's a plugin called mysql_bigint that takes care of this. See http://svn.northpub.com/plugins/mysql_bigint/. The plugin also allows use of MySQL's smaller integer types.

• Login or register to post comments

Daniel Insley replied on Tue, 2007/09/11 - 7:51pm

You can also pass native column types into the column call of your migrations, like the following: create_table 'list', :force => true do |t| t.column :position, 'integer unsigned' end I had a similar problem awhile ago where we had to set the id's of a lot of our tables to use unsigned integers, including the primary keys. I wrote a plugin and threw it on my blog, more technical details are available here: http://www.danielinsley.com/articles/2007/06/10/rails-plugin-primary_key_fix It also fixes the schema dumper to report the correct column type as well.

• Login or register to post comments