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"
load(file)
end
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 = File.open(no_defaults_for_text_columns_schema, "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")
File.open(file, "r") do |original|
original.each_line{|line|
line.gsub!(/:default => "",/, "") if line.include?("text") && line.include?(":default")
modified_file.puts(line)
}
end
modified_file.close
file = no_defaults_for_text_columns_schema
end


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