Defaults for MySQL text/blob columns in Rails migrations

It's a well documented problem with Rails and MySQL that even though text and blob columns cannot have default values, the Rails schema dumper does add :default attributes when dumping a schema. You can find the Rails ticket for this issue here. Rails routinely dumps the schema when running tests as part of the process of cloning the current development db schema to the test db. This mean all your tests go kaput with an exception looking something like this: Mysql::Error: #42000BLOB/TEXT column 'xyz' can't have a default value.

The suggested solution for this (also documented in the ticket) was to uncomment the config.active_record.schema_format = :sql line in config/environment.rb and start dumping the schema in SQL instead of Rails migrations. Unfortunately, mysqldump (actually the underlying SHOW CREATE in MySQL) has it's own share of issues which caused my tests to break with the following error: ActiveRecord::StatementInvalid: Mysql::Error: #HY000Field 'name' doesn't have a default value

I don't know if anyone else has faced this problem, but I figure I'd share the rake hack which I use to get around this issue. Open up databases.rake and look for
desc "Load a schema.rb file into the database"
task :load => :environment do
file = ENV['SCHEMA'] || "db/schema.rb"
and modify it to
desc "Load a schema.rb file into the database"
task :load => :environment do
file = ENV['SCHEMA'] || "db/schema.rb"
case ActiveRecord::Base.configurations[RAILS_ENV]["adapter"]
when "mysql"
no_defaults_for_text_columns_schema = "db/no_defaults_for_text_columns_schema.rb"
modified_file =, "w")
modified_file.puts("# This file is a clone of the dumped schema with 'default'
# statements removed for text columns so MySQL doesn't complain"), "r") do |original|
line.gsub!(/:default => "",/, "") if line.include?("text") && line.include?(":default")
file = no_defaults_for_text_columns_schema

I know this is fairly ugly, so please let me know if you've come across any better solutions to this problem.

1 comment:

Unknown said...

I found out that the :default => "" is not dumped to the schema if NULL values are allowed.
So removing :null => false or setting the option via other MySQL tools fixes the problem.
Of course only possible if removing :null => false does not break your code.