Why MySQL TIMESTAMP Columns May Fail with “cannot be null” After Migration

Recently the author encountered a problem similar to an article titled "Fault Analysis | MySQL Migration timestamp column cannot be null". A colleague used a commercial data‑sync tool to copy a MySQL 5.7.28 database to a MySQL 5.7.20 instance. Tables containing TIMESTAMP columns with a default of current_timestamp failed to sync, while tables with DATETIME columns succeeded.

When the tables are created manually the statements succeed. The original DDL is:

create table test(
  id int not null auto_increment,
  createtime timestamp null default current_timestamp,
  updatetime timestamp null default current_timestamp on update current_timestamp
);

The sync tool reports an error indicating an invalid default value for the updatetime column. The logged SQL generated by the tool loses the NULL and DEFAULT clauses:

CREATE TABLE `test`.`test` (
  `id` INT NOT NULL,
  `createtime` TIMESTAMP,
  `updatetime` TIMESTAMP
) COLLATE utf8_general_ci

MySQL’s explicit_defaults_for_timestamp system variable determines how TIMESTAMP columns handle NULL and default values. When this variable is OFF (the default for MySQL 5.7.20), the server applies non‑standard rules:

(1) A TIMESTAMP column without an explicit NULL declaration is automatically declared NOT NULL.

(2) The first TIMESTAMP column without explicit NULL, DEFAULT, or ON UPDATE is automatically given DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP .

(3) Subsequent TIMESTAMP columns without explicit NULL or DEFAULT are given DEFAULT '0000-00-00 00:00:00' . If the SQL mode includes NO_ZERO_DATE , this default is illegal, triggering the error.

Because the target MySQL 5.7.20 has explicit_defaults_for_timestamp=OFF and NO_ZERO_DATE enabled, the second TIMESTAMP column ( updatetime ) is implicitly set to '0000-00-00 00:00:00' , which is rejected, resulting in the "Invalid default value for 'updatetime'" message.

Re‑creating the table with the simplified DDL that the tool actually executes reproduces the error:

create table test(id int not null, createtime timestamp, updatetime timestamp);

Invalid default value for 'updatetime'

The root cause is that the sync tool strips the NULL and DEFAULT attributes, causing MySQL to apply its implicit TIMESTAMP rules. If a table contains two or more TIMESTAMP columns, the migration will fail unless the tool preserves the original column definitions or the server is configured with explicit_defaults_for_timestamp=ON (or the offending column defaults are adjusted).

In addition, the original DDL is contradictory because it declares TIMESTAMP columns as NULL while also assigning a non‑NULL default, which is a design inconsistency that should be avoided.