Mysql field time type timestamp default value is the current time problem, mysqltimestamp

Mysql field time type timestamp default value is the current time problem, mysqltimestamp

Deploy the application on AWS today and find that the program reports an error after the background modification content is submitted. After troubleshooting, it is found that the data is updated, the default value of a timestamp field in a data table is "0000-00-00 00:00:00. 000000 "format, resulting in parsing failure.

The statement for creating 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 should normally be the time format of the current data change

The application environment has been tested in the local development environment. The only difference is that the database in the production environment uses the mysql of aws rds, and the error information is searched, it should be about the problem of mysql parameter configuration.

 

Read the mysql official documentation.

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

In many cases, this is not what we want. How can we disable it?

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

2. The value of "explicit_defaults_for_timestamp" is still OFF. You can disable it in either of the following ways:

1> use the DEFAULT clause to specify a DEFAULT value for this column

2> specify the NULL attribute for this 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 the development environment explicit_defaults_for_timestamp is OFF.

By comparing the mysql parameter in RDS, we find that the value of this parameter is 0, because the default parameter group of mysql In rds is not allowed to be modified, so we create a parameter group, the default parameter group will be inherited by default. At that time, I didn't know how 0 and 1 correspond to on and off, so I changed the value to 1. then restart rds.

This error does not occur.

Change the mysql parameter in rds to the following situation to perfect solution

Explicit_defaults_for_timestamp = 1