Sometimes an unlucky report can create an issue for the DB server. To avoid that an easy to achieve fix would be to add a configurable timeout via max_statement_time to the query. This way the query would abort automatically if going over the time limit. See: https://mariadb.com/kb/en/aborting-statements/
I would suggest to add that to the config file and not the sysprefs. We have top bear in mind that we have to distinguish between mysql select /*+ MAX_EXECUTION_TIME(300) */ sleep(5); which returns normaly when it's running too long and mariadb select /*+ MAX_EXECUTION_TIME(300) */ sleep(5); when throws an errors when running too long
mariadb code SET STATEMENT max_statement_time=1 FOR select sleep(5); unit is second, whereas with mysql it's milliseconds
Created attachment 189691 [details] [review] Bug 39164: Add max_statement_time to SQL report queries Reports may consume too much resources of the db. To avoid that a new config param 'report_sql_max_statement_time_seconds' was created, which sets the maximum time a report statement may take to execute in seconds. If it takes longer it will be aborted. Test plan: 0. if you run this test in ktd you should check if you are running mysql or mariadb, and then be able to switch database 1. create a new report by going to More > Reports > New SQL Report (/cgi-bin/koha/reports/guided_reports.pl?op=add_form_sql) enter the following SQL (which only purpose is to run long) select ExtractValue(bm1.metadata,'//controlfield[@tag="001"]') from biblio_metadata bm1 join biblio_metadata bm2 on ExtractValue(bm1.metadata,'//controlfield[@tag="001"]') = ExtractValue(bm2.metadata,'//datafield[@tag="773"]/subfield[@code="w"]' give the Report a name eg "extract_long" 2. run the report 3. report should be executed and return several 1000 lines on ktd 4. apply patch 5. add a the parameter to koha-conf.xml to the <config> </config> part <report_sql_max_statement_time_seconds>0.001</report_sql_max_statement_time_seconds> OR use the following statemtn sed -i '/<biblioserver>/a <report_sql_max_statement_time_seconds>0.001<\/report_sql_max_statement_time_seconds>' /etc/koha/sites/kohadev/koha-conf.xml 6. restart koha koha-plack --restart kohadev 7. go back to the report page and rerun report which now should lead to an error The following error was encountered: The database returned the following error: Query execution was interrupted, maximum statement execution time exceeded Please check the log for further details. 8. in koha-conf.xml set <report_sql_max_statement_time_seconds>20</report_sql_max_statement_time_seconds> 9. restart koha 10. rerun query, which now should execute as before 11. stop ktd, and restart with the "other" database DB_IMAGE=mysql:8.0 ktd up DB_IMAGE=mariadb:10.5 ktd up 12. repeat from step 1 (omit applying the patch) 13. Sign off 14. Thx
Created attachment 189693 [details] [review] Bug 39164: Add max_statement_time to SQL report queries Reports may consume too much resources of the db. To avoid that a new config param 'report_sql_max_statement_time_seconds' was created, which sets the maximum time a report statement may take to execute in seconds. If it takes longer it will be aborted. Test plan: 0. if you run this test in ktd you should check if you are running mysql or mariadb, and then be able to switch database 1. create a new report by going to More > Reports > New SQL Report (/cgi-bin/koha/reports/guided_reports.pl?op=add_form_sql) enter the following SQL (which only purpose is to run long) select ExtractValue(bm1.metadata,'//controlfield[@tag="001"]') from biblio_metadata bm1 join biblio_metadata bm2 on ExtractValue(bm1.metadata,'//controlfield[@tag="001"]') = ExtractValue(bm2.metadata,'//datafield[@tag="773"]/subfield[@code="w"]') give the Report a name eg "extract_long" 2. run the report 3. report should be executed and return several 1000 lines on ktd 4. apply patch 5. add a the parameter to koha-conf.xml to the <config> </config> part <report_sql_max_statement_time_seconds>0.001</report_sql_max_statement_time_seconds> OR use the following statemtn sed -i '/<biblioserver>/a <report_sql_max_statement_time_seconds>0.001<\/report_sql_max_statement_time_seconds>' /etc/koha/sites/kohadev/koha-conf.xml 6. restart koha koha-plack --restart kohadev 7. go back to the report page and rerun report which now should lead to an error The following error was encountered: The database returned the following error: Query execution was interrupted, maximum statement execution time exceeded Please check the log for further details. 8. in koha-conf.xml set <report_sql_max_statement_time_seconds>20</report_sql_max_statement_time_seconds> 9. restart koha 10. rerun query, which now should execute as before 11. stop ktd, and restart with the "other" database DB_IMAGE=mysql:8.0 ktd up DB_IMAGE=mariadb:10.5 ktd up 12. repeat from step 1 (omit applying the patch) 13. Sign off 14. Thx Signed-off-by: David Nind <david@davidnind.com>
Applied this patch Added -- <report_sql_max_statement_time_seconds>1</report_sql_max_statement_time_seconds> -- to my koha-conf.xml Created a report: -- SELECT b.biblionumber, b.title, bi.publicationyear, it.description AS itemtype_desc, m.tagfield, m.tagsubfield, (SELECT COUNT(*) FROM items i2 JOIN biblioitems bi2 ON i2.biblioitemnumber = bi2.biblioitemnumber WHERE bi2.biblionumber = b.biblionumber) AS item_count, (SELECT COUNT(*) FROM marc_subfield_structure m2 WHERE m2.frameworkcode = b.frameworkcode AND m2.liblibrarian LIKE '%title%') AS title_related_tags, (SELECT COUNT(*) FROM biblio b3 WHERE b3.title LIKE CONCAT('%', b.title, '%')) AS fuzzy_title_overlap FROM biblio b JOIN biblioitems bi ON b.biblionumber = bi.biblionumber JOIN marc_subfield_structure m ON m.frameworkcode = b.frameworkcode CROSS JOIN itemtypes it /* intentional explosion */ WHERE m.liblibrarian LIKE '%a%' /* force full scans */ ORDER BY b.biblionumber, m.tagfield, m.tagsubfield -- When running report I get: -- The following error was encountered: The database returned the following error: Missing SELECT Please check the log for further details. -- This appears to be true for all queries ( tried SELECT * FROM biblio as well )