How to Solve mysql [Err] 1067-Invalid default value for

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:

Leave a Reply

Your email address will not be published. Required fields are marked *