Regarding the problem of mysql field time type timestamp default value is the current time

Today I deployed the application to AWS and found that the program reported an error after the background modification content was submitted. After investigation, it was found that when updating data, the default value of a timestamp type field in a data table changed to "0000-00-00 00: 00:00.000000" format, causing parsing failure.

The creation statement of this field in mysql is as follows

`XXX` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP Normally it should be the time format of the current data change

Because in the local development environment test However, there is no such problem and the application environment remains unchanged. The only difference is that the production environment database uses MySQL of AWS RDS. After searching the error information, it should be roughly a problem with the MySQL parameter configuration.

Look at the official mysql documentation

By default, the first TIMESTAMP column has both DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP if neither is specified explicitly.

Many times, this is not what we want. How to disable it?

1. Set the value of "explicit_defaults_for_timestamp" to ON.

2. The value of "explicit_defaults_for_timestamp" is still OFF, and there are two ways to disable it

1> Use the DEFAULT clause to specify a default value for the column

2> Specify the NULL attribute for the column.

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

The value of explicit_defaults_for_timestamp in the development environment is OFF

I compared the parameters of mysql in RDS and found that the parameter value is 0. Because the default parameter group of mysql in rds is not allowed to be modified, so I created a parameter group. The default parameter group will be inherited by default. At that time, I didn’t know how 0 and 1 here corresponded to on and off, so I changed the value to 1. Then restart rds.

At this time, I found that there will be no such error.

Change the parameters of mysql in rds to the following situation, perfect solution

explicit_defaults_for_timestamp = 1