I got my first taste today of dealing with the default values in MySQL. It seems that Rails loads the default values for fields based on the database (seems reasonable, specify in one place and let if flow through). The problem is that if I say NOT NULL and do not specify a default value, MySQL inserts a default value anyway. See
for all the details. Obviously Rails can't fix this since it is an oddity with MySQL (that seems to be fixed in >5.0.2). Rails has no way of knowing if the developer meant "not null with no default" or "not null with a default of an empty string". So it seems to have taking the attitude of just doing what the database tells it. I guess the idea is that the developer can override this if the developer knows something more about the field. My problem is that I really don't like invalid data sneaking through. But I don't want to put a bunch over overrides all over my models either.
I would rather Rails assume I meant "not null with no default" when it sees "not null with a default of empty string" since it seems so rare that someone would actually want "not null with a default of empty string" compared to just "not null with no default". For integers you have a much more common case of wanting "not null with a default of 0" but I still feel that it is less common than an integer field where you have "not null with no default". So in effort to fix this problem I have developed the following code which I place in a file in my lib/ directory and then require it from my environment.rb.
class MysqlColumn < Column
return @default if 50002 <= sprintf('%02d%02d%02d',
return @default if name.to_sym == :lock_version
when :integer, :float, :decimal
@default == 0 ? nil : @default
when :datetime, :timestamp
@default == '0000-00-00 00:00:00' ? nil : @default
@default == '0000-00-00' ? nil : @default
@default == '00:00:00' ? nil : @default
when :text, :string
@default == '' ? nil : @default
class << self
alias db_columns :columns
db_columns.collect do |c|
def default_for(field, cur_default)
This code will try to guess what the developer meant only if we are running MySQL < 5.0.2. It makes a special exception if the field is "lock_version" since this is commonly an integer with a default of 0. In addition I have a hook that a model can override to correct my guessing if needed. Basically you override "default_for" in your model. The method gets two arguments. The field name and the current guessed default (or the database default if no guessing took place). This allows a model to easily correct the database or my guessing if need be.
After setting up this code Rails will hopefully see the database as it was specified not as MySQL interpreted my schema.
I am thinking of packaging this code up as a plugin since it seems to work for me (just wrote it today so it is very alpha) but I was interested in getting thoughts from others to see if another solution might be more elegant and involve less guessing.
I look forward to feedback.