Is it possible to disable ONLY_FULL_GROUP_BY in MySQL 8?
I saw suggestions on how to do this on the MySQL server directly, as well as hacking up Drupal core, but none of them have been successful so far. I suspect either because the options do not apply to MySQL 8 or Drupal is hard coded to force this option (even though I cannot find any reference to this in core).
The issue comes from a view not properly handling duplicate results. A common solution is to use hook_views_query_alter() to add a GroupBy clause. This works well unless your view has a sort. In that case Views code inserts extra GroupBys for each field in the sort to prevent getting an error for violating the ONLY_FULL_GROUP_BY rule within MySQL. These extra GroupBys break the purpose of the added GroupBy and the view returns duplicates.
I can easily make a Views patch to provide a view option to not add the aggregate GroupBys; but then the view fails with the SQL error. Even though pasting this resulting SQL directly into the database console does not give an error and returns the correct results.
In hook_views_query_alter(), I could use the following code.
case 'policies_by_user':
// Fix duplicates.
$query->addField('node_field_data', 'nid', '', ['function' => 'groupby']);
$query->addGroupBy('node_field_data.nid');
break;
That code adds this GROUP BY to the view query.
GROUP BY node_field_data.nid,
node__field_policy_effective_date_field_policy_effective_dat,
field_policy_node_field_data_completed
The two additional terms are from the sort fields added in the view and forced by the Views module to meet the ONLY_FULL_GROUP_BY requirements.
Modifying the Sql.php file where it Assembles the group by clause fixes it; the additional GROUP BY elements are not added. This generates an error, although it creates the correct query.
SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sia.node__field_policy_effective_date.field_policy_effective_date_value' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
GROUP BY clause and contains nonaggregated column and incompatible with sql_mode=only_full_group_by suggests a couple of different methods to use with Drupal.
- Add
init_commandto the database connection array in the settings.php file - Hack the SQL mode setting in the Connection.php file (although the code mentioned to hack it is no longer there)
Alternatively, there is the server approach mentioned on Disable ONLY_FULL_GROUP_BY to modify @@sql_mode directly in SQL and remove the ONLY_FULL_GROUP_BY option. In my MySQL 8 setup, that is not listed as an option, though.
