We have the following error with MySQL 5.6, while MySQL 5.7 has no problems with the SQL statement.
Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1055 'database.r.revision_timestamp' isn't in GROUP BY: SELECT t.vid AS vid FROM (SELECT r.vid AS vid, r.revision_timestamp AS revision_timestamp FROM {node_field_data} n INNER JOIN {node_revision} r ON r.nid = n.nid WHERE (type = :db_condition_placeholder_1) AND (n.nid = :db_condition_placeholder_2) AND (changed < :db_condition_placeholder_3) AND ((n.vid <> r.vid)) GROUP BY n.nid, r.vid ORDER BY revision_timestamp DESC LIMIT 9223372036854775807 OFFSET 0) t WHERE revision_timestamp < :db_condition_placeholder_0; Array ( [:db_condition_placeholder_0] => 1568794622 [:db_condition_placeholder_1] => artikel [:db_condition_placeholder_2] => 47391 [:db_condition_placeholder_3] => 1566116222 ) in Drupal\node_revision_delete\NodeRevisionDelete->getCandidatesRevisions() (line 331 of docroot/modules/contrib/node_revision_delete/src/NodeRevisionDelete.php).

The inner query has a missing group by statement:

SELECT r.vid AS vid, r.revision_timestamp AS revision_timestamp
FROM {node_field_data} n
INNER JOIN {node_revision} r ON r.nid = n.nid
WHERE (type = :db_condition_placeholder_1) AND (n.nid = :db_condition_placeholder_2) AND (changed < :db_condition_placeholder_3) AND ((n.vid <> r.vid))
GROUP BY n.nid, r.vid
ORDER BY revision_timestamp DESC
LIMIT 9223372036854775807 OFFSET 0

The select is on vid and revisions so the group by should be on those fields as well.

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

pdenooijer created an issue. See original summary.

pdenooijer’s picture

Following patch adds the missing group by statement, giving me the same results on 5.7 and fixing the bug on 5.6.

adriancid’s picture

Status: Active » Fixed

Thanks, this is not related to the mysql version, is related with the ONLY_FULL_GROUP_BY mode.

pdenooijer’s picture

Thanks, that is quick :). Ah, wrong conclusion from my part.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

davidburns’s picture

I'm still getting this error w/ 8.x-1.0-beta3 release. To fix this error I had to add these 2 patches.

https://www.drupal.org/files/issues/2019-09-18/node_revision_delete-fix_...
https://www.drupal.org/files/issues/2019-10-08/3078433-5.patch

adriancid’s picture

Thanks @davidburns

richardbporter’s picture

I've got the same issue described in #7. The patches mentioned in that comment do resolve it.