0

I've recently migrated to mysql 5.7. All queries with "GROUP BY" does not work anymore.

It will helpfull if someone could help me to write this one : 

SELECT * FROM visitors WHERE visitor_country = :visitor_country GROUP BY visitor_ip ORDER BY visitor_date ASC

Thanks,

Have a nice day,

Paul T.
  • 4,703
  • 11
  • 25
  • 29
kwartz
  • 23
  • 1
  • 7
  • 5
    *does not work* could mean anything from "My server caught fire and my data center burned down" to "returned incorrect data" to "threw an error message." If you want help on Stack Overflow *please* be specific about your failure. And, read this about the problem you're having. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html MySQL has, for a long time, had a very confusing nonstandard extension to `GROUP BY`. – O. Jones Dec 04 '17 at 18:17
  • 3
    "MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them." – Patrick Q Dec 04 '17 at 18:17
  • i mean the same query on mysql5.6 does not work on mysql5.7. i've no acces to the mysql settings on the server – kwartz Dec 04 '17 at 18:20
  • Yes Patrick you're right, and so i must rewrite all y queries. But i cant get it work – kwartz Dec 04 '17 at 18:21
  • Well, what are you doing to _try_ to get it to work? Anything in your `SELECT` list must either be an aggregate, or must be in your `GROUP BY` CLAUSE. Same with anything in your `ORDER BY` clause. – Patrick Q Dec 04 '17 at 18:25
  • I've tried this : [code]SELECT visitor_id, visitor_date, visitor_country, visitor_region, visitor_city, visitor_postcode, visitor_latitude, count(visitor_ip) FROM visitors WHERE visitor_country =:visitor_country GROUP BY visitor_id, visitor_ip ORDER BY visitor_date ASC[code] But it returns the entire table – kwartz Dec 04 '17 at 18:32
  • 1
    Sure _before migrating version of any software_ **read the migration notes** – RiggsFolly Dec 04 '17 at 18:49
  • `GROUP BY visitor_id, visitor_ip` I'm not sure you really understand what `GROUP BY` actually does. What is your objective with that clause? – Patrick Q Dec 04 '17 at 18:55
  • i just want to remove duplicate visitor_ip from the results. on mysql5.6 it works fine, not on mysql5.7 – kwartz Dec 04 '17 at 19:17
  • 1
    `GROUP BY visitor_id, visitor_date, visitor_country, visitor_region, visitor_city, visitor_postcode, visitor_latitude` and you are golden. If you have a column in your SELECT clause and you are not aggregating it with a function `Max()`, `AVG()`, `COUNT()` etc then you MUST have it in your GROUP BY. That's the way every other RDBMS has always worked and now finally with 5.7 mysql is defaulting to the same behavior. – JNevill Dec 04 '17 at 20:00
  • To elaborate, pre 5.7 if you had more than one value in one of these non-group-by'd non-aggregated columns Mysql (or rather your storage engine) would arbitrarily pick one of the values to conform to your GROUP BY'd column. That is scary and unpredictable and is no longer the default behavior. – JNevill Dec 04 '17 at 20:01
  • guys, many thanks for your tips, but the problem is not resolved. I try this : SELECT visitor_id, visitor_date, visitor_country, visitor_region, visitor_city, visitor_postcode, visitor_latitude, COUNT(visitor_ip) FROM visitors WHERE visitor_country =: visitor_country GROUP BY visitor_id, visitor_ip ORDER BY visitor_date ASC – kwartz Dec 04 '17 at 20:20
  • @kwartz again: every column in your select and order by must appear in your group by too. or you need to change your server settings. – ysth Dec 04 '17 at 20:36
  • @ysth: yes i did it but i get 18000 rows vs 2500 under mysql5.6. it doesn't work. thx – kwartz Dec 04 '17 at 20:41

1 Answers1

2

Pre-5.7, your query was selecting arbitrary values for date/country/region/etc from those for each visitor_id/visitor_ip, which is presumably not what you wanted. Starting with 5.7, you need to be explicit about what values you want to return. You can just be explicit:

SELECT visitor_id, MAX(visitor_date), MAX(visitor_country), MAX(visitor_region), MAX(visitor_city), MAX(visitor_postcode), MAX(visitor_latitude), MAX(visitor_ip)
FROM visitors
WHERE visitor_country =: visitor_country
GROUP BY visitor_id, visitor_ip
ORDER BY MAX(visitor_date) ASC

Or you can revert to the pre-5.7 settings (even if you can't change the global server settings) by doing

SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION';

after connecting to the database. Though if you are using auto-reconnect, you may want to do it before each problematic query.

ysth
  • 96,171
  • 6
  • 121
  • 214
  • thank you for your suggestion but i do not have all fields extract from the table with this query. Maybe an other way with SELECT DISTINCT? I need to extract all data from the table but not duplicate IP's – kwartz Dec 05 '17 at 08:00
  • After a while, my problem is resolved ! with this : SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION'; Thank you ! – kwartz Dec 19 '17 at 20:34