https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |dcook@prosentient.com.au -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #1 from David Cook <dcook@prosentient.com.au> --- This one could leverage bug 14435 to save the results as well, yeah? -- 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=41921 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 the assignee for the bug. You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |42108 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42108 [Bug 42108] Add ability to specify that a report can only be run as a foreground or background report -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #4 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 195481 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195481&action=edit Bug 41921: Split user permission Splits the execute_reports permission into execute_reports_foreground and execute_reports_background. System preference decides what options are available to use, permissions decide if the user is able to take the allowed action. That is to say, the system preference has precedence over the user permission. If a librarian has the execute_reports_foreground permission, but no the execute_reports_background permission, and the syspref is set to only allow background reports to run, that librarian will not be able to execute reports. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195482|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=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195480|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=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195483|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=41921 --- Comment #8 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 195485 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195485&action=edit Bug 41921: Split user permission Splits the execute_reports permission into execute_reports_foreground and execute_reports_background. System preference decides what options are available to use, permissions decide if the user is able to take the allowed action. That is to say, the system preference has precedence over the user permission. If a librarian has the execute_reports_foreground permission, but no the execute_reports_background permission, and the syspref is set to only allow background reports to run, that librarian will not be able to execute reports. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #10 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- I am wondering if this is practical - I think we resend the query quite often, when changing the number of results on a page and when switching between the paginated pages. Could it make sense intstead of tying it to a preference/permission to tie it to the report? Some reports might be quick and the background job could get in the way easily why this might make more sense for other reports. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195484|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=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195486|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=41921 --- Comment #12 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 195843 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=195843&action=edit Bug 41921: Split user permission Splits the execute_reports permission into execute_reports_foreground and execute_reports_background. System preference decides what options are available to use, permissions decide if the user is able to take the allowed action. That is to say, the system preference has precedence over the user permission. If a librarian has the execute_reports_foreground permission, but no the execute_reports_background permission, and the syspref is set to only allow background reports to run, that librarian will not be able to execute reports. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #14 from Andrew Fuerste-Henry <andrew@bywatersolutions.com> --- (In reply to Katrin Fischer from comment #10)

I am wondering if this is practical - I think we resend the query quite often, when changing the number of results on a page and when switching between the paginated pages. Could it make sense intstead of tying it to a preference/permission to tie it to the report? Some reports might be quick and the background job could get in the way easily why this might make more sense for other reports.

