0

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_command to 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.

2
  • Could you please include the full error text and hyperlink the suggestions that did not work?
    – mona lisa
    Jun 15, 2023 at 23:12
  • This is a bit confusing. Sorry. I had been trying various things as mentioned above to fix this issue; but I had thought they were having no impact. Then Commerce spit out the same error as posted above. I removed the settings I had added for sql_mode to my my.ini and this fixed Commerce. A few days later i revisited the view i was trying to fix and it was now also working. I still require the hack to Views (Sql.php) and I must have managed to disabled the only_full_group_by mode somehow.
    – liquidcms
    Jun 24, 2023 at 4:48

1 Answer 1

-1

You can modify this property by going to your MySQL instance > Server parameters > look for sql_mode property > you can then select/unselect required property values from the drop down like ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES etc

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.