I need help, because my forums become unusable when i activate the Advanced Forums module. It just display this message:

PDOException: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column '[drupalDB].lnsc.last_comment_timestamp' which is not in SELECT list; this is incompatible with DISTINCT: SELECT ncs.last_comment_timestamp AS last_comment_timestamp, ncs.last_comment_uid AS last_comment_uid, n.nid AS nid, n.title AS title, n.type AS type, CASE ncs.last_comment_uid WHEN 0 THEN ncs.last_comment_name ELSE u.name END AS last_comment_name FROM {node} n INNER JOIN (SELECT DISTINCT lnsc.nid AS nid FROM {node_comment_statistics} lnsc ORDER BY last_comment_timestamp DESC LIMIT 10000 OFFSET 0) lastnodes ON n.nid = lastnodes.nid INNER JOIN {forum_index} f ON n.nid = f.nid AND f.tid = :tid INNER JOIN {node_comment_statistics} ncs ON n.nid = ncs.nid INNER JOIN {users} u ON ncs.last_comment_uid = u.uid WHERE (n.status = :db_condition_placeholder_0) ORDER BY last_comment_timestamp DESC LIMIT 1 OFFSET 0; Array ( [:db_condition_placeholder_0] => 1 [:tid] => 2 ) in advanced_forum_forum_load() (line 224 of [website]\sites\all\modules\advanced_forum\includes\core-overrides.inc).

When i just have forums enabled everything appears just fine.
I've search all over the internet but i didn't find anyone with a similar problem and/or solution.

CommentFileSizeAuthor
#2 mysql_57_group_by-2638760.patch607 bytesaberan
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

Pineapplez created an issue. See original summary.

aberan’s picture

aberan CreditAttribution: aberan commented
FileSize
mysql_57_group_by-2638760.patch607 bytes

The sql error comes from mysql 5.7+. Specifically ONLY_FULL_GROUP_BY mode being set as default. You can see the changes list here - http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html#mysql-nutshel....

I've attached a patch that should resolve the issue if you are running mysql 5.7+. There shouldn't be any issues with older versions of mysql.

mccrodp’s picture

mccrodp CreditAttribution: mccrodp at X-Team commented

Thank you for this @aberan. I had a NodeQueue module error on View pages, giving a similar SQL error. I downgraded to MySQL 5.6 (as I don't need 5.7) and no longer have this issue!

armyofda12mnkeys’s picture

armyofda12mnkeys CreditAttribution: armyofda12mnkeys commented

I also got same errors on admin/content and admin/people pages:
Exception in People[user_admin_people]: SQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'mycmsschema.users_field_data.created' which is not in SELECT list; this is incompatible with DISTINCT

The query it outputted worked fine in Mysql Workbench but not running in Drupal8 when visiting those pages.

Mysql 5.7 doesn't seem to like Drupal8 queries. I think need to downgrade to 5.6 (so doesn't default to super strict sql_mode settings like ONLY_FULL_GROUP_BY which are the default in Mysql 5.7) or go into core/lib/Drupal/Core/Database/Driver/mysql/Connection.php
and change:
/*
'sql_mode' => "SET sql_mode = 'ANSI,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,ONLY_FULL_GROUP_BY'",
*/
to:
'sql_mode' => "SET sql_mode = 'ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'",
which worked for me.