[Bug 43381] New: Report row count query ( nb_rows ) ignores report_sql_max_statement_time_seconds
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 Bug ID: 43381 Summary: Report row count query ( nb_rows ) ignores report_sql_max_statement_time_seconds Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: major Priority: P5 - low Component: Reports Assignee: koha-bugs@lists.koha-community.org Reporter: kyle@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org CC: lisette@bywatersolutions.com Target Milestone: --- Bug 39164 added report_sql_max_statement_time_seconds, which wraps report SQL in a SET STATEMENT max_statement_time so the database kills a runaway report. It works for the query that fetches the results, but not for the query that counts them. C4::Reports::Guided::execute_query() applies the limit to its own copy of the SQL, so the caller's copy is untouched. reports/guided_reports.pl then hands that original SQL to nb_rows(), which runs SELECT COUNT(*) FROM $sql with no time limit. If that wrapper fails ( e.g. "Duplicate column name" ), nb_rows falls back to running the report a second time and counting every row by hand, also with no limit. Test Plan: 1) Apply the first patch 2) prove t/db_dependent/Reports/Guided.t 3) Note the 'nb_rows() tests' subtest fails! 4) Apply the remaining patches 5) prove t/db_dependent/Reports/Guided.t 6) Note the tests pass now! 7) Add this to koha-conf.xml: <report_sql_max_statement_time_seconds>2<report_sql_max_statement_time_seconds> 8) Restart all the things! 9) Create a report with the SQL: SELECT b1.borrowernumber FROM borrowers b1, borrowers b2 WHERE SLEEP(0.005) = 0 10) Run the report 11) Note the page comes back in about 2 seconds, the first 20 rows are shown, and "Total number of results" is *not* displayed! 12) Comment out report_sql_max_statement_time_seconds in koha-conf.xml 13) Restart plack again 14) Run the report again 15) Note the page now takes about 20 seconds and "Total number of results" is displayed! -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |kyle@bywatersolutions.com |ity.org | -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Depends on| |39164 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=39164 [Bug 39164] Add max_statement_time to SQL report queries -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 --- Comment #1 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 204170 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=204170&action=edit Bug 43381: Add unit tests Patch from commit 2b5fbe1 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 --- Comment #2 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 204171 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=204171&action=edit Bug 43381: Report row count query ( nb_rows ) ignores report_sql_max_statement_time_seconds Bug 39164 added report_sql_max_statement_time_seconds, which wraps report SQL in a SET STATEMENT max_statement_time so the database kills a runaway report. It works for the query that fetches the results, but not for the query that counts them. C4::Reports::Guided::execute_query() applies the limit to its own copy of the SQL, so the caller's copy is untouched. reports/guided_reports.pl then hands that original SQL to nb_rows(), which runs SELECT COUNT(*) FROM $sql with no time limit. If that wrapper fails ( e.g. "Duplicate column name" ), nb_rows falls back to running the report a second time and counting every row by hand, also with no limit. Test Plan: 1) Apply the first patch 2) prove t/db_dependent/Reports/Guided.t 3) Note the 'nb_rows() tests' subtest fails! 4) Apply the remaining patches 5) prove t/db_dependent/Reports/Guided.t 6) Note the tests pass now! 7) Add this to koha-conf.xml: <report_sql_max_statement_time_seconds>2<report_sql_max_statement_time_seconds> 8) Restart all the things! 9) Create a report with the SQL: SELECT b1.borrowernumber FROM borrowers b1, borrowers b2 WHERE SLEEP(0.005) = 0 10) Run the report 11) Note the page comes back in about 2 seconds, the first 20 rows are shown, and "Total number of results" is *not* displayed! 12) Comment out report_sql_max_statement_time_seconds in koha-conf.xml 13) Restart plack again 14) Run the report again 15) Note the page now takes about 20 seconds and "Total number of results" is displayed! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 --- Comment #3 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 204172 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=204172&action=edit Bug 43381: (QA follow-up) Add POD for private subs in C4::Reports::Guided Patch from commit 6abbd56 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #204170|0 |1 is obsolete| | Attachment #204171|0 |1 is obsolete| | Attachment #204172|0 |1 is obsolete| | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 --- Comment #4 from David Nind <david@davidnind.com> --- Created attachment 204183 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=204183&action=edit Bug 43381: Add unit tests Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 --- Comment #5 from David Nind <david@davidnind.com> --- Created attachment 204184 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=204184&action=edit Bug 43381: Report row count query ( nb_rows ) ignores report_sql_max_statement_time_seconds Bug 39164 added report_sql_max_statement_time_seconds, which wraps report SQL in a SET STATEMENT max_statement_time so the database kills a runaway report. It works for the query that fetches the results, but not for the query that counts them. C4::Reports::Guided::execute_query() applies the limit to its own copy of the SQL, so the caller's copy is untouched. reports/guided_reports.pl then hands that original SQL to nb_rows(), which runs SELECT COUNT(*) FROM $sql with no time limit. If that wrapper fails ( e.g. "Duplicate column name" ), nb_rows falls back to running the report a second time and counting every row by hand, also with no limit. Test Plan: 1) Apply the first patch 2) prove t/db_dependent/Reports/Guided.t 3) Note the 'nb_rows() tests' subtest fails! 4) Apply the remaining patches 5) prove t/db_dependent/Reports/Guided.t 6) Note the tests pass now! 7) Add this to koha-conf.xml: <report_sql_max_statement_time_seconds>2<report_sql_max_statement_time_seconds> 8) Restart all the things! 9) Create a report with the SQL: SELECT b1.borrowernumber FROM borrowers b1, borrowers b2 WHERE SLEEP(0.005) = 0 10) Run the report 11) Note the page comes back in about 2 seconds, the first 20 rows are shown, and "Total number of results" is *not* displayed! 12) Comment out report_sql_max_statement_time_seconds in koha-conf.xml 13) Restart plack again 14) Run the report again 15) Note the page now takes about 20 seconds and "Total number of results" is displayed! Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 --- Comment #6 from David Nind <david@davidnind.com> --- Created attachment 204185 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=204185&action=edit Bug 43381: (QA follow-up) Add POD for private subs in C4::Reports::Guided Signed-off-by: David Nind <david@davidnind.com> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com --- Comment #7 from David Nind <david@davidnind.com> --- Testing notes (using KTD): 1. Step 7: Add closing slash to XML code - ...2</report...> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 --- Comment #8 from Aleisha Amohia <aleisha@catalyst.net.nz> --- Created attachment 205170 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=205170&action=edit Batch operation on 0 visible records This is certainly working and QA tests pass, however the batch operations count when report_sql_max_statement_time_seconds is configured in koha-conf.xml now shows 0 visible records. The batch operation button still actually works on the records, so it's just the display of the count that's broken. I feel this was an unintended consequence of the patch. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43381 Aleisha Amohia <aleisha@catalyst.net.nz> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Failed QA -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org