[Mysql 8] Cerberus no compliant

[mflorid]

Adeo do the migration to Mysql 8 last week. We have encouter some trubles that have contrain us to roll back.

Here is the détails :

We have SQL errors linked to incompatibility with MySQL 8

Case 1

Query that comes from cerberus testing github

SELECT * FROM testdatalib tdl
LEFT OUTER JOIN testdatalibdata tdd ON tdl.TestDataLibID = tdd.TestDataLibID and tdd.Subdata=''
WHERE `name` LIKE 'ELIGIBILITY-DATA'
and (`system` = '' or `system` = '')
and (`environment` = '' or `environment` = '')
and (`country` = '' or `country` = '')
ORDER BY `name` DESC, system DESC, environment DESC, country DESC, tdl.TestDataLibID ASC
LIMIT 1 

Executed on MySQL 5.6: OK

Executed on MySQL 8.0.30:

SQL Error [1064] [42000]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system DESC, environment DESC, country DESC, tdl.TestDataLibID ASC
LIMIT 1' at line 7 
Error reported by Cerberus logs

ERROR TestDataLibDAO:197 - Unable to execute query : com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system DESC, environment DESC, country DESC, tdl.TestDataLibID ASC LIMIT 1' at line 1 

Other cases

May 30 08:55:31 instance-cerberus-front-prod tomcat9[1427200]: 2023-05-30 08:55:31 ERROR TestCaseDAO:868 - Unable to execute query : java.sql.SQLSyntaxErrorException: Expression #
53 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'cerberus_prod.tel.Id' which is not functionally dependent on columns in GROUP BY clause; this is inc
ompatible with sql_mode=only_full_group_by 

May 30 08:36:17 instance-cerberus-front-prod tomcat9[199991]: Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: 'N'
May 30 08:36:17 instance-cerberus-front-prod tomcat9[199991]:         at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) ~[mysql-connector-java-5.1.47-bin.jar:5.1.47]

[vertigo17]

Hello @mflorid can you confirm which version of Cerberus you use ?
MySQL 8 require at least v4.15
I did perform a 1st fix on the 1st error.
I will check the other later this week.

[mflorid]

Hello @vertigo17

We are in 4.16. We didnt check all the app maybe there is more request impacted.

[vertigo17]

duplicate of #2251