I’ve recently updated my system to MySQL 5.7.9 for a web application I’m developing in PHP. Now I’m facing a problem with a query that used to function correctly in prior MySQL versions. I’m encountering the following error message:
Expression #1 of SELECT list is not in GROUP BY clause and contains non-aggregated column ‘support_desk.mod_users_groups.group_id’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
This is the query that is giving me issues:
SELECT mod_users_groups.group_id AS 'value',
group_name AS 'text'
FROM mod_users_groups
LEFT JOIN mod_users_data ON mod_users_groups.group_id = mod_users_data.group_id
WHERE mod_users_groups.active = 1
AND mod_users_groups.department_id = 1
AND mod_users_groups.manage_work_orders = 1
AND group_name != 'root'
AND group_name != 'superuser'
GROUP BY group_name
HAVING COUNT(`user_id`) > 0
ORDER BY group_name
I don’t quite understand how the only_full_group_by setting functions. Should I simply disable this option or is there an alternative way to adjust my query to fix this issue? What would be the best course of action here?
Hit this exact problem when we upgraded from MySQL 5.6 to 5.7 last year. Your GROUP BY only has group_name but you’re selecting group_id too. MySQL 5.7’s stricter - it won’t let you select non-aggregated columns that aren’t in your GROUP BY.
Looks like you want one row per group_name with user counts. Just wrap group_id in MIN() or MAX() since you’re grabbing any group_id that matches the group_name anyway. Change your SELECT to MIN(mod_users_groups.group_id) AS 'value' and keep the same GROUP BY. This tells MySQL which group_id to pick when multiple rows share the same group_name. Way cleaner than adding group_id to GROUP BY if you don’t actually need to group by it.
I’ve been through this nightmare during our system migration. Instead of fighting SQL modes and fixing queries every time MySQL gets pickier, I automated the whole database layer.
Built a Latenode workflow that handles all our database ops. When MySQL throws compliance errors, it detects the problem and applies the fix - adjusts GROUP BY clauses, uses ANY_VALUE(), or restructures queries.
Works across MySQL versions without breaking. No more manual debugging when you upgrade. Set the logic once and it handles the edge cases.
Saved us tons of troubleshooting time. Way better than patching queries or messing with SQL modes.
don’t disable only_full_group_by - it exists for good reason. since you’re selecting group_id, add it to your GROUP BY clause: GROUP BY group_name, mod_users_groups.group_id. this fixes the error and keeps your query sql compliant.
The only_full_group_by mode makes MySQL follow SQL standards - you can’t select columns that aren’t in your GROUP BY clause unless they’re functionally dependent on the grouped columns. Your query picks group_id but only groups by group_name. If multiple records have the same group_name, MySQL doesn’t know which group_id to return. Don’t disable the mode. Just fix your query by grouping on both: GROUP BY group_name, mod_users_groups.group_id. If group_name is actually unique for each group_id, you could use ANY_VALUE(mod_users_groups.group_id) instead. But I’d go with the first option - it’s more reliable in production and doesn’t rely on MySQL-specific functions.