When running a report as a background job, it seems we always return all rows on a single page, so there's no re-running while paging through. If the syspref is set to "let the user choose," there's a "run as background job" checkbox each time one runs a report that is checked by default. So if one were running a report multiple times with different parameter selections and didn't want to run them in the background, one would need to uncheck that box each time. That does seem onerous. Maybe there could be an option to set any given report to default to running not as a background job? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- See Also| |https://bugs.koha-community | |.org/bugzilla3/show_bug.cgi | |?id=38414 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |42596 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42596 [Bug 42596] Add ability for reports APIs ( svc/report, opac/svc/report ) to run reports as background jobs -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |42643 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42643 [Bug 42643] [OMNIBUS] Assorted performance and stability work -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #195843|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=41921 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=41921 --- Comment #17 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199312 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199312&action=edit Bug 41921: Add run_type to the prep_report tracking marker Test plan 1) Apply patch 2) prove t/db_dependent/Koha/Reports.t -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #19 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199315 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199315&action=edit Bug 41921: Add ability to run reports as background jobs Adds Koha::BackgroundJob::RunReport job type, registered with the long_tasks queue ( dedicated queue planned in bug 42642 ) Test plan 1) Apply patch, run updatedatabase.pl 2) prove t/db_dependent/Koha/BackgroundJob/RunReport.t 3) prove t/db_dependent/Koha/Reports.t 4) prove t/Koha/Auth/Permissions.t 5) Set ReportsRunInBackground to 'always'. Run a saved SQL report from the staff client. Confirm a job appears under admin/background_jobs.pl with type 'Run report'. Once finished, click 'View results' on the job page and confirm the stored result renders in the standard report-results view. 6) Set ReportsRunInBackground to 'never'. Run the same report. Confirm it executes in the foreground. 7) Set ReportsRunInBackground to 'user_choice'. Run the report, confirm the 'choose run mode' page is shown with a Background checkbox preselected, and the report runs accordingly. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #21 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199317 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199317&action=edit Bug 41921: Wire check_limits into svc/report and opac/svc/report * svc/report calls Koha::Reports->check_limits before prep_report, passing the logged-in patron's borrowernumber. Returns a 429 if the limits have been exceeded. * opac/svc/report does the same, but with borrowernumber from the userenv when present (anonymous OPAC requests still count toward the per-instance limit). * Update error keys for consistency Test plan 1) Apply patch, restart all the things 2) prove t/db_dependent/Koha/Reports.t 3) Set total_running_reports_per_instance_limit to 1 in koha-conf.xml 4) Start a long report through the staff UI ( e.g. "SELECT SLEEP(120)" ) 5) Browse to /cgi-bin/koha/svc/report?id=N (while authenticated) and confirm the response is 429 with body {"error":"total_running_reports_per_instance_limit_exceeded"} 6) Browse to /cgi-bin/koha/opac/svc/report?id=N (while logged out) and confirm the same 429 response 7) Reset the limit 8) Repeat steps 4-6 9) Confirm normal JSON results -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #23 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199319 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199319&action=edit Bug 41921: Add a Report jobs dashboard for librarians This commit adds a dedicated Report jobs dashboard requiring the same permissions as running a report. The is needed because the background jobs viewer may not be accesible to everyone who ran run reports. In addition, this page will let librarians cancel their own queued reports. Superlibrarian's can cancel any report. The ability to cancel running reports is planned for a future bug report. Note: Should we have a new permission for the ability to cancel any queued report? Test plan 1) Apply patch, run updatedatabase.pl, restart all the things 2) prove t/db_dependent/api/v1/reports_jobs.t 3) Set ReportsRunInBackground=always 4) Run a saved SQL report from the staff client, something long running like "SELECT SLEEP(60)" 5) Confirm you land on the Report jobs dashboard with your new job highlighted 6) Click Cancel on the queued row, confirm the prompt, and confirm the row's status changes to 'Cancelled' on the next ajax reload 7) Run another report, let it finish, click 'View results' on the finished row, confirm the stored result renders in the standard report-results view. I've attempted to make the stored sql view match the "live results" view as much as I could. 8) Log in as a librarian with reports access but not superlibrarian, confirm you only see Cancel on your own queued rows, not on jobs owned by other librarians 9) Log in as superlibrarian, confirm Cancel is available on any queued row regardless of owner -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #25 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199321 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199321&action=edit Bug 41921: Update Schema files [DO NOT PUSH] Patch from commit fb8ad49 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Patch doesn't apply |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #199312|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=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #199315|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=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #199317|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=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #199319|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=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #199321|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=41921 --- Comment #28 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199552 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199552&action=edit Bug 41921: Add run_type to the prep_report tracking marker Test plan 1) Apply patch 2) prove t/db_dependent/Koha/Reports.t -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #30 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199554 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199554&action=edit Bug 41921: Add ability to run reports as background jobs Adds Koha::BackgroundJob::RunReport job type, registered with the long_tasks queue ( dedicated queue planned in bug 42642 ) Test plan 1) Apply patch, run updatedatabase.pl 2) prove t/db_dependent/Koha/BackgroundJob/RunReport.t 3) prove t/db_dependent/Koha/Reports.t 4) prove t/Koha/Auth/Permissions.t 5) Set ReportsRunInBackground to 'always'. Run a saved SQL report from the staff client. Confirm a job appears under admin/background_jobs.pl with type 'Run report'. Once finished, click 'View results' on the job page and confirm the stored result renders in the standard report-results view. 6) Set ReportsRunInBackground to 'never'. Run the same report. Confirm it executes in the foreground. 7) Set ReportsRunInBackground to 'user_choice'. Run the report, confirm the 'choose run mode' page is shown with a Background checkbox preselected, and the report runs accordingly. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #32 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199556 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199556&action=edit Bug 41921: Wire check_limits into svc/report and opac/svc/report * svc/report calls Koha::Reports->check_limits before prep_report, passing the logged-in patron's borrowernumber. Returns a 429 if the limits have been exceeded. * opac/svc/report does the same, but with borrowernumber from the userenv when present (anonymous OPAC requests still count toward the per-instance limit). * Update error keys for consistency Test plan 1) Apply patch, restart all the things 2) prove t/db_dependent/Koha/Reports.t 3) Set total_running_reports_per_instance_limit to 1 in koha-conf.xml 4) Start a long report through the staff UI ( e.g. "SELECT SLEEP(120)" ) 5) Browse to /cgi-bin/koha/svc/report?id=N (while authenticated) and confirm the response is 429 with body {"error":"total_running_reports_per_instance_limit_exceeded"} 6) Browse to /cgi-bin/koha/opac/svc/report?id=N (while logged out) and confirm the same 429 response 7) Reset the limit 8) Repeat steps 4-6 9) Confirm normal JSON results -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #34 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199558 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199558&action=edit Bug 41921: Add a Report jobs dashboard for librarians This commit adds a dedicated Report jobs dashboard requiring the same permissions as running a report. The is needed because the background jobs viewer may not be accesible to everyone who ran run reports. In addition, this page will let librarians cancel their own queued reports. Superlibrarian's can cancel any report. The ability to cancel running reports is planned for a future bug report. Note: Should we have a new permission for the ability to cancel any queued report? Test plan 1) Apply patch, run updatedatabase.pl, restart all the things 2) prove t/db_dependent/api/v1/reports_jobs.t 3) Set ReportsRunInBackground=always 4) Run a saved SQL report from the staff client, something long running like "SELECT SLEEP(60)" 5) Confirm you land on the Report jobs dashboard with your new job highlighted 6) Click Cancel on the queued row, confirm the prompt, and confirm the row's status changes to 'Cancelled' on the next ajax reload 7) Run another report, let it finish, click 'View results' on the finished row, confirm the stored result renders in the standard report-results view. I've attempted to make the stored sql view match the "live results" view as much as I could. 8) Log in as a librarian with reports access but not superlibrarian, confirm you only see Cancel on your own queued rows, not on jobs owned by other librarians 9) Log in as superlibrarian, confirm Cancel is available on any queued row regardless of owner -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #36 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199560 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199560&action=edit Bug 41921: Update Schema files [DO NOT PUSH] Patch from commit 20230e9 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #199551|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=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #199553|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=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #199555|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=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #199557|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=41921 Kyle M Hall (khall) <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #199559|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=41921 --- Comment #37 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199567 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199567&action=edit Bug 41921: Add syspref and split execute_reports permission Split the execute_reports user permission into execute_reports_foreground and execute_reports_background. Adds ReportsRunInBackground system preference the controls which types of reports running are available for this instance. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #39 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199569 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199569&action=edit Bug 41921: Add Koha::Reports->check_limits Consolidates the per-user duplicate, per-user total, and per-instance total concurrent-run checks into a single method. Adds the count of queued run_report background jobs to the total. Test plan 1) Apply patch 2) prove t/db_dependent/Koha/Reports.t -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #41 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199571 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199571&action=edit Bug 41921: Add check_limits to guided_reports.pl Wires check_limits to the guided reports script. Test plan 1) Apply patch 2) prove t/db_dependent/Koha/Reports.t 3) Set total_running_reports_per_instance_limit to 1 in koha-conf.xml 4) Run two long reports ( "SELECT SLEEP(120)" works well) in two tabs 5) Confirm the second tab shows the 'too many total reports' alert 6) Click 'Export' on a saved report under the same conditions and confirm the export request returns 429 with a 'too many reports' message -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #43 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199573 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199573&action=edit Bug 41921: Add per-run-type overrides to report concurrency caps The existing caps in koha-conf.xml apply to every run type, so an admin who wanted to leave plenty of headroom for background jobs but keep a tight cap on foreground reports had no way to express that. This commit adds six new keys that override the combined caps per run type. Each existing cap now has matching foreground_ and background_ keys. A value of 0 in any key means no limit, matching how the combined keys already behave. When a run-type key is non-zero it replaces the combined key for that run type and only counts same-run-type queries: - foreground_X caps count foreground processlist queries only. - background_X caps count background processlist queries plus queued run_report jobs (queued jobs are always background). Test plan 1) Apply patch 2) prove t/db_dependent/Koha/Reports.t 3) Set total_running_reports_per_instance_limit to 5 and foreground_total_running_reports_per_instance_limit to 2 in koha-conf.xml, restart all the things 4) Start 2 long foreground reports 5) Start a third foreground report, confirm the 'too many total reports' alert is shown 6) Queue 4 background reports (ReportsRunInBackground=always or the choose-run-mode page) 7) Confirm all 4 are enqueued (foreground cap doesn't apply to background, combined cap allows 5) 8) Queue a 5th, confirm it is enqueued, queue a 6th and confirm it is blocked by the combined cap 9) Unset the foreground override, set background_total_running_reports_per_instance_limit to 3 and restart, repeat steps 5 and 7 confirming the bg cap now applies -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #45 from Kyle M Hall (khall) <kyle@bywatersolutions.com> --- Created attachment 199575 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=199575&action=edit Bug 41921: Add currently-running reports to the dashboard Test plan 1) Apply patch, restart all the things 2) prove t/db_dependent/api/v1/reports_jobs.t t/db_dependent/Koha/Reports.t 3) Browse to /cgi-bin/koha/reports/jobs.pl 4) Confirm the page shows 'No reports are running right now.' when nothing is in flight, and that the background report jobs table renders as before 5) Create an SQL report with body SELECT SLEEP(60), 6) Run it from the staff client in foreground mode 7) Confirm a row appears in the Currently running table 7) Refresh the view with the refresh button, note the data changes 8) Let the foreground report finish 9) Confirm the row disappears from the "Currently running" table on the next refresh 9) Run the same report as a background job 10) Confirm the row appears again with a Type of "background" while the worker is executing 11) Confirm the row disappears once the worker finishes from the running reports table when it has finished executing -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 David Cook <dcook@prosentient.com.au> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |katrin.fischer@bsz-bw.de --- Comment #47 from David Cook <dcook@prosentient.com.au> --- How long are report results stored? This could be a big problem in terms of data privacy. Adding Katrin in here as she might have some thoughts on that. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com --- Comment #48 from David Nind <david@davidnind.com> --- I managed to work my way through the test plans 8-) A couple of things I have noted: 1. When accessing the standard background jobs page (Administration > Jobs > Manage jobs) you get a browser popup with this error and table sits on "Processing ...": Something went wrong when loading the table 500: Internal Server error. Properties not allowed: report_id. Properties not allowed: report_id. Properties not allowed: report_id. Properties not allowed: report_id. Properties not allowed: report_id. .... In the developer tools console I get: GET http://127.0.0.1:8081/api/v1/jobs?only_current=0&_page=1&_per_page=20&_match=contains&_order_by=-me.enqueued_date 500 (Internal Server Error) Got 500 (Internal Server Error) 2. For the "Reports job" page for the "Currently running" section: - There is a "X Clear filter" option which is greyed out, but there is no search box or other controls apart from "Export" - Nothing works for me when entering anything in the column filters. - Filters (search + column) work OK in the "Background jobs" section. Consolidated test plan (so far) =============================== 1. Apply all the patches 2. Update the database: updatedatabase 3. Rebuild things (not sure if it is required): - yarn api:bundle - yarn build 4. DBIC (not sure if it is required): dbic 5. Update koha-conf.xml: - Edit koha-conf.xml: vi /etc/koha/sites/kohadev/koha-conf.xml - Added this after </plugin_repos> (near the end of file but before CSP section): <total_running_reports_per_instance_limit>1</total_running_reports_per_instance_limit> 6. Restart everything: restart_all 7. Tests pass: prove t/db_dependent/Koha/BackgroundJob/RunReport.t t/db_dependent/Koha/Reports.t t/Koha/Auth/Permissions.t t/db_dependent/api/v1/reports_jobs.t 8. Create a report using SQL: select * from biblio 9. Run through the individual test plans for: - Add ability to run reports as background jobs - Add check_limits to guided_reports.pl - Wire check_limits into svc/report and opac/svc/report - Add per-run-type overrides to report concurrency caps - Add a Report jobs dashboard for librarians - Add currently-running reports to the dashboard Add ability to run reports as background jobs ============================================= Check the different settings for the new system preference ReportsRunInBackground: - 'always run as a background job' (always): . Run the report . Confirm a job appears under Administration > Jobs > Manage jobs (admin/background_jobs.pl) with type 'Run report'. . Once finished, click 'View results' on the job page and confirm the stored result renders in the standard report-results view. - 'always run in the foreground' (never): . Run the report. . Confirm it executes in the foreground. - 'let the user choose' (user_choice): . Run the report. . Confirm the 'choose run mode' page is shown with a Background checkbox preselected, and the report runs accordingly. Results: 'always run as a background job' (always) ----------------------------------------- 1. After clicking "Run" action, goes to the "Report jobs" page: . Information message at the top of the page: "Your report has been queued as job #XX. It is highlighted below." . "Currently running" heading with "No reports are running right now" . "Background jobs" heading: lists background jobs with the current job highlighted with an orange border around the table . Clicking "View results" action: gives you the report results 2. When accessing the standard background jobs page (Administration > Jobs > Manage jobs) you get a browser popup with this error and the table sits on "Processing ...": Something went wrong when loading the table 500: Internal Server error. Properties not allowed: report_id. Properties not allowed: report_id. Properties not allowed: report_id. Properties not allowed: report_id. Properties not allowed: report_id. .... In the developer tools console I get: GET http://127.0.0.1:8081/api/v1/jobs?only_current=0&_page=1&_per_page=20&_match=contains&_order_by=-me.enqueued_date 500 (Internal Server Error) Got 500 (Internal Server Error) 'always run in the foreground' (never) -------------------------------------- 1. After clicking "Run" action, you get the report results page. 2. When accessing the background jobs page I get the same results as 'always' option 'let the user choose' (user_choice) ----------------------------------- 1. After clicking "Run" action, get: How would you like to run this report? "Run now" "Queue as background job" 2. Run now: report runs 3. Queue as background job: get "Report jobs" page 4. When accessing the background jobs page I get the same results as 'always' and 'never' options. Add check_limits to guided_reports.pl ===================================== 1. Added koha-conf.xml setting earlier, as got errors on the report pages otherwise. 2. Create a new saved report with this SQL: SELECT SLEEP(120) 3. Results from running the report in two different tabs: - First tab: eventually completes - Second tab: you eventually (but not straight away) get this message: The following error was encountered: There are too many total reports running at the moment. Please wait for some of them to finish before running this report again. Return to previous page 4. On the same two tabs using Download > Comma separated text (CSV): - First tab: eventually saves CSV - Second tab: get a page with a white background and "Too many reports are currently running. Please wait for some to finish before running this report." Wire check_limits into svc/report and opac/svc/report ===================================================== 1. Step 4: Make the report public. 2. Results as per the test plan. Add per-run-type overrides to report concurrency caps ===================================================== 1. Edit koha-conf.xml and change and add settings: <total_running_reports_per_instance_limit>5</total_running_reports_per_instance_limit> <foreground_total_running_reports_per_instance_limit>2</foreground_total_running_reports_per_instance_limit> 2. Restart everything: restart_all Add a Report jobs dashboard for librarians =========================================== 1. For the "Cancel" option to come up under "background jobs" section, need to start a couple of reports running. 2. Found it hard to test steps 8 and 9, so skipped. Add currently-running reports to the dashboard ============================================== Works as expected. However, note that for the "Currently running" section: 1. There is a "X Clear filter" option which is greyed out, but there is no search box or other controls apart from "Export" 2. Nothing works for me when entering anything in the column filters. 3. Filters (search + column) work OK in the "Background jobs" section. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #50 from David Nind <david@davidnind.com> --- The third thing to note (I forgot to include in my notes in comment #48): 3. In the reports table, the list of saved reports column is going to get very long after a while. Is there any way to remove these or collapse to only show the last few? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #52 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to Andrew Fuerste-Henry from comment #51)

