Background

In MySQL, there exists a default behavior: If some columns in a row of data are updated, and there is a column of timestamp type in the current row, this "timestamp" column updates timestamp automatically. This behavior is controlled by "explicit_defaults_for_timestamp" parameter. During the application process, there are always some unexpected anomalies, bringing many conveniences to database usage. The following introduces frequent problems and notices in the usage of "explicit_defaults_for_timestamp".

Failure Phenomenon

• Problem 1：Why does the same table-building statement show different table structure information in the wake of successful creation after MySQL5.5 is upgraded to MySQL5.6?
• Problem 2： Why cannot timestamp field update the timestamp automatically after MySQL5.5 is upgraded to MySQL5.6?
• Problem 3: Why does the shown information change from the current timestamp to '0000-00-00 00:00:00' when the timestamp field is inserted after MySQL5.5 is upgraded to MySQL5.6.
• Problem 4: in one table there are multiple timestamp fields, and default values of the fields differ in shown results.

Analysis of cause

"explicit_defaults_for_timestamp" is the primary cause leading to the aforesaid problems. This parameter is introduced in MySQL5.6.6, and behaviors of earlier versions are equivalent to the value "OFF". In 5.6 and 5.7 series versions, the default value is OFF. From MySQL8.0.2 on, the default value is ON. This parameter belongs to the Boolean type, and descriptions of value meanings are as follows:

• ON: disable the feature of "timestamp default", thus timestamp is not updated, except for the explicit designation when defining table structure;
• OFF: enable the feature of "timestamp default", thus timestamp is automatically updated. When creating the table, automatically add the following content to table structure definition of "timestamp" field type: DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Solution

During the version upgrading and data migration, the data with the "timestamp" field need to be aligned to the value of "explicit_defaults_for_timestamp" parameter, ensuring that results shown by timestamp meet the expected requirements.

Case review

Case 1: Differences of table structure after table creation using the same SQL

Table structure definition is as follows:

CREATE TABLE tb_01(
   x int(11) NOT NULL,
   y timestamp NOT NULL,
   PRIMARY KEY (x)
) ENGINE=InnoDB;

Set "explicit_defaults_for_timestamp" as different values, and observe the differences of the table structure after creation:

Case 2: insert the data and check if "timestamp" field is automatically updated

Insert the data:

insert into tb_01(x) values(1);
insert into tb_01(x) values(2);
insert into tb_01(x) values(3);

Set "explicit_defaults_for_timestamp" as different values, and observe the differences of the table structure after creation:

Notes: If inserting the data when the value is ON, there might be the following error information, e.g., "NO_ZERO_IN_DATE" and "NO_ZERO_DATE" are set for the current instance's sql_mode. Solution: change "sql_mode" into "Loose Mode". Then, you can insert the data successfully, but there will be "warning" information.

ERROR 1364 (HY000): Field 'y' doesn't have a default value

Case 3: Difference of results after a NULL value is inserted into "timestamp" field

Insert the data:

insert ignore into tb_01 values(1,NULL);

Value: ON	Value: OFF

Note: If executing "Insert Into" directly when the value is ON, there will be the following error information:

ERROR 1048 (23000): Column 'y' cannot be null

You need to add "ignore" keyword to insert the data successfully, but there will be the following "warning" information:

Case 4: Single table contains multiple "timestamp" fields

Set the parameter "explicit_defaults_for_timestamp=OFF" to create the table:

CREATE TABLE tb_02(
   x int(11) NOT NULL AUTO_INCREMENT,
   y timestamp NOT NULL,
   z timestamp NOT NULL,
   w timestamp NOT NULL,
   PRIMARY KEY (x)
) ENGINE=InnoDB;

Show the following result:

Note: If multiple "timestamp" fields coexist. only one field's default value is updated with a timestamp, and other default values are all '0000-00-00 00:00:00' 。

Conclusions & suggestions

Conclusions

• If you update timestamp automatically using the "timestamp" field, you need to set "explicit_defaults_for_timestamp=OFF" before creating table structure. Then, the created table automatically adds the following definition to "timestamp" field: DEFAULT CURRENT_TIMESTAMP ON UPDATE
• If the table has been created and "ALTER TABLE" command needs to be executed, there is explicit table definition for automated updating of timestamp field, for example:

ALTER TABLE tb_02 modify column  w  timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; 

Recommendations

1. Before data migration, you need to ensure the consistency of the values of the parameter "explicit_defaults_for_timestamp" in the source database and target database.
2. In table creation, we recommend that you specify explicitly if "timestamp" field updates timestamp automatically, and avoid the compatibility risk resulting from use of implicit default value, for example:

CREATE TABLE tb_01 (
  x int(11) NOT NULL AUTO_INCREMENT,
  y timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  z timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (x)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. We recommend that the timestamp for data is subject to control by the applications, avoiding the influence by a behavioral difference of the "timestamp" field during the version upgrading process.