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:
In strict mode, the date and month are not allowed to be zero.
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.
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.
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
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
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
Finally restart MySQL
service mysqld restart
- How to Solve MySQL version 5.7+ Group by group error
- [Solved] MySQL: Syntax error or access violation: 1055 Expression #1 of SELECT list is not …
- MYSQL gruop by Error: this is incompatible with sql_mode=only_full_group_by
- [Solved] MYSQL 5.7 gruop by eRROR: this is incompatible with sql_mode=only_full_group_by
- Mysql ERROR 1067: Invalid default value for ‘date’ [How to Solve]
- [Solved] MySQL5.6.44 [Err] 1067 – Invalid default value for create_date settlement programme
- [Solved] Sequelize DatabaseError: ER_WRONG_FIELD_WITH_GROUP: Expression #2 of SELECT list is not in GROUP
- [Solved] ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregate
- Dbeaver Import SQL File Error: Error executing process Process failed (exit code = 1). See error log. Process failed (exit code = 1). See error log.
- Mysql :error 1111. Invalid use of group function
- [Solved] Access denied for user ‘root‘@‘localhost‘ (using password: YES)
- After installing mysql5.7 on centos7, an error 1045 (28000) is reported: access denied for user ‘root’ @’localhost ‘(using PAS)
- [MySQL] The principle of group by aggregation function and the reason for aggregation limitation SELECT list is not in GROUP BY clause and contains nonaggregated column
- MYSQL Error 1045 (28000): access denied for user ‘root’ @’localhost ‘(using password: Yes)
- Mysql220.127.116.11 Error 1054 (42S22): Unknown Column’password’ In’field List’ When Changing User Password
- MySQL Install Error: MySQL error 1042: Unable to connect to any of the specified MySQL hosts
- Introduction to Relational Databases in SQL
- [Solved] There is a problem with mysql8.0 without password in Navicat connection
- SQL Server Group sort de duplication row_ number() over ( PARTITION BY t1.col_ 2 ORDER BY 1 )
- [Solved] SQL Error: Method queryTotal execution error of sql