(In reply to David Cook from comment #47)

...

How long are report results stored?

This could be a big problem in terms of data privacy.

Adding Katrin in here as she might have some thoughts on that.

Saved report results can be deleted by cleanup_database after a given number of days.

This cron is not scheduled by default as it's currently not needed if you don't also run reports from cron/command line. Maybe we should add it to the defaults? The table could get really really big pretty fast which is a bit of a risk. I am not sure how it helps the load to run as a background job - it's still the same query. Can someone explain what difference it actually makes on the server side or what the big advantage here is? -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #54 from David Cook <dcook@prosentient.com.au> --- (In reply to Katrin Fischer from comment #52)

I am not sure how it helps the load to run as a background job - it's still the same query. Can someone explain what difference it actually makes on the server side or what the big advantage here is?

There's a couple advantages that I can think of. If you have a big query/long running report, it might take a long time to run, and if it's synchronous your browser could time out. Also, if you do execute a long running report and then try to page through it, it would likely be slow. Whereas if you're viewing the results, you could page through them fairly quickly I suspect. A pro/con would also be that it captures a moment in time. The pro being that you could run a report and its results would be fixed, so you could rely on that report result again and again. The con would be that it would be stale immediately after running. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #56 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- (In reply to David Cook from comment #54)

(In reply to Katrin Fischer from comment #52)

...

I am not sure how it helps the load to run as a background job - it's still the same query. Can someone explain what difference it actually makes on the server side or what the big advantage here is?

There's a couple advantages that I can think of.

If you have a big query/long running report, it might take a long time to run, and if it's synchronous your browser could time out.

Also, if you do execute a long running report and then try to page through it, it would likely be slow. Whereas if you're viewing the results, you could page through them fairly quickly I suspect.

A pro/con would also be that it captures a moment in time. The pro being that you could run a report and its results would be fixed, so you could rely on that report result again and again. The con would be that it would be stale immediately after running.

But that means it won't help with the situation where a long running query creates load on the server. The advantage is storing it, so multiple people might use the results from within Koha. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #58 from David Cook <dcook@prosentient.com.au> --- (In reply to Katrin Fischer from comment #56)

But that means it won't help with the situation where a long running query creates load on the server. The advantage is storing it, so multiple people might use the results from within Koha.

Yes, a long running query will still create load on the server. That said, if we wanted to handle that better, we could create the ability for Koha to use a different database connection to connect to a read-only MySQL instance in a database cluster. Another option is allowing a separate database connection for the same database, but using a user with more limited database permissions. This would actually be useful for a number of security reasons as well, although could lead to reduced report functionality. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #60 from David Nind <david@davidnind.com> --- (In reply to David Cook from comment #59)

I think there's a bug report somewhere for adding max_execution_time which would be valuable as well.

Bug 39164 -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Blocks| |43016 Referenced Bugs: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=43016 [Bug 43016] [OMNIBUS] Server resource protection -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Michelle Spinney <mspinney@clamsnet.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |mspinney@clamsnet.org -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Patch doesn't apply --- Comment #61 from Andrew Fuerste-Henry <andrew@bywatersolutions.com> --- I get: Error: Apply failed: Git command (git am -3 /tmp/LY9bEjfeu6/0001-199567.patch) failed: error: sha1 information is lacking or useless (koha-tmpl/intranet-tmpl/prog/en/modules/reports/guided_reports_start.tt). error: could not build fake ancestor -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #62 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 203106 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203106&action=edit Bug 41921: Add syspref and split execute_reports permission Split the execute_reports user permission into execute_reports_foreground and execute_reports_background. Adds ReportsRunInBackground system preference the controls which types of reports running are available for this instance. -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #64 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 203108 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203108&action=edit Bug 41921: Add Koha::Reports->check_limits Consolidates the per-user duplicate, per-user total, and per-instance total concurrent-run checks into a single method. Adds the count of queued run_report background jobs to the total. Test plan 1) Apply patch 2) prove t/db_dependent/Koha/Reports.t -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #66 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 203110 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203110&action=edit Bug 41921: Add check_limits to guided_reports.pl Wires check_limits to the guided reports script. Test plan 1) Apply patch 2) prove t/db_dependent/Koha/Reports.t 3) Set total_running_reports_per_instance_limit to 1 in koha-conf.xml 4) Run two long reports ( "SELECT SLEEP(120)" works well) in two tabs 5) Confirm the second tab shows the 'too many total reports' alert 6) Click 'Export' on a saved report under the same conditions and confirm the export request returns 429 with a 'too many reports' message -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #68 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 203112 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203112&action=edit Bug 41921: Add per-run-type overrides to report concurrency caps The existing caps in koha-conf.xml apply to every run type, so an admin who wanted to leave plenty of headroom for background jobs but keep a tight cap on foreground reports had no way to express that. This commit adds six new keys that override the combined caps per run type. Each existing cap now has matching foreground_ and background_ keys. A value of 0 in any key means no limit, matching how the combined keys already behave. When a run-type key is non-zero it replaces the combined key for that run type and only counts same-run-type queries: - foreground_X caps count foreground processlist queries only. - background_X caps count background processlist queries plus queued run_report jobs (queued jobs are always background). Test plan 1) Apply patch 2) prove t/db_dependent/Koha/Reports.t 3) Set total_running_reports_per_instance_limit to 5 and foreground_total_running_reports_per_instance_limit to 2 in koha-conf.xml, restart all the things 4) Start 2 long foreground reports 5) Start a third foreground report, confirm the 'too many total reports' alert is shown 6) Queue 4 background reports (ReportsRunInBackground=always or the choose-run-mode page) 7) Confirm all 4 are enqueued (foreground cap doesn't apply to background, combined cap allows 5) 8) Queue a 5th, confirm it is enqueued, queue a 6th and confirm it is blocked by the combined cap 9) Unset the foreground override, set background_total_running_reports_per_instance_limit to 3 and restart, repeat steps 5 and 7 confirming the bg cap now applies -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #70 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 203114 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203114&action=edit Bug 41921: Add currently-running reports to the dashboard Test plan 1) Apply patch, restart all the things 2) prove t/db_dependent/api/v1/reports_jobs.t t/db_dependent/Koha/Reports.t 3) Browse to /cgi-bin/koha/reports/jobs.pl 4) Confirm the page shows 'No reports are running right now.' when nothing is in flight, and that the background report jobs table renders as before 5) Create an SQL report with body SELECT SLEEP(60), 6) Run it from the staff client in foreground mode 7) Confirm a row appears in the Currently running table 7) Refresh the view with the refresh button, note the data changes 8) Let the foreground report finish 9) Confirm the row disappears from the "Currently running" table on the next refresh 9) Run the same report as a background job 10) Confirm the row appears again with a Type of "background" while the worker is executing 11) Confirm the row disappears once the worker finishes from the running reports table when it has finished executing -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 --- Comment #72 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Created attachment 203116 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=203116&action=edit Bug 41921: (follow-up) Tidy Patch from commit 57c66ef -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Patch complexity|--- |Medium patch -- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Failed QA --- Comment #74 from Andrew Fuerste-Henry <andrew@bywatersolutions.com> --- (In reply to Lucas Gass (lukeg) from comment #67) This is the one bit of this test plan that didn't work for me. When I set that limit in the koha-conf and started one long report running I was still able to get results via the svc URL

