Re: Error 1067 Invalid default value

[Peter Brawley]

DRV_SERIALVARCHAR(32) is invalid, whitespace missing between DRV_SERIAL and VARCHAR.

That could not possible run on any version of MySQL.

> ETL_LOAD_DATE TIMESTAMP,

A column name should not imply a datatype it isn't; the name etl_load_timestamp would bebetter.

Timestamp rules changed substantially in versions 5.5.3 through 5.6.6:

Before 5.6.4 TIMESTAMPs were 4-byte integers.

Before 5.5.3 TIMESTAMP(n) specified display width but since 5.6.4 TIMESTAMP(n) specifies 0<=n<=6 microsecond digits. If created with sql_mode=MAXDB (Appendix B), they behave like DATETIME columns. Otherwise:

Before 5.6.5 tThey could be NULLed only if created as TIMESTAMP NULL and were otherwise autodeclared NOT NULL. They took zero or valid DATETIME values. One TIMESTAMP column per table could be defaulted on INSERT or UPDATE. If there was no explicit default, the first TIMESTAMP was autoassigned DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, and subsequent TIMESTAMPs were autoassigned DEFAULT ‘0000-00-00 00:00:00’.

Since 5.6.5: Any number of TIMESTAMP columns can be defined with any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP. DEFAULT <constant> and ON UPDATE <constant> apply <constant> on INSERT and UPDATE respectively.

Since 5.6.6: If the new system variable explicit_defaults_for_timestamp is set ON, TIMESTAMPs not explicitly declared NOT NULL accept NULLs, no TIMESTAMP column is autoassigned DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, and TIMESTAMPs declared NOT NULL without a DEFAULT clause have no default value (if sql_mode is strict, an error occurs).