MySQL error: [ERR] 1055-expression #1 of ORDER by clause are not in GROUP by clause and contains nonaggregated

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.