Bugzilla – Attachment 192283 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
7235aba.patch (text/plain), 5.32 KB, created by
Mark Hofstetter
on 2026-02-01 15:38:45 UTC
(
hide
)
Description:
Bug 39164: Add max_statement_time to SQL report queries
Filename:
MIME Type:
Creator:
Mark Hofstetter
Created:
2026-02-01 15:38:45 UTC
Size:
5.32 KB
patch
obsolete
>From 7235aba544e8ee47234500f2e4dd0cb2e4f29c9a 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 ++++++++++++++++++++++++++++---------------- > 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 >
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