Ubuntu 19.10 MySQL 8 and 'grouping' keyword in OptionGroup.php
CiviCRM 5.19.3 Joomla 3.9.11 Ubuntu 19.10
Updating Ubuntu from 19.04 to 19.10 will pull in MySQL v8. It is a known issue (#392 (closed)) that grouping
now became a reserved word which caused some problems.
I found an instance of this in OptionGroup.php
. On line 124 (https://lab.civicrm.org/dev/core/blob/master/CRM/Core/OptionGroup.php#L124) :
SELECT v.{$labelColumnName} as {$labelColumnName} ,v.{$keyColumnName} as value, v.grouping as grouping
This caused our whole site to DB Error syntax error
since the first page is an event listing.
I did not have the time to figure out if there actually was some further use of that identifier, since the full query is built in run-time. Seems like an easy fix anyway.
Just single-quoting the grouping-word on that line brought it back for us. And a quick test seems to indicate that the site works ok.
I'm volunteering to staying on MySQL 8 for now to flesh out any further issues. Worst case, I'll downgrade MySQL.