Dkan Drupal Error after installing field permission module

Question

After installing the Fields Permissions Drupal module in DKAN, an error displays whenever I try to view my catalog of datasets in DKAN. It is fine for me to view individual datasets by navigating to their individual hyperlink.

Error: SQLSTATE[HY000]: General Error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'dkan_opendatatest.t.changed' which is not in SELECT list: this is incompatible with DISTINCT

Any ideas on how to solve this problem?

Answer 1

This is likely the view that drives the search page. The dropdown expects the field to be a select list but the change in the field makes that view generate a SQL query that isn't supported by MySQL.

You can likely fix this by editing the view at "/admin/structure/views/view/dkan_datasets/edit/panel_pane_1" and changing the "Indexed Node: Date changed" in "Sort criteria". Not sure offhand what the correct "Sort criteria" setting would be with your change but you can hopefully figure it out from there by changing the "Sort criteria". Maybe remove and re-add the "changed" field.

Answer 2

I also got similar errors in Drupal visiting the 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 (and maybe 7 queries too but didnt check). Need to downgrade to Mysql 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. Note: thought removing just ONLY_FULL_GROUP_BY should have fixed, but seems like I needed to remove some of rest too.