14,953
edits
Troubleshooting of MySQL errors: Difference between revisions
Jump to navigation
Jump to search
→Error 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column
| Line 281: | Line 281: | ||
=== | === ERROR 1055: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column === | ||
Message | Message | ||
<pre> | <pre> | ||
| Line 304: | Line 304: | ||
(2, 'A123', 'Bob', 'Great!', 5, '2024-01-02'), | (2, 'A123', 'Bob', 'Great!', 5, '2024-01-02'), | ||
(3, 'A123', 'Bob', 'Nice!', 3, '2024-01-03'); | (3, 'A123', 'Bob', 'Nice!', 3, '2024-01-03'); | ||
-- This will cause an error | |||
SELECT | |||
id, -- Problem: Not in GROUP BY | |||
post_id, | |||
author, | |||
GROUP_CONCAT(content SEPARATOR "\n") AS content, -- This is OK as it uses aggregate function | |||
likes -- Problem: Not in GROUP BY | |||
FROM posts | |||
GROUP BY post_id, author; | |||
</pre> | </pre> | ||
Solution: Fix the query by either (1) including all columns in GROUP BY or (2) using aggregate functions (like MAX, MIN, SUM, COUNT, GROUP_CONCAT) | Solution: Fix the query by either (1) including all columns in GROUP BY or (2) using aggregate functions (like MAX, MIN, SUM, COUNT, GROUP_CONCAT) | ||
<pre> | <pre> | ||
-- Method 1: Add all columns to GROUP BY | -- Method 1: disable `sql_mode=only_full_group_by` | ||
``` | |||
There are two ways to disable `sql_mode=only_full_group_by`: | |||
1. Temporary Change (Only affects current session) | |||
```sql | |||
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY','')); | |||
``` | |||
2. Permanent Change (Modify MySQL configuration file) | |||
- Locate MySQL configuration file (usually `my.cnf` or `my.ini`) | |||
- Add or modify in the `[mysqld]` section: | |||
```ini | |||
[mysqld] | |||
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | |||
``` | |||
Important Notes: | |||
1. After modifying the configuration file, MySQL service needs to be restarted to take effect | |||
2. Disabling this setting is not recommended because: | |||
- May lead to unpredictable query results | |||
- Violates SQL standards | |||
- May cause compatibility issues in future versions | |||
The recommended approach is to fix the SQL query rather than changing the SQL mode. | |||
``` | |||
-- Method 2: Add all columns to GROUP BY | |||
SELECT | SELECT | ||
id, | id, | ||
| Line 318: | Line 355: | ||
GROUP BY post_id, author, id, content, likes; | GROUP BY post_id, author, id, content, likes; | ||
-- Method | -- Method 3: Use aggregate functions for non-GROUP BY columns | ||
SELECT | SELECT | ||
MAX(id) as id, | MAX(id) as id, | ||