From 7235aba544e8ee47234500f2e4dd0cb2e4f29c9a Mon Sep 17 00:00:00 2001 From: Mark Hofstetter Date: Sun, 1 Feb 2026 16:29:21 +0100 Subject: [PATCH] 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 part 0.001 OR use the following statemtn sed -i '//a 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 20 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 --- C4/Reports/Guided.pm | 2 ++ Koha/Report.pm | 50 ++++++++++++++++++++++++++++---------------- 2 files changed, 34 insertions(+), 18 deletions(-) diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index 577b369495..639608dacc 100644 --- a/C4/Reports/Guided.pm +++ b/C4/Reports/Guided.pm @@ -54,6 +54,7 @@ use Koha::Logger; use Koha::Notice::Templates; use Koha::Patron::Categories; use Koha::Patrons; +use Koha::Report; use Koha::Reports; use Koha::SharedContent; use Koha::TemplateUtils qw( process_tt ); @@ -604,6 +605,7 @@ sub execute_query { my ( $is_sql_valid, $errors ) = Koha::Report->new( { savedsql => $sql } )->is_sql_valid; return ( undef, @{$errors}[0] ) unless $is_sql_valid; + $sql = Koha::Report->apply_execution_time_limit($sql); foreach my $sql_param (@$sql_params) { if ( $sql_param =~ m/\n/ ) { diff --git a/Koha/Report.pm b/Koha/Report.pm index cb7f8fdcf5..176378202e 100644 --- a/Koha/Report.pm +++ b/Koha/Report.pm @@ -233,24 +233,6 @@ sub prep_report { $sql = "$sql /* saved_sql.id: ${\( $self->id )} */"; - # https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 - my $report_sql_max_statement_time_seconds = C4::Context->config('report_sql_max_statement_time_seconds') // 0; - if ($report_sql_max_statement_time_seconds) { - my %versions = C4::Context::get_versions(); - if ( $versions{'mysqlVersion'} =~ /MariaDB/i ) { - $sql = sprintf( - 'SET STATEMENT max_statement_time=%d FOR %s', - $report_sql_max_statement_time_seconds, - $sql - ); - } else { - - # mysql timing is in milliseconds - $report_sql_max_statement_time_seconds *= 1000; - $sql =~ s#select#select /*+ MAX_EXECUTION_TIME($report_sql_max_statement_time_seconds) */#i; - } - } - return $sql, $headers; } @@ -277,6 +259,38 @@ sub _might_add_limit { return $sql; } +=head3 apply_execution_time_limit + + $sql = Koha::Report->apply_execution_time_limit($sql); + +Apply SQL execution time limits based on report_sql_max_statement_time_seconds. + +=cut + +sub apply_execution_time_limit { + my ( $class, $sql ) = @_; + + # https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 + my $report_sql_max_statement_time_seconds = C4::Context->config('report_sql_max_statement_time_seconds') // 0; + if ($report_sql_max_statement_time_seconds) { + my %versions = C4::Context::get_versions(); + if ( $versions{'mysqlVersion'} =~ /MariaDB/i ) { + $sql = sprintf( + 'SET STATEMENT max_statement_time=%f FOR %s', + $report_sql_max_statement_time_seconds, + $sql + ); + } else { + + # mysql timing is in milliseconds + $report_sql_max_statement_time_seconds *= 1000; + $sql =~ s#select#select /*+ MAX_EXECUTION_TIME($report_sql_max_statement_time_seconds) */#i; + } + } + + return $sql; +} + =head3 _type Returns name of corresponding DBIC resultset -- 2.47.3