Cause of error

The default value incompatibility problem caused by mysql5.7 version, the same problem may also occur in mysql8.0.

The problematic values ​​are:
NO_ZERO_IN_DATE
In strict mode, the date and month are not allowed to be zero.

NO_ZERO_DATE
Set this value, mysql database does not allow the insertion of a zero date, and inserting a zero date will throw an error instead of a warning.

ONLY_FULL_GROUP_BY
For the GROUP BY aggregation operation, if the column in the SELECT does not appear in the GROUP BY, then this SQL is illegal because the column is not in the GROUP BY clause.

Solution

method one

Execute select @@sql_mode, copy the value of the query and delete NO_ZERO_DATE, and then execute set sql_mode = ‘modify number’.

This method only works in the current session

Way two

Execute select @@global.sql_mode, copy the value of the query and delete NO_ZERO_DATE, and then execute set global sql_mode = ‘modify number’.

This method takes effect in the current service, and it becomes invalid after restarting the MySQL service

Method Three

In the mysql installation directory, open the my.ini or my.cnf file. Under wamp, SQL_MODE is not set in MySQL 5.7.

1. Found in my.ini file [mysqld]

2. If there is no SQL_MODE, add it, and modify it if it has

sql_mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

or

sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Finally restart MySQL

service mysqld restart

Read More:

• Mysql ERROR 1067: Invalid default value for ‘date’ [How to Solve]
• [Solved]ERROR 1067 (42000): Invalid default value for ‘end_time‘ Mysql
• [Solved] MySQL5.6.44 [Err] 1067 – Invalid default value for create_date settlement programme
• MYSQL Create TIMESTAMP and Save Error: ERROR 1067 (42000): Invalid default value for ‘last_updated_on’
• How to Solve MySQL version 5.7+ Group by group error
• How to Solve can‘t connect to local mysql server through socket ‘/var/lib/mysql/mysql.sock‘
• How to Solve MYSQL Error: Failed to start MySQL 8.0 database server
• Docker: How to Solve MYSQL8 & Navicat remote connection error
• How to Solve MYSQL into outfile Error13
• How to Solve Mysql8 load data error
• How to Solve c3p0 connect mysql8.0 Error
• [MySQL] mysql 5.5 and 5.6 timestamp default default value CURRENT_TIMESTAMP problem
• How to Fix MySQL ERROR 1130 (HY000): Host ‘XXXX’ is not allowed to connect to this MySQL server
• How to Solve MYSQL Error: No module named MySQLdb
• How to Solve MYSQL ERROR: relation “table_name” does not exist
• mysql workbench Error Code: 1046. No database selected Select the default DB to be used by doubl
• Mysql :error 1111. Invalid use of group function
• [Solved] ERROR 2002 (HY000): Can’t connect to local MySQL server through socket’/var/lib/mysql/mysql.sock’ (2)
• MySQL Install Error: MySQL error 1042: Unable to connect to any of the specified MySQL hosts
• [Solved] MySQL: datetime (0) null default NULL