This project is archived and is in readonly mode.

#55 ✓resolved

Remove default integer :limit from MysqlAdapter.native_database_types

Reported by matthuhiggins | April 27th, 2008 @ 11:11 PM

A recent change to the MySql adapter adds smallint and bigint support based on the specified :limit. Since the default integer limit is specified as 11, integer columns now default with type 'bigint(11)' rather than 'int(11)'. I assume this change was unintentional.

Patch is attached.

Comments and changes to this ticket

  • DHH

    DHH April 29th, 2008 @ 10:48 PM

    • State changed from “new” to “resolved”
  • Mirko Froehlich

    Mirko Froehlich May 21st, 2008 @ 09:39 PM

    • Assigned user set to “DHH”

    Actually this does not seem to be fixed. I am running into this issue as well:

    When I run "rake db:test:clone" in Rails 2.0.991, it now creates foreign key integer columns as bigint(11) rather than int(11), which later causes problems for our foreign key constraints since the types don't match those of the primary keys.

    Could it be that it infers the limit of 11 from my development database (even though my migrations don't explicitly specify a limit), and then applies this when cloning the test database, thus creating these columns as bigint instead of int?

  • Mirko Froehlich

    Mirko Froehlich May 21st, 2008 @ 10:57 PM

    I am now convinced that this is the problem, i.e. rake db:test:clone results in integer columns that were originally created without an explicit limit in the migration script (and implicitly ended up as int(11)) to be created as bigint(11) in the test db, rendering them incompatible with primary keys of type int(11). This would never cause a problem in production or development, but it breaks the tests if you are using foreign key constraints.

    I am not sure about a good solution, other than reverting the bigint change. For my purposes, I am currently monkey-patching this code to raise the bigint range from 9..20 to 12..20, which bypasses this problem.

  • Bill Cleveland

    Bill Cleveland July 3rd, 2008 @ 08:49 PM

    • Tag set to activerecord, patch

    Can we patch the mysql_adapter so that the primary index is a bigint(11) instead of a int(11)?

    This would solve the following problems:

    1. The 11 in int(11) is a formatting specification and not a datatype specification. Therefore even though we specified an integer being formatted to display 11 character wide, the key value can only store 9 digits (well 9 full digits) due to the index being only 32 bits wide.

    2. The issue that Mirko is having would be resolved, since the foreign key integer columns would now correctly match the data type of the primary key.

    3. Being a BIGINT the primary key data type inches closer to the standard data type that MySQL databases use as its primary key which is an UNSIGNED BIGINT



  • Bill Cleveland

    Bill Cleveland July 3rd, 2008 @ 09:30 PM

    • Tag cleared.

    Well since there is a move to change the interpretation of :limit to number of bytes instead of number of characters, please disregard some of my previous comment.

    It would be nice if the primary_key was created using a "key datatype" like :uid, so that regardless of which database adapter is being used, it will be possible to create foreign_key columns with the same datatype as the primary key it references.

    I would still like the MySQL adapter to use BIGINT as its primary key.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>