How to fix "ERROR 1067 (42000): Invalid default value for tableName" occur even after removing 'DEFAULT 0' when two timestamp columns are defined.

In MySQL 5.7 'DEFAULT 0' is not accepted for when defining timestamp columns as 'NO_ZERO_DATE' mode is enabled by default.

I get "ERROR 1067 (42000): Invalid default value for 'REG_LAST_UPDATED_TIME' error when creating the table below, even after removing 'DEFAULT 0' in
EX: 'REG_CREATED_TIME    TIMESTAMP NOT NULL DEFAULT 0,'

1. Issue description

CREATE TABLE IF NOT EXISTS REG_RESOURCE(
            REG_PATH_ID         INTEGER NOT NULL,
            REG_NAME            VARCHAR(256),
            REG_VERSION         INTEGER NOT NULL AUTO_INCREMENT,
            REG_MEDIA_TYPE      VARCHAR(500),
            REG_CREATOR         VARCHAR(31) NOT NULL,
            REG_CREATED_TIME    TIMESTAMP NOT NULL,
            REG_LAST_UPDATOR    VARCHAR(31),
            REG_LAST_UPDATED_TIME    TIMESTAMP NOT NULL,
            REG_DESCRIPTION     VARCHAR(1000),
            REG_CONTENT_ID      INTEGER,
            REG_TENANT_ID INTEGER DEFAULT 0,
            REG_UUID VARCHAR(100) NOT NULL,
            CONSTRAINT PK_REG_RESOURCE PRIMARY KEY(REG_VERSION, REG_TENANT_ID)
)ENGINE INNODB;

When creating above table "ERROR 1067 (42000): Invalid default value for 'REG_LAST_UPDATED_TIME' error occurred and table didn't get created. Please note that this error shows only when there are two timestamp columns (REG_CREATED_TIME and REG_LAST_UPDATED_TIME) in the table.

2. What happens when there are two time stamp columns?

According to [1] when we have two timestamp columns not declared an explicit 'DEFAULT' or 'ON UPDATE' clause, first column is automatically assigned the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes, while the second one is assigned the '0000-00-00 00:00:00' (the “zero” timestamp).

3. Why are we getting the Error explained In issue description?

When 'strict SQL mode' and ' NO_ZERO_DATE' mode is enabled in MySQL server the “zero” timestamp is not allowed and it gives an error [2]. In MySQL 5.7 by default 'NO_ZERO_DATE' mode is enabled.

4.Fix

1) Recommended way of doing is replacing "DEFAULT 0" with "DEFAULT CURRENT_TIMESTAMP" .

2) You can also fix it by changing mysql modes

E.g. Setting MySQL mode to  'ALLOW_INVALID_DATES' [3].

> SET SQL_MODE='ALLOW_INVALID_DATES';


[1]. http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp
[2]. http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date
[3]. http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_allow_invalid_dates