Bug 39164

Summary: Add max_statement_time to SQL report queries
Product: Koha Reporter: Katrin Fischer <katrin.fischer>
Component: ReportsAssignee: Mark Hofstetter <koha>
Status: Failed QA --- QA Contact: Kyle M Hall (khall) <kyle>
Severity: enhancement    
Priority: P5 - low CC: cornelius.hertfelder, david, koha, kyle, markus.john, nick
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=33116
GIT URL: Initiative type: ---
Sponsorship status: --- Comma delimited list of Sponsors:
Crowdfunding goal: 0 Patch complexity: ---
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Circulation function:
Attachments: Bug 39164: Add max_statement_time to SQL report queries
Bug 39164: Add max_statement_time to SQL report queries

Description Katrin Fischer 2025-02-19 14:51:52 UTC
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/
Comment 1 Mark Hofstetter 2025-11-18 01:17:45 UTC
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
Comment 2 Mark Hofstetter 2025-11-18 01:19:23 UTC
mariadb code

SET STATEMENT max_statement_time=1 FOR select sleep(5);

unit is second, whereas with mysql it's milliseconds
Comment 3 Mark Hofstetter 2025-11-19 01:22:40 UTC Comment hidden (obsolete)
Comment 4 David Nind 2025-11-19 03:14:17 UTC
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>
Comment 5 Kyle M Hall (khall) 2025-12-10 11:40:15 UTC
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 )