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 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 #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 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 ---------------------------------------------------------------------------- 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 ---------------------------------------------------------------------------- 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 --- 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 ---------------------------------------------------------------------------- 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 ---------------------------------------------------------------------------- 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 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 --- 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 --- 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 ---------------------------------------------------------------------------- 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 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 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 --- 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 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 --- 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 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 --- 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 #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 ---------------------------------------------------------------------------- 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 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 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 ---------------------------------------------------------------------------- Status|Pushed to oldoldstable |Needs documenting -- You are receiving this mail because: You are watching all bug changes.