When migrating the Django database today, this error occurs when running the program:
[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
I have not encountered such a problem, so I recorded it, lest I meet again later.
First directly on the solution, the principle of analysis in the back, willing to look at, do not want to have a solution, absolutely works.
"" Modify the value of the Sql_mode and remove the only_full_group_by ""
Go to your MySQL:
mysql -uxxx -pxxxxx
Execute command:
set @@global.sql_mode =‘NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;
That's good.
Now say the principle, first of all, how did I get this problem, is that I upgraded my own MySQL to 5.7, and then the original 5.6 of some old grammar on the issue of such or that, after mysql5.7.5, only_full_group_by the default is true, Then the field in select must appear in group by, so it is obvious that the old new version of the problem.
Official documentation here:
A new function, Any_value (), is available so can used to force
MySQL to accept queries that it thinks should is rejected with
Only_full_group_by enabled.The function return value and type are
The same as the return value and type of its argument, but the
function result is a checked for the only_full_group_by SQL mode.
If you find that has only_full_group_by enabled causes queries for existing applications to be rejected, either of thes E actions should restore operation:
If It is possible to modify a offending query, do so, either so
Nondeterministic nonaggregated columns is functionally dependent on
GROUP by columns, or by referring to nonaggregated columns using
Any_value ().
If it is a possible to modify a offending query (for example, if
It is generated by a third-party application), set the Sql_mode
System variable at server startup-not enable only_full_group_by.
For more information on SQL modes and group by queries, see Server SQL modes, and MySQL handling of group by. (Bug #18486310)
MySQL error: [ERR] 1055-expression #1 of ORDER by clause are not in GROUP by clause and contains nonaggregated