Error while creating a new testresult DB

[gdroes]

While executing utils.sql one function fail to be created

CREATE DEFINER=pjcmd_bvt@% FUNCTION pjcmd_bvt._get_ConfValue(strParam varchar(50)) RETURNS varchar(50) CHARSET utf8mb4
BEGIN
#declare buffer for value
declare strValue varchar(50) default "";
#get value into buffer
select ParamValue into strValue from tbl_config where (ParamName=strParam);
#return buffer RETURN TRIM(strValue);
END

Error Code: 1418.
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

[test-fullautomation]

Hi @gdroes,
can you please tell us the relevant environment you are using?
OS, mysql version, ...
Thank you,
Thomas

[gdroes]

Hi @test-fullautomation, currently I use my Windows Docker Desktop environment (v4.20.1) to host a mysql Docker with Version 8.0.

Best regards
Gerhard

[ngoan1608]

Hello Gerhard,

Can you try by adding below line add the beginning of utils.sql file then execute that scrip again?
SET GLOBAL log_bin_trust_function_creators = 1;

Thank you,
Ngoan

[gdroes]

Hi @ngoan1608, the import after running the modified utils.sql throw the following error.

MySQLdb.OperationalError: (1055, "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pjcmd_bvt.tbl_case.component' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

I am using the latest RobotLog2DB Version v1.3.9 (23.06.2023) also.

Best regards
Gerhard

[ngoan1608]

Hi Gerhard,

Please also add below line to disable ONLY_FULL_GROUP_BY mode for above error:
SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));

Thank you,
Ngoan

[gdroes]

Hi @ngoan1608 , sorry. We see the same error again.
MySQLdb.OperationalError: (1055, "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pjcmd_bvt.tbl_case.component' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

[ngoan1608]

Hi Gerhad,

Sorry for this inconvenience.
The restart maybe requires for changing mysql mode.

Please help to double-check that ONLY_FULL_GROUP_BY mode is disable successfully.

• Verify mysql modes is already disabled by above query
  SELECT @@sql_mode;
• ONLY_FULL_GROUP_BY should not be in the query result.
• Restart mysql server then try again.

Thank you,
Ngoan

[ngoan1608]

Hi @gdroes ,

Could you update the status of your DB creation? Is the mysql error solved?

Thank you,
Ngoan

[gdroes]

Hi @ngoan1608, sorry I was busy the last days. Unfortunately my mysql (8.0.33) docker is not running without the global sql_mode ONLY_FULL_GROUP_BY. On session level we can change the mode.
SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
But on global level the docker is not running.

[ngoan1608]

Hi Gerhard,

Is problem solved after disabling ONLY_FULL_GROUP_BY mode?

Thank you,
Ngoan

[gdroes]

Hi @ngoan1608, as I wrote above. The database is not running without this mode. So I am currently not able to check this. I try to create a new environment. But I need some time.
Best regards
Gerhard

[gdroes]

Hi @ngoan1608 , I have checked the import now with
SELECT @@sql_mode;
result['STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION']
But the error is still.
MySQLdb.OperationalError: (1055, "Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pjcmd_bvt.tbl_case.component' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by")

[test-fullautomation]

Hi @ngoan1608 ,

is this a problem which occurs with mysql 8.0? Or is it independent of the mysql version.
When we migrate to our new server I want to migrate also to mysql 8.0 with our WebApp instances.
Might be that we face the same issue?

Thank you,
Thomas

[ngoan1608]

Hi Thomas,

Yes, we will face this issue when migrating to mysql 8.0.
ONLY_FULL_GROUP_BY SQL mode is enabled by default, this is change from mysql 5.7 as following doc https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html.

I hope that disabling ONLY_FULL_GROUP_BY will help but it seems it does not.

I will take a look into the query in _update_evtbl_failed_unknown_per_component store procedure to check the possibility for changing the query to avoid this error.

Hi @gdroes ,
Could you help to check that the store procedure _update_evtbl_failed_unknown_per_component is already created successfully in DB or not?

Thank you,
Ngoan

[gdroes]

Hi @ngoan1608 the stored procedure is already created.

[test-fullautomation]

Hi @ngoan1608 ,
I found that we have places in the code where columns are part of the select statement, but not mentioned in the group by statement.
Maybe it's good to check all procedures/functions and add all up to now not used columns to the end of the group by statement? This should have no side effects and should not be difficult to do.
Thank you,
Thomas

[ngoan1608]

Hi @gdroes ,

I have updated the implement of _update_evtbl_failed_unknown_per_component store procedure to avoid the error message at below branch:
https://github.com/test-fullautomation/testresultwebapp/tree/ntd1hc/bug/error-while-creating-a-new-testresult-db

Could you help to to double-check by checking out that branch and execute the failed_unknown_per_component.sql file (of my branch) to update _update_evtbl_failed_unknown_per_component store procedure?
Then check again with the import tool.

Looking forward to your response!

Thank you,
Ngoan

[test-fullautomation]

Hi @ngoan1608 ,
what is the status of this issue?
Thank you,
Thomas