useragent statistics groupings are incompatible with MySQL 5.7 default sql_mode (ONLY_FULL_GROUP_BY)

[giangi]

Hello,

I just noticed, by checking why useragent statistics did not seem to be displayed, that current APIs for usergent statistics use a query not compatible with recent changes in MySQL's default sql_mode (see https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html, https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes). Specifically, I have (from ngrep)

T 2015/12/15 12:01:05.268456 127.0.0.1:50464 -> 127.0.0.1:3306 [AP] 5....SELECT id, UNIX_TIMESTAMP(`from_date`) as from_ts, UNIX_TIMESTAMP(`to_date`) as to_ts, useragent, method, COUNT(id) as cnt, SUM(total) as total FROM stats_useragent WHERE (`to_date` BETWEEN FROM_UNIXTIME(1450088318) AND FROM_UNIXTIME(1450174718)) AND ((method = 'INVITE')) GROUP BY useragent order by id DESC

T 2015/12/15 12:01:05.268805 127.0.0.1:3306 -> 127.0.0.1:50464 [AP] �...�..#42000Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'homer_statistic.stats_useragent.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

It may be nice to see if at least the suggested configuration for MySQL can include a proper sql-mode override (removing ONLY_FULL_GROUP_BY, or if future API changes can take into account these "strict" modes.

[adubovikov]

Thanks for your bug report, unfortunately so far I don't see any good solution to be compatible for old and new mysql :-( because ANY_VALUE doesn't exists in the <= 5.6. As a workaround we can recommend to disable ONLY_FULL_GROUP_BY support in the my.cnf.

references: http://johnemb.blogspot.de/2014/09/adding-or-removing-individual-sql-modes.html