Howto Fix “Invalid default value for” For Timestamp Column

I have a database migration like this:

class FreeradiusCui < ActiveRecord::Migration[4.2]
  def change
		create_table :cui, id: false do |t|
			t.string :clientipaddress, null: false, default: ''
			t.string :callingstationid, null: false, default: ''
			t.string :username, null: false, default: ''
			t.string :cui, null: false, default: ''
			t.timestamp :lastaccounting, null: false, default: '0000-00-00 00:00:00'
		end

		execute 'ALTER TABLE cui ADD COLUMN creationdate TIMESTAMP NOT NULL DEFAULT now() AFTER cui';
		execute 'ALTER TABLE cui ADD PRIMARY KEY (username, clientipaddress, callingstationid);'
  end
end

It used to work just fine for years, but today I installed Mariadb 10.11.3 and I got:

== 20140821214704 FreeradiusCui: migrating ====================================
-- create_table(:cui, {:options=>"ENGINE=InnoDB", :id=>false})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Invalid default value for 'lastaccounting'
/home/damon/softworks/sos-sso/db/migrate/20140821214704_freeradius_cui.rb:3:in `change'

Caused by:
ActiveRecord::StatementInvalid: Mysql2::Error: Invalid default value for 'lastaccounting'
/home/damon/softworks/sos-sso/db/migrate/20140821214704_freeradius_cui.rb:3:in `change'

Caused by:
Mysql2::Error: Invalid default value for 'lastaccounting'
/home/damon/softworks/sos-sso/db/migrate/20140821214704_freeradius_cui.rb:3:in `change'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

Well, that sucks. After some search on Gxxgle and not finding anything relevant (as usual?) - am I the only one or Gxxgle search is degrading even more? After messing around for a bit and searching again I found in MySQL reference manual - https://dev.mysql.com/doc/refman/8.0/en/datetime.html:

The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’ UTC.

Apparently year 0 is not supported anymore for timestamp type. Why not? Don’t we have 64-bit timestamps now or what? Also I tried to change to datetime and it worked with year zero which is…interesting.

Anyways I changed it to 1970, but a day later, because of timezones. See you on 20 February 2038 again.

class FreeradiusCui < ActiveRecord::Migration[4.2]
  def change
		create_table :cui, id: false do |t|
			t.string :clientipaddress, null: false, default: ''
			t.string :callingstationid, null: false, default: ''
			t.string :username, null: false, default: ''
			t.string :cui, null: false, default: ''
			t.timestamp :lastaccounting, null: false, default: '1970-01-02 00:00:00'
		end

		execute 'ALTER TABLE cui ADD COLUMN creationdate TIMESTAMP NOT NULL DEFAULT now() AFTER cui';
		execute 'ALTER TABLE cui ADD PRIMARY KEY (username, clientipaddress, callingstationid);'
  end
end

And it works now.

References:

https://dev.mysql.com/doc/refman/8.0/en/datetime.html
https://fullstackheroes.com/tutorials/rails/column-types/
misleading, but why? https://api.rubyonrails.org/classes/ActiveRecord/Timestamp.html
also the quality of Rails Guides is lower today, does not list types, nor defaults examples - https://guides.rubyonrails.org/active_record_migrations.html

Howto Fix “Invalid default value for” For Timestamp Column

References:

Add Comment