1. Wrong screenshot
2. Error analysis
The first TIMESTAMP column in the table (if not declared NULL or if the DEFAULT or ON UPDATE clause is displayed) automatically assigns the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP properties
The TIMESTAMP column after the first (if it is not declared NULL or the DEFAULT clause is displayed) automatically allocates DEFAULT ‘0000-00-00 00:00:00’ (zero TIMESTAMP), which does not satisfy the NO_ZERO_DATE in SQL_mode and gives an error.
Note: THERE are two types of SQL_mode, one is null value, the other is strict mode, and many default Settings will be given. Strict mode is used by default after MySQL5.7.
NO_ZERO_DATE: If this value is set, the MySQL database does not allow insertion of a zero date, which throws an error rather than a warning.
3. Solutions
First, execute select @@sql_mode, copy the value of the query and delete the NO_ZERO_DATE, then execute set SQL_mode = ‘modified value’.
This method takes effect only in the current session
Select @@glob.sql_mode, copy the value of the query and delete the NO_ZERO_DATE, then execute set global SQL_mode = ‘modified value’.
This method takes effect in the current service and expires after the MySQL service is re-run
Sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,
Then restart mysql.
This method is permanent
2. Error analysis
The first TIMESTAMP column in the table (if not declared NULL or if the DEFAULT or ON UPDATE clause is displayed) automatically assigns the DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP properties
The TIMESTAMP column after the first (if it is not declared NULL or the DEFAULT clause is displayed) automatically allocates DEFAULT ‘0000-00-00 00:00:00’ (zero TIMESTAMP), which does not satisfy the NO_ZERO_DATE in SQL_mode and gives an error.
Note: THERE are two types of SQL_mode, one is null value, the other is strict mode, and many default Settings will be given. Strict mode is used by default after MySQL5.7.
NO_ZERO_DATE: If this value is set, the MySQL database does not allow insertion of a zero date, which throws an error rather than a warning.
3. Solutions
First, execute select @@sql_mode, copy the value of the query and delete the NO_ZERO_DATE, then execute set SQL_mode = ‘modified value’.
This method takes effect only in the current session
Select @@glob.sql_mode, copy the value of the query and delete the NO_ZERO_DATE, then execute set global SQL_mode = ‘modified value’.
This method takes effect in the current service and expires after the MySQL service is re-run
Sql_mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,
Then restart mysql.
This method is permanent
Read More:
- Set the default time to the current value in MySQL
- An error was reported in the process of importing the table from mysql5.7: [err] 1067 – invalid default value for ‘***‘
- MySql Error1067:Invalid default value for ‘c_Gender‘
- In SQLite database, set the default value for the field as the current time
- MySQL will delete the data minutes before the current time
- Elasticsearch6. X invalid time range query bug
- mysql workbench Error Code: 1046. No database selected Select the default DB to be used
- Error (1820) if the password is not changed in time after MySQL installation
- The time of the time database displayed by the front end is inconsistent
- A solution to the default invalid host only mode of CentOS virtual machine in VirtualBox
- How to get the current time in java time string
- After IOS upgrade, the time control selected by the wheel will be invalid, uidatepicker
- After switching the tidb database, an error could not commit JDBC transaction appears from time to time
- Error: field * doesn’t have a default value
- 1067 – Invalid default value for ‘sex‘1366 – Incorrect string value: ‘\xE6\x8A\x80\xE6\x9C\xAF…‘ f
- Error 1364 (HY000): field ‘SSL_ cipher’ doesn’t have a default value
- When calling time module – time / datetime in wxPython, an error is reported. Valueerror: unknown locale: zh cn
- MySQL error set: failed to start mysql.service : Unit mysql.service is masked.
- (error when importing database): MySQL 2006-mysql server has gone away
- Question: cannot create windows service for mysql.error : 0 (install MySQL)