While hacking on a side project in Ruby on Rails, I ran across this weird error when trying to insert new data:
ActiveRecord::StatementInvalid: Mysql::Error: Column 'attr2' cannot be null: INSERT INTO `foo` (`attr1`, `attr2`) ... VALUES ('1', NULL)
where attr2 is a varchar (or t.string, in Rails lingo) and set to not null default '' (or, in other words, :null=>false, :default=>''). Strangely enough, instead of the default value of ”, ActiveRecord was setting the value to nil instead, which translates into a NULL. Since the schema explicitly forbids NULLs on that column, the statement explodes.
After an hour of poking around and hacking up a spike solution, it turns out a plugin was to blame. I’d pulled in the foreign_key_migrations plugin (a highly recommended add-on) to automatically install foreign key constraints (in this day and age, the foremost web framework still can’t automatically handle FOREIGN KEY constraints, the most basic tool for ensuring data integrity in relational databases, for its migrations? Bah!).
This plugin has a dependency on redhillonrails_core, which has a known bug: Incorrectly overwrites mysql empty-string default with nil for string/text/binary types.
The bug is apparently not being worked on as of the time of this writing. The dev doesn’t consider this a bug, as he claims that “[he considers] empty strings to be semantically identical to NULL”.
This position, unfortunately, is not supported by the SQL standard. Wikipedia has a section on common SQL NULL mistakes documenting some of the potential problems involved in making such an assumption. Philip Greenspun has further notes on this. Using NULLs trigger the all the arcane annoyances of three-valued logic, and you must be prepared to consider True, False, and NULL values as comparison outcomes. Someone not very versed in three-valued logic can easily cause a number of subtle mistakes trying to compare values.
To workaround this bug, you will need to comment out the initializer function in
vendor/plugins/redhillonrails_core/lib/red_hill_consulting/core/active_record/connection_adapters/mysql_column.rb
Alternatively, you can delete the file, and remove relevant references to it..
If you agree with dev’s position that NULLs are “semantically identical” to empty strings, remember to pay attention when you formulate your SQL queries (and when Rails formulates those queries) — your results may not be what you expect, if implemented naively. Get your three-valued logic truth tables out 🙂