Peter Marklund

Peter Marklund's Home

Mon October 01, 2007
Programming

Rails Migration Gotcha: Forgetting to set active_record.schema_format to :sql

If you rely on any SQL not supported by Rails migrations such as foreign keys - don't forget to set config.active_record.schema_format = :sql in your environment.rb. Otherwise your test database will be out of synch with your development database. I really would prefer if Rails used the SQL dump format for setting up the test database by default as that would be more reliable.

The reason I came across this now was actually that I found what seems to be a bug with Rails migrations. Given this create_table statement in my migration:

    create_table :users do |t|
      t.string :username, :null => false
      t.string :role, :null => false
      t.timestamps 
    end

Rails will create this statement in the dump file:

  create_table "users", :force => true do |t|
    t.string   "username",   :default => "", :null => false
    t.string   "role",       :default => "", :null => false
    t.datetime "created_at"
    t.datetime "updated_at"
  end

The difference is subtle but important to me. In the dump format Rails insists that the default value is the empty string. This is not what I want when I say that a column is not null, since with MySQL 5, even with SQL mode set to traditional, the column will then be defaulted to the empty string when someone tries to set the column to NULL.

Comments

Marc Chung said over 6 years ago:

I think you're running into a bug with MySQL

http://bugs.mysql.com/bug.php?id=27747

The resolution is to upgrade to MySQL 5.0.51. Give that a shot and see if your Ruby schema.rb is properly created (without the default => "")

-Marc

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