Bug 39164 - Add max_statement_time to SQL report queries
Summary: Add max_statement_time to SQL report queries
Status: Needs Signoff
Alias: None
Product: Koha
Classification: Unclassified
Component: Reports (show other bugs)
Version: Main
Hardware: All All
: P5 - low enhancement
Assignee: Mark Hofstetter
QA Contact: Kyle M Hall (khall)
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2025-02-19 14:51 UTC by Katrin Fischer
Modified: 2026-02-15 10:02 UTC (History)
8 users (show)

See Also:
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 (9.45 KB, patch)
2025-11-19 01:22 UTC, Mark Hofstetter
Details | Diff | Splinter Review
Bug 39164: Add max_statement_time to SQL report queries (9.50 KB, patch)
2025-11-19 03:14 UTC, David Nind
Details | Diff | Splinter Review
Bug 39164: Strip the wrapper before validaing the SQL (853 bytes, patch)
2026-01-29 17:30 UTC, Lucas Gass (lukeg)
Details | Diff | Splinter Review
Bug 39164: Add max_statement_time to SQL report queries (5.32 KB, patch)
2026-02-01 15:38 UTC, Mark Hofstetter
Details | Diff | Splinter Review
Bug 39164: Add max_statement_time to SQL report queries (6.82 KB, patch)
2026-02-06 10:11 UTC, Mark Hofstetter
Details | Diff | Splinter Review
Bug 39164: Add max_statement_time to SQL report queries (5.92 KB, patch)
2026-02-11 14:52 UTC, Lucas Gass (lukeg)
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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 )
Comment 6 Lucas Gass (lukeg) 2026-01-29 17:30:18 UTC
Created attachment 192172 [details] [review]
Bug 39164: Strip the wrapper before validaing the SQL
Comment 7 Lucas Gass (lukeg) 2026-01-30 19:30:43 UTC
Obsoleted my patch here because, after thinking about it, it would not be good to strip SQL and then validate. 

Setting back to FQA, sorry for the noise.
Comment 8 Mark Hofstetter 2026-02-01 15:38:43 UTC
Bug Update for Bug 39164 - Add max_statement_time to SQL report queries
Comment 9 Mark Hofstetter 2026-02-01 15:38:45 UTC Comment hidden (obsolete)
Comment 10 Mark Hofstetter 2026-02-06 10:11:24 UTC
Created attachment 192598 [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.

reflowed the code, so that is is executed after is_sql_valid, and put
the code in a subroutine

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
Comment 11 David Nind 2026-02-07 21:07:20 UTC
Getting a sha1 and fake ancestor error when applying the patch 8-(...
Comment 12 David Cook 2026-02-08 23:09:50 UTC
(In reply to David Nind from comment #11)
> Getting a sha1 and fake ancestor error when applying the patch 8-(...

Looks like that Report.pm needs a rebase
Comment 13 Lucas Gass (lukeg) 2026-02-11 14:52:39 UTC
Created attachment 192983 [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.

reflowed the code, so that is is executed after is_sql_valid, and put
the code in a subroutine

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
Comment 14 Lucas Gass (lukeg) 2026-02-11 14:52:52 UTC
rebased to current main
Comment 15 Lucas Gass (lukeg) 2026-02-11 15:17:11 UTC
This isnt working with MariaDB, if I set the max_time to 1 second the report which clearly takes more than 1 second never times out.
Comment 16 Mark Hofstetter 2026-02-15 10:02:01 UTC
just tested it again with both mariadb and mysql and it "works for me" (tm)

please consider that sometimes statements appear to run longer but in reality the execution time is far less

please try again with the statment provided by kyle 

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

and/or use very short (0.001) time settings