MySQL5.7 Group BY statement error:
Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘t.road_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The reason I looked for it was because in versions prior to MySQL5.7,
MySQL does not detect functional dependencies,
The only_FULL_group_BY default is not enabled.
Only_full_group_by default is enabled in MYSQL5.7.
So MySQL will reject queries that reference a list, condition, or order,
These queries refer to unnamed non-aggregated columns in a group rather than relying on them functionally.
Solutions:
Find the MySQL configuration file
Find /etc/my.cnf on the server and edit it
Add a sentence to the document:
sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’
Then restart MySQL. LNMP is installed here. Just enter LNMP restart
Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘t.road_name’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The reason I looked for it was because in versions prior to MySQL5.7,
MySQL does not detect functional dependencies,
The only_FULL_group_BY default is not enabled.
Only_full_group_by default is enabled in MYSQL5.7.
So MySQL will reject queries that reference a list, condition, or order,
These queries refer to unnamed non-aggregated columns in a group rather than relying on them functionally.
Solutions:
Find the MySQL configuration file
Find /etc/my.cnf on the server and edit it
Add a sentence to the document:
sql_mode=’STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION’
Then restart MySQL. LNMP is installed here. Just enter LNMP restart
Read More:
- Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated
- Handling of expression not in group by key [value] reported by hive on October 12, 2020
- Syntax error or access violation: 1071 specified key was too long; max key length is 767 bytes
- Group by query only_ FULL_ GROUP_ By error
- SQL query time group_ Was cut by group_ CONCAT()
- LoadRunner error — memory violation: exception access_ Solution
- How to handle when select single is not allowed in loop
- Group by operator of hive execution plan
- MySQL error code 1217 (ER_ROW_IS_REFERENCED): Cannot delete or update a parent row: a foreign key co
- [Java] Java program error: exception_ ACCESS_ VIOLATION (0xc0000005)
- Unhandled exception: 0xc0000005: access violation while reading location 0x00000000
- AR# 57595 – Vivado Synthesis – ERROR: [Synth 8-4169] error in use clause: package ‘xxx‘ not found in
- ?: (staticfiles.E001) The STATICFILES_DIRS setting is not a tuple or list.
- request:fail url not in domain list or Cannot send network request to localhost
- Error code 2: Access violation error.
- Access restriction in Eclipse: the type ‘xxx’ is not API solution
- “Typeerror: List indexes must be integers or slices, not STR” about error reporting solutions
- Dxly solves the fatal error of AutoCAD2006: unhandled access violation reading 0x0000 exception at 6532b0h.
- In windows, “cmake” is not an internal or external command, nor a runnable program or batch file.
- C / C + + error handling (document): unhandled exception: 0xc0000005: access violation while reading location 0x00000000