A DDL statement is used to create a table for administrators.

CREATE TABLE `manager` (
  `id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL.`name` varchar(10) NOT NULL COMMENT 'name'.`phone` varchar(11) NOT NULL COMMENT 'Mobile number'.`createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP.`updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP.`deletedAt` datetime,
  PRIMARY KEY (`id`),
  UNIQUE KEY phone (`phone`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Administrator';
Copy the code

All of this works fine in my local environment, but when I run this SQL in my test environment’s database, I get the following error:

[42000][1067] Invalid default value for 'createdAt'
Copy the code

At first I thought there was something wrong with my SQL, so I routinely checked my SQL, but I didn’t find anything wrong. Many of the answers online are about SQL_MODEL, but my SQ_MODEL is empty, so it’s not the problem.

Finally, the problem is the version problem, because my local MySQL version is 5.6, while the test environment is 5.5, so this problem occurs. Another point about this problem is that in the 5.5 version, one field cannot have two CURRENT_TIMESTAMP at the same time. After 5.6, a series of restrictions were lifted.

Previously, at most one TIMESTAMP column per table could be automatically initialized or updated to the current date and time. This restriction has been lifted. Any TIMESTAMP column definition can have any combination of DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP clauses. In addition, these clauses now can be used with DATETIME column definitions. For more information, see Automatic Initialization and Updating for TIMESTAMP and DATETIME. The official explanation