Rails 2.1 introduced in the MySQL Adapter "smart integer columns." The idea was to use the :limit option to determine whether a smallint, int, or bigint should be used. This is something that the Postgres adapter had already previously implemented. The relevant code in activerecord/lib/active_record/connection_adapters/mysql_adapter.rb is:
# Maps logical Rails types to MySQL-specific data types.
def type_to_sql(type, limit = nil, precision = nil, scale = nil)
return super unless type.to_s == 'integer'
case limit
when 0..3
"smallint(#{limit})"
when 4..8
"int(#{limit})"
when 9..20
"bigint(#{limit})"
else
'int(11)'
end
end
Mirko Froehlich suggests monkey patching this function. Timothy Jones blogged about it.
To monkey-patch this, just drop a file (fix_mysql_adapter.rb) into your initializers/ directory, as such:
module ActiveRecord
module ConnectionAdapters
class MysqlAdapter < AbstractAdapter
# Maps logical Rails types to MySQL-specific data types.
def type_to_sql(type, limit = nil, precision = nil, scale = nil)
return super unless type.to_s == 'integer'
case limit
when 0..3
"smallint(#{limit})"
when 4..11
"int(#{limit})"
when 12..20
"bigint(#{limit})"
else
'int(11)'
end
end
end
end
end
All we've done is change how the mysql adapter interprets the limit attribute. In my opinion, this isn't a particularly great solution, as it's more appropriate for non-foreign key integers to behave as the rails team suggested. This really only affects us when we're dealing with foreign keys, because mysql enforces our column types to match.
The Real Problem
So, if the problem isn't here, where is it? The problem actually lies in the schema dumper. Notice in the previous code samples the else clause in the case statement. If limit is nil (or outside of 0..20), then this falls back to int(11). Curiously, when I have a migration such as the following, the schema dumper adds a :limit => 11, even though I didn't specify it!
$> rails test -d mysql
$> cd test
$> ruby script/generate model user name:string
$> ruby script/generate model game name:string user_id:integer
$> rake db:create db:migrate
First, let's check our game migration to verify that it doesn't specify :limit => 11.
class CreateGames < ActiveRecord::Migration
def self.up
create_table :games do |t|
t.string :name
t.integer :user_id
t.timestamps
end
end
def self.down
drop_table :games
end
end
So, there's no limit specified. But let's take a look at schema.rb.
ActiveRecord::Schema.define(:version => 20080624161220) do
create_table "games", :force => true do |t|
t.string "name"
t.integer "user_id", :limit => 11
t.datetime "created_at"
t.datetime "updated_at"
end
create_table "users", :force => true do |t|
t.string "name"
t.datetime "created_at"
t.datetime "updated_at"
end
end
Ah ha! Where'd that :limit => 11 come from? The schema generator!!! This is our culprit. We need to isolate the schema generation code, and ensure that isn't forcing :limit => 11 onto our integers that don't explicitly set a limit.
As I started down this track, I hooked up with Rob Sterner from ITA Software on IRC. He filed the original ticket. By the time I got in touch with him, he pretty much had the problem solved.
First, let's fully illustrate the problem. When you run migrations, naked integer statements
t.integer :myvalue
are inserted into the database with :limit => 11 (this is handled by the extract_limit() function in the adapters). SchemaDumper.dump is called after your migrations are finished, and creates schema.rb by inspecting the database (not your migrations -- which makes sense). However, when extracting the information from the database, the SchemaDumper picks up this limit of 11, which causes it to write this out in your schema.rb file:
t.integer :myvalue, :limit => 11
See a problem? Yup, where in our migration we did not explicitly set a limit, the SchemaDumper did!
The Solution
So what's the solution? Well, what we need to do is alter SchemaDumper to identify int(11), and special case the output (so that :limit => 11 is not appended). At first, this seemed very hacky to me. What if I want a bigint(11) and not int(11)? Well, turns out this isn't a valid concern. Why? If we really wanted bigint(11), our migration would look different:
t.integer :myvalue, :limit => 11
So, when migrations are run, this field would be placed into the database as a bigint(11) column. And, when SchemaDumper encounters it, it will also see it as bigint(11), As long as our special case discriminates between bigint(11) and int(11), then we're in the clear! The fact is, in rails 2.1, there's no way to get an int(11) column in mysql unless you've left off the :limit in your migration.
Where's the patch?
Funny (read: agonizing) story.....we were testing all of this against 2.1.0. When we checked out edge rails, all of this code had changed!!! In fact, looks like Jeremy Kemper (bitsweat) already fixed it.
For the sake of completeness, I'm including the patch that Rob threw together that does the trick for 2.1.0. This patch applied to /activerecord-2.1.0/lib/active_record/connection_adapters/mysql_adapter.rb does the trick.
127c127,129
< else
---
> elsif sql_type == 'int(11)'
> nil # special case for :integer columns w/ no explicit :limit set in their migration
> else
The Lesson
Check out edge rails to make sure that the problem is still unresolved before you spend all day on something!!!
Also, be aware that edge has changed the meaning of :limit. See the ticket for Jeremy's explanation. Up through Rails 2.1, the :limit attribute for integers dictated the display width. In other words, it did not specify the storage size, but rather the amount of space mysql would use to display it when returning query results. Moving forward, it will be used to indicate the number of bytes to use for storage. Rails will now effectively use tinyint (1), smallint (2), mediumint (3), int (4) and bigint (8). So, prepare yourselves accordingly.
Special thanks to Rob Sterner for spending lots of time today working through this with me.
