| Summary: | Add max_statement_time to SQL report queries | ||
|---|---|---|---|
| Product: | Koha | Reporter: | Katrin Fischer <katrin.fischer> |
| Component: | Reports | Assignee: | 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
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 )
|