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.
Comment | File | Size | Author |
---|---|---|---|
#2 | node_revision_delete-fix_candidate_nodes_group_by-3082021-2.patch | 622 bytes | pdenooijer |
Comments
Comment #2
pdenooijer CreditAttribution: pdenooijer at Ordina Digital Services for RTL Nieuws commentedFollowing patch adds the missing group by statement, giving me the same results on 5.7 and fixing the bug on 5.6.
Comment #4
adriancidThanks, this is not related to the mysql version, is related with the ONLY_FULL_GROUP_BY mode.
Comment #5
pdenooijer CreditAttribution: pdenooijer at Ordina Digital Services for RTL Nieuws commentedThanks, that is quick :). Ah, wrong conclusion from my part.
Comment #7
davidburnsI'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
Comment #8
adriancidThanks @davidburns
Comment #9
richardbporter CreditAttribution: richardbporter as a volunteer commentedI've got the same issue described in #7. The patches mentioned in that comment do resolve it.