|
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 )
Created attachment 192172 [details] [review] Bug 39164: Strip the wrapper before validaing the SQL 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. Bug Update for Bug 39164 - Add max_statement_time to SQL report queries Created attachment 192283 [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 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 Getting a sha1 and fake ancestor error when applying the patch 8-(... (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 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 rebased to current main 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. 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
|