Tag Archives: MySQL timestamp

MySQL timestamp set default value error Invalid default value reason and solution

MySQL timestamp setting default value error Invalid default value reason and solution

The data exported from the mysql5.5 database is placed in mysql5.7.10 and an error is reported!

DROP TABLE IF EXISTS `passwd_reset`;
CREATE TABLE `passwd_reset` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staffId` int(11) DEFAULT NULL,
  `toEmail` varchar(50) DEFAULT NULL,
  `token` varchar(100) DEFAULT NULL,
  `validTime` int(11) DEFAULT NULL,
  `createTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=gbk;

error:

[SQL] DROP TABLE IF EXISTS passwd_reset; Affected rows: 0 Time: 0.000s [Err] 1067-Invalid default value for’createTime’

reason:

The value range of timestamp type: 1970-01-01 00:00:00 to 2037-12-31 23:59:59,

Adjust the initial value to 1970-01-02 00:00:00 and it’s fine. For the time being, I don’t know how to get it into the 5.5 database.