From 299ae7a278e948143e5bbc28cdcbda3e96277100 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 | 32 ++++++++++++++++++++++++++++++++ t/db_dependent/Koha/Reports.t | 27 ++++++++++++++++++++++++++- 3 files changed, 60 insertions(+), 1 deletion(-) diff --git a/C4/Reports/Guided.pm b/C4/Reports/Guided.pm index 577b3694956..639608dacc3 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 b6a210eb9fc..c41feff26c0 100644 --- a/Koha/Report.pm +++ b/Koha/Report.pm @@ -258,6 +258,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 diff --git a/t/db_dependent/Koha/Reports.t b/t/db_dependent/Koha/Reports.t index 19d1d60679e..6125a64f693 100755 --- a/t/db_dependent/Koha/Reports.t +++ b/t/db_dependent/Koha/Reports.t @@ -18,7 +18,7 @@ use Modern::Perl; use Test::NoWarnings; -use Test::More tests => 9; +use Test::More tests => 10; use Koha::Report; use Koha::Reports; @@ -185,4 +185,29 @@ subtest '_might_add_limit' => sub { ); }; +subtest 'apply_execution_time_limit' => sub { + plan tests => 3; + + my $sql = "SELECT * FROM biblio"; + + t::lib::Mocks::mock_config( 'report_sql_max_statement_time_seconds', 0 ); + is( Koha::Report->apply_execution_time_limit($sql), $sql, 'No execution time limit configured' ); + + t::lib::Mocks::mock_config( 'report_sql_max_statement_time_seconds', 1.5 ); + my $context = Test::MockModule->new('C4::Context'); + $context->mock( 'get_versions', sub { return ( mysqlVersion => '10.6.0-MariaDB' ); } ); + is( + Koha::Report->apply_execution_time_limit($sql), + 'SET STATEMENT max_statement_time=1.500000 FOR SELECT * FROM biblio', + 'MariaDB max statement time applied' + ); + + $context->mock( 'get_versions', sub { return ( mysqlVersion => '8.0.36' ); } ); + like( + Koha::Report->apply_execution_time_limit($sql), + qr!^(?i:select) /\*\+ MAX_EXECUTION_TIME\(1500\) \*/ \* FROM biblio$!, + 'MySQL max execution time hint applied' + ); +}; + $schema->storage->txn_rollback; -- 2.39.5