MySQL Server SQL modes

My colleague recently encountered a Drupal error as follow:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1140 In aggregated query without GROUP BY, expression #3 of SELECT list contains nonaggregated column 'n.b'; this is incompatible with sql_mode=only_full_group_by: SELECT n.id, COUNT(n.a), n.b FROM table AS n WHERE n.id=1;

At first glance, it seems simple enough. The query does not have a GROUP BY for n.b. As the query was generated from Drupal views, he could not add in the GROUP BY clause. According to MySQL documentation, it is likely the ONLY_FULL_GROUP_BY mode was enabled.

My first thought was to do a sanity check on MySQL:

> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                         |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


> SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                        |
+-------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

It shows that the ONLY_FULL_GROUP_BY was not enabled. That was odd. So I asked my colleague to print out the query and I ran it directly on the MySQL CLI. To my surprise, the query worked fine. It seems that the particular MySQL server instance itself does not have issues with the query.

Suspecting it has something to do with Drupal, I checked into the MySQL driver code instead. This seems fine too at first glance, as I did not see the connection option sending in the ONLY_FULL_GROUP_BY mode.

public static function open(array &$connection_options = []) {
    ...
    $connection_options += [
      'init_commands' => [],
    ];

    $connection_options['init_commands'] += [
      'sql_mode' => "SET sql_mode = 'ANSI,TRADITIONAL'",
    ];

It is on further reading of the documentation that I found the mode ANSI is a combination mode that and it says:

Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, and ONLY_FULL_GROUP_BY.

Thus the cause was the ANSI mode. I advised my colleague on this and suggest that if he can't change the query, the only workaround is to change the connection option:

$connection_options['init_commands'] += [
    'sql_mode' => "SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,TRADITIONAL'",
];

He tried and it worked!