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,'
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) 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
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" .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
