Drupal throws MySQL "expression [...] of ORDER BY clause is not in SELECT list" errors

Issue

Under MySQL 5.7 (or higher), a Drupal site sometimes shows errors similar to these:

Uncaught PHP Exception Drupal\Core\Database\DatabaseExceptionWrapper: "SQLSTATE[HY000]: General error: 3065 Expression [... redacted ...] of ORDER BY clause is not in SELECT list, references column '[... redacted ...]' which is not in SELECT list; this is incompatible with DISTINCT: [...]

Resolution

There are 2 options:

• The recommended and best option is to modify any custom or contributed module code to properly include all fields used in ORDER BY clauses in the SELECT field list.
  • The first step to try is to first ensure your contrib modules are up to date; some contrib modules have had this problem fixed in the past (example1, example2).
  • If everything is up to date, you may need to review the code that leads up to the error. You may need to enable "All messages, With Backtrace information" on your Drupal site's admin UI at /admin/config/development/logging.
• Another option is a temporary workaround which configures MySQL to ignore this error by altering the sql_mode MySQL session variable at the time of the Database connection.

For the second option, you can modify your site's settings.php file (around the Acquia "require" line) to something similar to the snippet below.

Important: We can only recommend this for MySQL 5.7. This will cause a fatal error with MySQL 8 (see the note below).

NOTES:

• This code is provided as-is, and could potentially have side effects; we recommend testing this thoroughly before deployment.
• Also, we recommend re-testing this code on any updates or changes to Drupal core and/or the Database backend, especially with upgrades to MySQL 8.0.
• If you need this code still and your MySQL version is 8.0, try removing ,NO_AUTO_CREATE_USER as it no longer is a part of MySQL after that.

Acquia Cloud Platform users

/**
 * NOTE: EDIT and test this snippet; review all sections with [EDIT-ME]
 */
if (file_exists('/var/www/site-php/[EDIT-ME]')) {
  /**
   * Overriden init_commands of $databases on acquia.
   * See https://support.acquia.com/hc/en-us/articles/360005253954-Fixing-database-deadlocks
   */
  global $conf, $databases;
  $conf['acquia_hosting_settings_autoconnect'] = FALSE;
  require '/var/www/site-php/[EDIT-ME]/[EDIT-ME]-settings.inc';

  /**
   * Fix for MySQL 5.7 related DISTINCT and ORDER BY mysql query
   * Override setting of core /docroot/core/lib/Drupal/Core/Database/Driver/mysql/Connection.php
   * Check again when upgrading drupal core
   * Follow Drupal.org issue https://www.drupal.org/project/drupal/issues/2856270
   * The following avoids setting the "ANSI" option for sql_mode.
   */
  $databases['default']['default']['init_commands'] = [
    'sql_mode' => "SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'"
  ];
  acquia_hosting_db_choose_active();
}

Acquia Site Factory Users

Site Factory users will need to split this code between pre-settings.php and the post-settings.php.

pre-settings.php

Drupal 7:

$conf['acquia_hosting_settings_autoconnect'] = FALSE;

Drupal 8:

global $conf, $databases;
$conf['acquia_hosting_settings_autoconnect'] = FALSE;

post-settings.php

Drupal 7 and Drupal 8:

Again, if you need this code still and your MySQL version is 8.0, try removing ,NO_AUTO_CREATE_USER as it no longer is a part of MySQL after that. 

$databases['default']['default']['init_commands'] = [
  'sql_mode' => "SET sql_mode = 'STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER'"
];
acquia_hosting_db_choose_active();

Cause

Drupal in conjunction with newer MySQL versions have adopted certain defaults which can cause this error to be thrown when SELECT queries do not include fields that are then included in ORDER BY clauses. Taking either of the above steps should resolve the issue. The strongest option is the first one, that is, editing your code to remove the need for the settings.php workaround.