Bugzilla – Attachment 189691 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
Bug-39164-Add-maxstatementtime-to-SQL-report-queri.patch (text/plain), 9.45 KB, created by
Mark Hofstetter
on 2025-11-19 01:22:40 UTC
(
hide
)
Description:
Bug 39164: Add max_statement_time to SQL report queries
Filename:
MIME Type:
Creator:
Mark Hofstetter
Created:
2025-11-19 01:22:40 UTC
Size:
9.45 KB
patch
obsolete
>From 51283710be0a80257d766181ebf4c309221562da Mon Sep 17 00:00:00 2001 >From: Mark Hofstetter <mark@hofstetter.at> >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 <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 >--- > 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 0e07af36d8..6ae0c11a7e 100644 >--- a/debian/templates/koha-conf-site.xml.in >+++ b/debian/templates/koha-conf-site.xml.in >@@ -260,6 +260,7 @@ __END_SRU_PUBLICSERVER__ > <ca>__DB_TLS_CA_CERTIFICATE__</ca> > <cert>__DB_TLS_CLIENT_CERTIFICATE__</cert> > <key>__DB_TLS_CLIENT_KEY__</key> >+ <report_sql_max_statement_time_seconds>0</report_sql_max_statement_time_seconds> > <biblioserver>biblios</biblioserver> > <biblioservershadow>1</biblioservershadow> > <authorityserver>authorities</authorityserver> >diff --git a/etc/koha-conf.xml b/etc/koha-conf.xml >index cd58487213..aa4f7c54fb 100644 >--- a/etc/koha-conf.xml >+++ b/etc/koha-conf.xml >@@ -73,6 +73,7 @@ > <ca>__DB_TLS_CA_CERTIFICATE__</ca> > <cert>__DB_TLS_CLIENT_CERTIFICATE__</cert> > <key>__DB_TLS_CLIENT_KEY__</key> >+ <report_sql_max_statement_time_seconds>0</report_sql_max_statement_time_seconds> > <biblioserver>biblios</biblioserver> > <biblioservershadow>1</biblioservershadow> > <authorityserver>authorities</authorityserver> >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 @@ > <ca></ca> > <cert></cert> > <key></key> >+ <report_sql_max_statement_time_seconds>0</report_sql_max_statement_time_seconds> > <biblioserver>biblios</biblioserver> > <biblioservershadow>1</biblioservershadow> > <authorityserver>authorities</authorityserver> >-- >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