Errors due to MySQL8 ONLY_FULL_GROUP_BY setting

[2SJH]

With a new installation on Ubuntu 20.04, I've had issues with the rank keyword, but after working through that I'm finding that autosuggest doesn't work. The Javascript console reveals this:

Uncaught SyntaxError: expected expression, got '<'
setSuggestions http://localhost/jethro/resources/js/bsn_autosuggest.js?t=1634468773:186

This is caused by an uncaught exception in a HTTP request

GET | http://localhost/jethro/?call=find_person_json&search=h

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pp.first_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Line 162 of File /home/www/html/jethro/include/jethrodb.php

This can be overcome by turning off the ONLY_FULL_GROUP_BY mode in MySQL

This command will temporarily clear the flag, allowing the query to work:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

[tbar0970]

Another way of working around this for now is to enable the STRICT_MODE_FIX setting in conf.php

// Whether to explicity turn of MySQL's strict mode.
// Enable this if you experience strict-mode-related database errors and can't adjust your central Mysql config.
define('STRICT_MODE_FIX', TRUE);

[tbar0970]

Can you confirm your MySQL version?

[2SJH]

Thanks for the alternative solution. I'm using mysql 8.0.26 as packaged in ubuntu 20.04.02

I found when trying the STRICT_MODE_FIX it also gives an error:

Syntax error or access violation: 1231 Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' in /home/www/html/jethro/include/init.php:44 Stack trace: #0 /home/www/html/jethro/include/init.php(44):

It seems that my install of mysql also doesn't accept the NO_AUTO_CREATE_USER flag in the sql_mode. If I remove that flag from init.php, then it works fine.

The default sql_mode for my version is:

select @@sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |

[tbar0970]

Thanks for the info. I've done some reading which is telling me that

NO_AUTO_CREATE_USER is enabled by default in MySQL 5.7 but not supported in MySQL 8.0.

so as well as the problem you report in this issue, it looks like STRICT_MODE_FIX also needs adjusting.

[tbar0970]

Thanks for reporting. Overall diagnosis is:

• There are bunch of places where Jethro relies on MySQL's previous 'relaxed' approach about GROUP BY, so Jethro can't operate with ONLY_FULL_GROUP_BY enabled (without a lot of work to rewrite queries).
• STRICT_MODE_FIX was out of date - it referred to various settings that no longer exist in MySQL
• Therefore I've removed the STRICT_MODE_FIX config option, and replaced it with an SQL_MODE setting which can be used to set the sql mode when required. Explained in conf.php.sample
• It would be most efficient to disable ONLY_FULL_GROUP_BY via my.conf for those who have access to it.

[tbar0970]

I've now switched my dev environment to mysql8 (rather than mariaDB) to catch these issues