CMS MADE SIMPLE FORGE

CMS Made Simple Core

 
Back Back to List

[#12443] Admin Search fails on some searches with default mysql mode only_full_group_by (mysql 5.7.5+)

avatar
Created By: Matt Hornsby (DIGI3) (DIGI3)
Date Submitted: Sat Apr 24 11:15:36 -0400 2021

Assigned To: Ruud van der Velden (ruudvdvelden)
Version: 2.2.15
CMSMS Version: 2.2.15
Severity: None
Resolution: Fixed
State: Open
Summary:
Admin Search fails on some searches with default mysql mode only_full_group_by (mysql 5.7.5+)
Detailed Description:
Some searches in Admin Search were yielding no results when they should have.
Error log showed it was the new default mysql mode of only_full_group_by

MySQL 5.7.5 and later implements detection of functional dependence. If the
ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects
queries for which the select list, HAVING condition, or ORDER BY list refer to
nonaggregated columns that are neither named in the GROUP BY clause nor are
functionally dependent on them. (Before 5.7.5, MySQL does not detect functional
dependency and ONLY_FULL_GROUP_BY is not enabled by default. For a description
of pre-5.7.5 behavior, see the MySQL 5.6 Reference Manual.)

Workaround is to change the mysql mode to exclude only_full_group_by, but for
many users this would require the host to make a change they're unwilling/unable
to make.

(note: some modules, including FEU/MAMS, also have this issue)


History

Comments
avatar 
Date: 2021-05-07 19:44
Posted By: tom (tomphantoo)

All relevant db queries could be retrofitted with
  ANY_VALUE(column)
per https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html

BUT is laborious, and might not work with Mariadb.

Maybe a tweak in the system database connection constructor e.g.
$this->_mysql->query("SET SESSION sql_mode=(SELECT
REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))");

And might also need some ',' cleanup in the new sql_mode value.
avatar 
Date: 2021-05-13 16:26
Posted By: Ruud van der Velden (ruudvdvelden)

I've changed the code to make it search more attributes and fixed the problem on
the go
Updates

Updated: 2021-06-12 12:06
resolution_id: 5 => 7

Updated: 2021-06-11 18:29
resolution_id: => 5
assigned_to_id: 100 => 18365