[Bug 39164] New: Add max_statement_time to SQL report quieries
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Bug ID: 39164 Summary: Add max_statement_time to SQL report quieries Change sponsored?: --- Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: enhancement Priority: P5 - low Component: Reports Assignee: koha-bugs@lists.koha-community.org Reporter: katrin.fischer@bsz-bw.de QA Contact: testopia@bugs.koha-community.org CC: koha@trust-box.at 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/ -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |cornelius.amzar@bsz-bw.de, | |markus.john@bsz-bw.de Summary|Add max_statement_time to |Add max_statement_time to |SQL report quieries |SQL report queries -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Mark Hofstetter <koha@trust-box.at> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |koha@trust-box.at |ity.org | -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Mark Hofstetter <koha@trust-box.at> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=33116 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |nick@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #1 from Mark Hofstetter <koha@trust-box.at> --- 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #2 from Mark Hofstetter <koha@trust-box.at> --- mariadb code SET STATEMENT max_statement_time=1 FOR select sleep(5); unit is second, whereas with mysql it's milliseconds -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #3 from Mark Hofstetter <koha@trust-box.at> --- Created attachment 189691 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=189691&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #189691|0 |1 is obsolete| | --- Comment #4 from David Nind <david@davidnind.com> --- Created attachment 189693 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=189693&action=edit 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> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |kyle@bywatersolutions.com QA Contact|testopia@bugs.koha-communit |kyle@bywatersolutions.com |y.org | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Failed QA --- Comment #5 from Kyle M Hall (khall) <kyle@bywatersolutions.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 ) -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #6 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 192172 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=192172&action=edit Bug 39164: Strip the wrapper before validaing the SQL -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |lucas@bywatersolutions.com Attachment #192172|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Failed QA --- Comment #7 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Mark Hofstetter <koha@trust-box.at> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff --- Comment #8 from Mark Hofstetter <koha@trust-box.at> --- Bug Update for Bug 39164 - Add max_statement_time to SQL report queries -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Mark Hofstetter <koha@trust-box.at> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #189693|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #9 from Mark Hofstetter <koha@trust-box.at> --- Created attachment 192283 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=192283&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Mark Hofstetter <koha@trust-box.at> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #192283|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #10 from Mark Hofstetter <koha@trust-box.at> --- Created attachment 192598 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=192598&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Patch doesn't apply --- Comment #11 from David Nind <david@davidnind.com> --- Getting a sha1 and fake ancestor error when applying the patch 8-(... -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #12 from David Cook <dcook@prosentient.com.au> --- (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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Patch doesn't apply |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #192598|0 |1 is obsolete| | --- Comment #13 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 192983 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=192983&action=edit 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #14 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- rebased to current main -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Failed QA --- Comment #15 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- 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. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Mark Hofstetter <koha@trust-box.at> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Needs Signoff --- Comment #16 from Mark Hofstetter <koha@trust-box.at> --- 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 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #192983|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #17 from David Nind <david@davidnind.com> --- Created attachment 193597 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=193597&action=edit 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 Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #18 from David Nind <david@davidnind.com> --- Worked for me. Testing notes (using KTD): 1. I couldn't get Kyle's report to complete running (so assuming it is a very very long report). 2. The default MariaDB database in KTD is now 10.11, I didn't test with 10.5. 3. After adding or changing the max statement in the koha-conf.xml file, I did a flush_memcached and restart_all -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #193597|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #19 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 195616 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195616&action=edit 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 Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Kyle M Hall <kyle@bywatersolutions.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #20 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 195618 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195618&action=edit Bug 39164: (QA follow-up) Add setting to koha conf files Patch from commit 6619683 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |release-notes-needed -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Passed QA |Pushed to main Version(s)| |26.05.00 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #21 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Nice work everyone! Pushed to main for 26.05 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #22 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- If possible it would be great if this could be considered for backporting being inactive on update. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #23 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- (In reply to Katrin Fischer from comment #22)
If possible it would be great if this could be considered for backporting being inactive on update.
I agree, marking it as such. At least for 25.11 and 25.05 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |rel_25_05_candidate, | |rel_25_11_candidate -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Text to go in the| |This patchset adds the release notes| |abilty to set a maximum | |execution time in seconds | |for SQL report queries. | |Reports exceeding this | |limit will be automatically | |terminated. This is | |configured in the | |koha-conf.xml file by | |setting the | |report_sql_max_statement_ti | |me_seconds parameter. By | |default this is turned off. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|release-notes-needed | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Jacob O'Mara <jacob.omara@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to main |Pushed to stable Version(s)|26.05.00 |26.05.00,25.11.05 released in| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 --- Comment #24 from Jacob O'Mara <jacob.omara@openfifth.co.uk> --- Thanks all, pushed to 25.11.x -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |wainuiwitikapark@catalyst.n | |et.nz Status|Pushed to stable |Pushed to oldstable --- Comment #25 from Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> --- Pushed to 25.05.x for 25.05.12 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|26.05.00,25.11.05 |26.05.00,25.11.05,25.05.12 released in| | Status|Pushed to oldstable |Pushed to oldoldstable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Wainui Witika-Park <wainuiwitikapark@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to oldoldstable |Needs documenting -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |43016 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43016 [Bug 43016] [OMNIBUS] Server resource protection -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|rel_25_05_candidate, | |rel_25_11_candidate | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Michelle Spinney <mspinney@clamsnet.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mspinney@clamsnet.org -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |43381 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 [Bug 43381] Report row count query ( nb_rows ) ignores report_sql_max_statement_time_seconds -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org