3) Set total_running_reports_per_instance_limit to 1 in koha-conf.xml 4) Start a long report through the staff UI ( e.g. "SELECT SLEEP(120)" ) 5) Browse to /cgi-bin/koha/svc/report?id=N (while authenticated) and confirm the response is 429 with body {"error":"total_running_reports_per_instance_limit_exceeded"} 6) Browse to /cgi-bin/koha/opac/svc/report?id=N (while logged out) and confirm the same 429 response 7) Reset the limit 8) Repeat steps 4-6 9) Confirm normal JSON results

-- You are receiving this mail because: You are watching all bug changes.

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=41921 Andrew Fuerste-Henry <andrew@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Needs Signoff |Failed QA --- Comment #76 from Andrew Fuerste-Henry <andrew@bywatersolutions.com> --- Test plans do include testing the new permissions execute_reports_foreground and execute_reports_background. These do not seem to be enforcing correctly. To recreate: - set ReportsRunInBackground to "let the user choose" - log in as a user with execute_reports_foreground but not execute_reports_background - run a report, see you are given both foreground and background options ** we should not present the user with the background option when they don't have the background permission. Ideally, they'd bypass the choice of run method entirely as they only have one option ** - click "Queue background job," not the job enqueues and runs successfully ** in addition to not giving the user the background option, we should block the job from enqueueing if they try to sneak it in by entering the url or something ** - reverse your user's permissions so they do have background permissions but do not have foreground permissions - observe they are still able to run foreground reports ** again, we should not present them with a choice of how to run and we should block foreground reports that they attempt to run by some other means ** - set ReportsRunInBackground to "always run in the foreground" - your user still has background permission but not foreground - confirm that they get a Run button and reports run in the foreground when they click it ** if the syspref is set to always foreground, users without foreground permissions should not get a Run button and not be able to run any reports ** - set ReportsRunInBackground to "always run in the background" - edit your user to have foreground permissions but not background - confirm that they get a Run button and reports run in the foreground when they click it ** if the syspref is set to always background, users without background permissions should not get a Run button and not be able to run any reports ** -- You are receiving this mail because: You are watching all bug changes.