SmartLogic Logo (443) 451-3001

The SmartLogic Blog

SmartLogic is a web and mobile product development studio based in Baltimore. Contact us for help building your product or visit our website to learn more about what we do.

Rails 2.1 broke my mysql foreign keys!

June 24th, 2008 by

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.

  • http://www.swagonline.org Nigel Sharp

    Hi John,
    Is there a best practise workaround for this ? Unfortuneately I’m stuck with Rails 2.1. When I specify foreign keys as :integer, they’re ending up as BIGINT(11) in MySQL, resulting in failure to save my models. Thanks for any help you can give !
    Cheers,
    Nigel

  • http://www.smartlogicsolutions.com/wiki/John_Trupiano John Trupiano

    Hey Nigel,

    I continue to use the monkey patch I included at the top and drop it into initializers/. It seems to be the cleanest and most modular. Furthermore, when the app is upgraded to 2.2, I simply need to remove that file.

    The patch directly to the source that I included at the end requires the least amount of code, but it would probably require you to fork that version of rails and maintain it (because you’ll need that version of rails on any box that will be used to develop/test/run in production), which seems like overkill.

    -John

  • http://www.swagonline.org Nigel Sharp

    Thanks for your kind help, I will give that a go. I hadn’t realized that the fix could be implemented in my app’s code, rather than rails itself !
    Thanks once again,
    Nigel

  • Pingback: Rails Migrations, MySQL, unsigned integers, primary keys, and a lot of fun times | Snow Giraffe Tech

John Trupiano co-founded SmartLogic with Yair Flicker in May 2005 and was co-president through 2011. Check out his GitHub Projects or follow @jtrupiano on Twitter.

John Trupiano's posts