Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause (locally only)

[jywarren]

My local instance is seeing the same error we saw on June 7 (#2787 (comment)) that I thought we fixed in #2794

Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'plots.node_revisions.vid' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT node_revisions.* FROM node_revisionsINNER JOINnodeONnode.nid=node_revisions.nid WHERE (type = ('page')) AND (node.status = 1) AND (node_revisions.status = 1) AND (timestamp - node.created > 300) GROUP BY node.title, node.nid ORDER BY timestamp DESC LIMIT 10

On line 127 of home_controller.rb, but really to do with the preceding ActiveRecord call:

plots2/app/controllers/home_controller.rb

Lines 117 to 124 in 6f353b2

	revisions = Revision.joins(:node)
	.order('timestamp DESC')
	.where('type = (?)', 'page')
	.where('node.status = 1')
	.where('node_revisions.status = 1')
	.where('timestamp - node.created > ?', 300) # don't report edits within 5 mins of page creation
	.limit(10)
	.group(['node.title', 'node.nid'])

I'm going to try tweaking locally to get past it.

[jywarren]

Docs:

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

https://stackoverflow.com/questions/34913281/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column

[jywarren]

This seems to say that any column/field we select MUST be in the GROUP_BY call too. Weird, because we don't use select near here, only up on line 93:

plots2/app/controllers/home_controller.rb

Line 93 in 6f353b2

.select('node.*, term_data.*, community_tags.*')

[jywarren]

But ActiveRecord produces a select from our query, the full query being:

SELECT `node_revisions`.* FROM `node_revisions` INNER JOIN `node` ON `node`.`nid` = `node_revisions`.`nid` WHERE (type = ('page')) AND (node.status = 1) AND (node_revisions.status = 1) AND (timestamp - node.created > 300) GROUP BY node.title, node.nid ORDER BY timestamp DESC LIMIT 10

[jywarren]

Also noting that the default for the ONLY_FULL_GROUP_BY setting changed from off to on in MySQL 5.7, so we may have some version mismatch issues here, but i'll fix it on my local anyways. Which is:

mysql Ver 14.14 Distrib 5.7.21, for Linux (armv7l) using EditLine wrapper

[jywarren]

OK, adding a commit to #2925 to resolve this! 4544dc5