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

Question

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?

How are you creating the new item? – ericeason Apr 24 '13 at 18:57 — ericeason, Apr 24 '13 at 18:57
`@photo = Photo.new(params[:photo])` ... `@photo.save` – user984621 Apr 24 '13 at 19:17 — user984621, Apr 24 '13 at 19:17

score 17 · Answer 1 · answered May 13 '13 at 20:14

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.

Very helpful thank you. I hope they fix this soon. Here is more of a discussion on the issue: https://github.com/rails/rails/issues/10307 — Michael Yagudaev, Jul 04 '13 at 21:50

montrealmike · Answer 2 · 2015-11-06T19:51:26.770

5

to add to Jeremy's answer, on a mac you can remove strict mode with

> vi /usr/local/opt/mysql/my.cnf

then replace

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

with

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

edited Nov 06 '15 at 19:51

answered Nov 06 '15 at 19:19

montrealmike

11,433
10
64
86

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

2 Answers2

Linked