Rails 3.2 + MySQL: Error: Field 'created_at' doesn't have a default value: INSERT INTO

insert-into, mysql, ruby, ruby-on-rails, timestamp

Solution

I just ran into something similar on a fresh install of MySql on Mac OS.

I finally narrowed it down to the combination of newer versions of MySql turning on "strict mode" by default, and my project having a table with some questionable constraints on it. The table in question was the "join table" used in a `:has_and_belongs_to_many` relationship. Somehow that table had been created with `:created_at`, and `:updated_at` attributes that had a constraint of `:null => false` on them. Rails 3.2 doesn't automatically populate the timestamp fields for join tables of `:habtm` relationships. When strict mode is turned off MySql will just populate the cols with zeroed out dates, like `0000-00-00 00:00:00`. With strict mode turned on it raises an exception.

To fix the issue I ran a migration to allow the timestamp fields to be null. Like this:

class ChangeNullableForTimestampsOnThing1sThing2s < ActiveRecord::Migration
  def up
    change_column(:thing1s_thing2s, :created_at, :datetime, :null => true)
    change_column(:thing1s_thing2s, :updated_at, :datetime, :null => true)
  end

  def down
    change_column(:thing1s_thing2s, :created_at, :datetime, :null => false)
    change_column(:thing1s_thing2s, :updated_at, :datetime, :null => false)
  end
end

Honestly, it's probably better to just drop the columns if you don't need them, but we have a couple of special cases where they actually get set manually.

Problem

I created a new migration, where is mentioned ``` ... t.timestamps ``` in the created table are added these two columns ``` ... | created_at | datetime | NO (Null) | | NULL (Default) | | updated_at | datetime | NO (Null) | | NULL (Default) | ... ``` When I want to create a new item, I always get the error message ``` Mysql2::Error: Field 'created_at' doesn't have a default value: INSERT INTO `table_name` (`first_col`, `second_col`) VALUES ('a', 'b') ``` Am I missing something? I sent this miniapp to a friend of mine and he is able to run successfully run it -> the record is created in database. What am I missing?

Original source