From 66d5a151ca6f14073679fad8bece2450c4335b00 Mon Sep 17 00:00:00 2001 From: Mark Hofstetter Date: Wed, 19 Nov 2025 00:48:00 +0000 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. 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 Signed-off-by: David Nind --- Koha/Report.pm | 19 ++++++++ debian/templates/koha-conf-site.xml.in | 1 + etc/koha-conf.xml | 1 + t/Koha/Config.t | 65 +++++++++++++------------- t/data/koha-conf.xml | 1 + 5 files changed, 55 insertions(+), 32 deletions(-) diff --git a/Koha/Report.pm b/Koha/Report.pm index be146f214b..5036582812 100644 --- a/Koha/Report.pm +++ b/Koha/Report.pm @@ -232,6 +232,25 @@ sub prep_report { $sql = $self->_might_add_limit($sql) if $params->{export}; $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; } diff --git a/debian/templates/koha-conf-site.xml.in b/debian/templates/koha-conf-site.xml.in index 36f632fdb9..d0bd8142fe 100644 --- a/debian/templates/koha-conf-site.xml.in +++ b/debian/templates/koha-conf-site.xml.in @@ -260,6 +260,7 @@ __END_SRU_PUBLICSERVER__ __DB_TLS_CA_CERTIFICATE__ __DB_TLS_CLIENT_CERTIFICATE__ __DB_TLS_CLIENT_KEY__ + 0 biblios 1 authorities diff --git a/etc/koha-conf.xml b/etc/koha-conf.xml index 5c8bab6d67..45163007a6 100644 --- a/etc/koha-conf.xml +++ b/etc/koha-conf.xml @@ -73,6 +73,7 @@ __DB_TLS_CA_CERTIFICATE__ __DB_TLS_CLIENT_CERTIFICATE__ __DB_TLS_CLIENT_KEY__ + 0 biblios 1 authorities diff --git a/t/Koha/Config.t b/t/Koha/Config.t index 9040c79f14..e42699636f 100755 --- a/t/Koha/Config.t +++ b/t/Koha/Config.t @@ -90,38 +90,39 @@ subtest 'read_from_file() tests' => sub { } }, 'config' => { - 'db_scheme' => 'mysql', - 'database' => 'koha', - 'hostname' => 'localhost', - 'port' => '3306', - 'user' => 'kohaadmin', - 'pass' => 'katikoan', - 'tls' => 'no', - 'ca' => '', - 'cert' => '', - 'key' => '', - 'biblioserver' => 'biblios', - 'biblioservershadow' => '1', - 'authorityserver' => 'authorities', - 'authorityservershadow' => '1', - 'pluginsdir' => '/home/koha/var/lib/plugins', - 'enable_plugins' => '0', - 'upload_path' => '', - 'tmp_path' => '', - 'intranetdir' => '/home/koha/src', - 'opacdir' => '/home/koha/src/opac', - 'opachtdocs' => '/home/koha/src/koha-tmpl/opac-tmpl', - 'intrahtdocs' => '/home/koha/src/koha-tmpl/intranet-tmpl', - 'includes' => '/home/koha/src/koha-tmpl/intranet-tmpl/prog/en/includes/', - 'logdir' => '/home/koha/var/log', - 'docdir' => '/home/koha/doc', - 'backupdir' => '/home/koha/var/spool', - 'mana_config' => 'https://mana-kb.koha-community.org', - 'backup_db_via_tools' => '0', - 'backup_conf_via_tools' => '0', - 'install_log' => '/home/koha/misc/koha-install-log', - 'useldapserver' => '1', - 'ldapserver' => { + 'db_scheme' => 'mysql', + 'database' => 'koha', + 'hostname' => 'localhost', + 'port' => '3306', + 'user' => 'kohaadmin', + 'pass' => 'katikoan', + 'tls' => 'no', + 'ca' => '', + 'cert' => '', + 'key' => '', + 'report_sql_max_statement_time_seconds' => 0, + 'biblioserver' => 'biblios', + 'biblioservershadow' => '1', + 'authorityserver' => 'authorities', + 'authorityservershadow' => '1', + 'pluginsdir' => '/home/koha/var/lib/plugins', + 'enable_plugins' => '0', + 'upload_path' => '', + 'tmp_path' => '', + 'intranetdir' => '/home/koha/src', + 'opacdir' => '/home/koha/src/opac', + 'opachtdocs' => '/home/koha/src/koha-tmpl/opac-tmpl', + 'intrahtdocs' => '/home/koha/src/koha-tmpl/intranet-tmpl', + 'includes' => '/home/koha/src/koha-tmpl/intranet-tmpl/prog/en/includes/', + 'logdir' => '/home/koha/var/log', + 'docdir' => '/home/koha/doc', + 'backupdir' => '/home/koha/var/spool', + 'mana_config' => 'https://mana-kb.koha-community.org', + 'backup_db_via_tools' => '0', + 'backup_conf_via_tools' => '0', + 'install_log' => '/home/koha/misc/koha-install-log', + 'useldapserver' => '1', + 'ldapserver' => { ldapserver => { hostname => 'ldap://another_ldap_server:389', user => 'user', diff --git a/t/data/koha-conf.xml b/t/data/koha-conf.xml index a4efda6681..46f369e105 100644 --- a/t/data/koha-conf.xml +++ b/t/data/koha-conf.xml @@ -72,6 +72,7 @@ + 0 biblios 1 authorities -- 2.39.5