[SOLVED] MySQL invalid default value for [something]

MySQL invalid default value for [something]

I tried to restore external MySQL DB in my window MySQL.

mysql -u root -p test < C:\Users\myname\Downloads\db.sql

but there is error like this.

ERROR 1067 (42000) at line 200: Invalid default value for 'VERSION_DATE'

some people said that it was caused by character encoding so i tried this again.

mysql -u root -p test < C:\Users\myname\Downloads\db.sql --default-character-set=utf8

but same error.

what is wrong with this MySQL restoring.

here's the line 200 starts.

CREATE TABLE `APP_EVENT_HISTORY` (
  `OWNER_ID` varchar(128) NOT NULL,
  `APP_ID` varchar(64) NOT NULL,
  `EVENT_ID` varchar(64) NOT NULL,
  `VERSION_ID` varchar(64) NOT NULL,
  `VERSION_DATE` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `SCRIPT` text,
  `DESCRIPTION` varchar(1024) DEFAULT NULL,
  `CREATE_USER_ID` varchar(128) NOT NULL,
  `CREATED_DATE` datetime NOT NULL,
  `UPDATE_USER_ID` varchar(128) DEFAULT NULL,
  `UPDATED_DATE` datetime DEFAULT NULL,
  PRIMARY KEY (`OWNER_ID`,`APP_ID`,`EVENT_ID`,`VERSION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Solution

I found this link which may prove useful: Mysql datetime DEFAULT CURRENT_TIMESTAMP error

Yes, it may be due to difference on mysql versions (if you did the backup on one version and restoring on another one).