Bugzilla – Attachment 192598 Details for
Bug 39164
Add max_statement_time to SQL report queries
Home
|
New
|
Browse
|
Search
|
[?]
|
Reports
|
Help
|
New Account
|
Log In
[x]
|
Forgot Password
Login:
[x]
[patch]
Bug 39164: Add max_statement_time to SQL report queries
e8f9cc1.patch (text/plain), 6.82 KB, created by
Mark Hofstetter
on 2026-02-06 10:11:24 UTC
(
hide
)
Description:
Bug 39164: Add max_statement_time to SQL report queries
Filename:
MIME Type:
Creator:
Mark Hofstetter
Created:
2026-02-06 10:11:24 UTC
Size:
6.82 KB
patch
obsolete
>From e8f9cc100996342f43ff94a4ea29ee1f712c5c13 Mon Sep 17 00:00:00 2001 >From: Mark Hofstetter <mark@hofstetter.at> >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 <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 >--- > C4/Reports/Guided.pm | 2 ++ > Koha/Report.pm | 50 ++++++++++++++++++++++------------- > t/db_dependent/Koha/Reports.t | 27 ++++++++++++++++++- > 3 files changed, 60 insertions(+), 19 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 >diff --git a/t/db_dependent/Koha/Reports.t b/t/db_dependent/Koha/Reports.t >index 19d1d60679..6125a64f69 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.47.3 >
You cannot view the attachment while viewing its details because your browser does not support IFRAMEs.
View the attachment on a separate page
.
View Attachment As Diff
View Attachment As Raw
Actions:
View
|
Diff
|
Splinter Review
Attachments on
bug 39164
:
189691
|
189693
|
192172
|
192283
|
192598
|
192983