Failure Analysis | Timestamp column cannot be null after MySQL migration - 个人文章

Author: Qin Fulang
A member of the Aikesheng DBA team, responsible for the daily problem handling of the project and the troubleshooting of the company's platform. A DBA who loves the Internet, knows photography, knows how to cook, and does not know how to cook. It’s not a good driver, didi~
Source of this article: original submission
* Produced by the Aikesheng open source community, original content is not allowed to be used without authorization. Please contact the editor and indicate the source for reprinting.

background

A business system has just been migrated, and the author has just returned home, and the development side encountered a business error "Column'create_time' cannot be null". It can be understood from the literal meaning that the table field'create_time' wants to insert a null value, but the field is reported incorrectly. Cannot be null. This led to thinking about the time parameter of explicit_defaults_for_timestamp.

Concept overview

1. TIMESTAMP and DATETIME

To mention the explicit_defaults_for_timestamp parameter, we must first briefly explain the time data types TIMESTAMP and DATETIME:

TIMESTAMP is a timestamp, ranging from '1970-01-01 00:00:01.000000' UTC to '2038-01-19 03:14:07.999999' UTC.
DATETIME is a combination of date and time, and the range is from '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999'.

Both the TIMESTAMP and DATETIME columns can be automatically initialized and updated to the current date and time. The column can also specify the current timestamp as the default value, the automatically updated value, or both.

2. explicit_defaults_for_timestamp

This system variable determines whether MySQL enables certain non-standard behaviors for the default value of the TIMESTAMP column and the handling of NULL values. By default in MySQL 5.7, explicit_defaults_for_timestamp is disabled, which will enable non-standard behavior. The default value in MySQL8.0 is on. This article defaults to the MySQL5.7 scenario.

See the scene

The business error "Column'create_time' cannot be null" is reported, and null values cannot be inserted into this column. Check the table structure:

#只展示部分时间相关列
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT NULL COMMENT '更新时间',

You can see that the attribute of the create_time column is not null. According to habitual thinking, null should not be inserted into this column. Why is there no problem in the previous environment? After checking the parameters, it is found that the problem lies in the explicit_defaults_for_timestamp parameter. The system did not set the parameter value separately before the migration. According to the official documentation of MySQL 5.7, the default value is OFF at this time. The default MySQL5.7 configuration file of the DMP database operation and maintenance platform, at this time the configuration file is configured with the parameter value of ON.

The parameter is closed on site, changed to OFF, and the test insertion is normal. So why can the parameter value manipulate the default value and null value of the TIMESTAMP column? Continue to test and analyze.

test analysis

1. First, take a look at the official website for a detailed explanation of explicit_defaults_for_timestamp:

(1) If explicit_defaults_for_timestamp=OFF, the server will enable non-standard behavior and process the TIMESTAMP column in the following way:

TIMESTAMP columns that are not explicitly declared with the NULL attribute will automatically be declared with the NOT NULL attribute. It is permissible to assign a NULL value to such a column and set the column to current timestamp.
If the first TIMESTAMP column in the table is not explicitly declared with NULL attribute, DEFAULT attribute or ON UPDATE attribute, it will automatically be declared with DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.
The TIMESTAMP column after the first column, if it is not explicitly declared with the NULL attribute or the explicit DEFAULT attribute, it will be automatically declared as DEFAULT '0000-00-00 00:00:00'. For the inserted row, if no clear value is specified for the column, then the column will be assigned as '0000-00-00 00:00:00' and no warning will occur. Depending on whether strict SQL mode or SQL mode containing NO_ZERO_DATE is enabled, the default value of '0000-00-00 00:00:00' may not be allowed.

Another thing to know is that this non-standard behavior has been deprecated; it is expected to be removed in a future version of MySQL.

(2) If explicit_defaults_for_timestamp=ON, the server will disable non-standard behavior and process the TIMESTAMP column as follows:

