mysql5.7 error this is incompatible with sql_mode=only_full_group_by
The latest version of mysql5.7.x is downloaded and installed. The only_full_group_by mode is enabled by default, but after this mode is enabled, the original group by statement will report an error, and then it will be removed.
Once only_full_group_by is turned on, it feels that group by will become the same as distinct. Only the field information affected by it can be obtained, and it cannot coexist with other fields that are not affected by it. In this way, the function of group by will become very narrow.
It is better to enable only_full_group_by mode.
Because there is a function in mysql: any_value(field) allows the appearance of non-grouping fields (same effect as turning off only_full_group_by mode).
Specific error message:
[Err] 1055 – Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
- View sql_mode select @@global.sql_mode;
The queried value is: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
- Remove ONLY_FULL_GROUP_BY and reset the value. set @@global.sql_mode =’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’;
- The above is to change the global sql_mode, which is valid for the newly created database.