It is not possible to insert a NULL value into the TIMESTAMP column, and then automatically set it to the current timestamp. To insert the current timestamp, you need to set the column to CURRENT_TIMESTAMP or a synonym, such as NOW().
TIMESTAMP columns that are not explicitly declared with the NOT NULL attribute are automatically declared with the NULL attribute, and NULL values are allowed. Inserting a NULL value into such a column will set it to the NULL value instead of the current timestamp.
TIMESTAMP columns declared with the NOT NULL attribute do not allow NULL values. For the column specified to insert NULL, if the strict SQL mode is enabled, the result is a single-row insert error, or when the strict SQL mode is disabled, the result of multi-row insert is '0000-00-00 00:00:00'. In any case, assigning NULL to this column will not set it to the current timestamp.
A TIMESTAMP column explicitly declared with the NOT NULL attribute, if there is no explicit DEFAULT attribute, will be considered as having no default value. For the inserted row, if no clear value is specified for such a column, the result depends on the SQL mode. If strict SQL mode is enabled, an error will be reported. If strict SQL mode is not enabled, the column is declared as the implicit default value "0000-00-00 00:00:00", and a warning is issued. This is similar to how MySQL handles other time types (such as DATETIME).

2. It can be seen by doing a test:

（1）explicit_defaults_for_timestamp=OFF ：

mysql> show variables like "%explicit_defaults_for_timestamp%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

Create a table with a timestamp column:

mysql> create table time_off(id int,time timestamp);
Query OK, 0 rows affected (0.02 sec)
mysql> show create table time_off;
+----------+------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------+
| Table    | Create Table                                                                                                                                                                                           |
+----------+------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------+
| time_off | CREATE TABLE `time_off` (
  `id` int(11) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+----------+------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------ ------------------------------+

1 row in set (0.00 sec)

You can see that the timestamp column will have the default attribute'NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' at this time.

Try to insert a NULL value into the table:

mysql> insert into time_off values (1,null);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from time_off;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    1 | 2021-10-12 01:05:28 |
+------+---------------------+
1 row in set (0.00 sec)
 
 
mysql> update time_off set id=2 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from time_off;
+------+---------------------+
| id   | time                |
+------+---------------------+
|    2 | 2021-10-12 01:06:30 |
+------+---------------------+
1 row in set (0.00 sec)

It is found that when a null value is inserted into the timestamp column, it will be inserted normally and automatically converted to the current timestamp. When updating other columns, it will also be updated to the current timestamp based on'ON UPDATE CURRENT_TIMESTAMP'.

（2）explicit_defaults_for_timestamp=ON ：

mysql> show variables like "%explicit_defaults_for_timestamp%";
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| explicit_defaults_for_timestamp | ON    |
+---------------------------------+-------+
1 row in set (0.01 sec)

Create a table with a timestamp column:

mysql> create table time_on(id int,time timestamp);
Query OK, 0 rows affected (0.01 sec)
 
mysql>  show create table time_on;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                           |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| time_on | CREATE TABLE `time_on` (
  `id` int(11) DEFAULT NULL,
  `time` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You can see that the timestamp column will have the default attribute'NULL DEFAULT NULL' at this time.
Try to insert a NULL value into the table:

mysql> insert into time_on values (1,null);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from time_on;
+------+------+
| id   | time |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.00 sec)

You will find that it can be inserted successfully. The inserted value is NULL instead of the current timestamp.

Then under this parameter, what happens when a null value is inserted into the table time_off created when the parameter value is OFF:

mysql> insert into time_off values (3,null);
ERROR 1048 (23000): Column 'time' cannot be null

You will find that the error message'Column'time' cannot be null' is inserted at this time, which conforms to the description of the parameter in the official document. It also proves that the reason for the business test error is that the parameter value of explicit_defaults_for_timestamp is set to ON, which causes the business to insert data failure.

Concluding remarks

Regarding this parameter, it actually standardizes MySQL's time-related operations and makes it more stringent, which is helpful for the standardized use of MySQL, so MySQL will also discard this parameter in the future.

Details determine success or failure. Many students feel that the migration work is familiar, but there is no reasonable migration plan and rigorous business testing. It is really not easy to say that the entire migration process will be smooth and profitable. Sometimes the pits are in the small details. Point.

Failure Analysis | Timestamp column cannot be null after MySQL migration