[Bug 29579] New: Show saved SQL report ID in database query
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Bug ID: 29579 Summary: Show saved SQL report ID in database query Change sponsored?: --- Product: Koha Version: unspecified Hardware: All OS: All Status: NEW Severity: new feature Priority: P5 - low Component: Reports Assignee: koha-bugs@lists.koha-community.org Reporter: pasi.kallinen@koha-suomi.fi QA Contact: testopia@bugs.koha-community.org When users run saved SQL reports, some of them might cause problems on the database end, for example taking too long or requiring too much resources. It would be useful if it was possible to see in the database server end which saved SQL report was the culprit. -- 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=29579 paxed <pasi.kallinen@koha-suomi.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |pasi.kallinen@koha-suomi.fi |ity.org | Status|NEW |Needs Signoff --- Comment #1 from paxed <pasi.kallinen@koha-suomi.fi> --- Created attachment 128037 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=128037&action=edit Bug 29579: Show saved SQL report ID in database query Bug 29579: Show saved SQL report ID in database query When trying to figure out which saved SQL report caused too much load, it's useful to have the report id show in the mysql process list. This patch adds the saved SQL ID number as a comment line in front of the SQL before passing it to the database. To test: 1) Run a saved report that takes long enough time, so you can: 2) Connect to the database with your preferred client, and use "show processlist;" to list all the running mysql processes. 3) The running saved SQL report should show up with "-- saved_sql.id=123" in the process info field. -- 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=29579 paxed <pasi.kallinen@koha-suomi.fi> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|new feature |enhancement Patch complexity|--- |Trivial patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |david@davidnind.com --- Comment #2 from David Nind <david@davidnind.com> --- I tried testing this using koha-testing-koha. However, I couldn't create a report that would run long enough as there are only 400-500 records in the sample database. Do you have a complicated report that takes a while to run, or do I just need to import a large number of MARC records? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Owen Leonard <oleonard@myacpl.org> 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=29579 Owen Leonard <oleonard@myacpl.org> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #128037|0 |1 is obsolete| | --- Comment #3 from Owen Leonard <oleonard@myacpl.org> --- Created attachment 130496 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=130496&action=edit Bug 29579: Show saved SQL report ID in database query When trying to figure out which saved SQL report caused too much load, it's useful to have the report id show in the mysql process list. This patch adds the saved SQL ID number as a comment line in front of the SQL before passing it to the database. To test: 1) Run a saved report that takes long enough time, so you can: 2) Connect to the database with your preferred client, and use "show processlist;" to list all the running mysql processes. 3) The running saved SQL report should show up with "-- saved_sql.id=123" in the process info field. Signed-off-by: Owen Leonard <oleonard@myacpl.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 --- Comment #4 from Owen Leonard <oleonard@myacpl.org> --- Tested using the query "select sleep(10)" -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #130496|0 |1 is obsolete| | --- Comment #5 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- Created attachment 130538 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=130538&action=edit Bug 29579: Show saved SQL report ID in database query When trying to figure out which saved SQL report caused too much load, it's useful to have the report id show in the mysql process list. This patch adds the saved SQL ID number as a comment line in front of the SQL before passing it to the database. To test: 1) Run a saved report that takes long enough time, so you can: 2) Connect to the database with your preferred client, and use "show processlist;" to list all the running mysql processes. 3) The running saved SQL report should show up with "-- saved_sql.id=123" in the process info field. Signed-off-by: Owen Leonard <oleonard@myacpl.org> Signed-off-by: Katrin Fischer <katrin.fischer.83@web.de> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 --- Comment #6 from David Nind <david@davidnind.com> --- (In reply to Owen Leonard from comment #4)
Tested using the query "select sleep(10)"
Nice tip - thanks Owen! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Text to go in the| |This enhancement shows a release notes| |saved SQL report's ID in | |the database process list | |(from example, "-- | |saved_sql.id=123"). This | |can help when | |troubleshooting reports | |that are causing issues, | |such as taking too long to | |run or taking up too many | |system resources. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |22.05.00 released in| | Status|Passed QA |Pushed to master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 --- Comment #7 from Fridolin Somers <fridolin.somers@biblibre.com> --- Pushed to master for 22.05, thanks to everybody involved 🦄 -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords| |additional_work_needed CC| |jonathan.druart+koha@gmail. | |com --- Comment #8 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- t/db_dependent/Reports/Guided.t is failing because of this patch. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 --- Comment #9 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- (In reply to Jonathan Druart from comment #8)
t/db_dependent/Reports/Guided.t is failing because of this patch.
# Failed test 'Wrong SQL syntax raises warning' # at t/db_dependent/Reports/Guided.t line 264. # found warning: Use of uninitialized value $report_id in concatenation (.) or string at /kohadevbox/k oha/C4/Reports/Guided.pm line 596. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to master |ASSIGNED CC| |fridolin.somers@biblibre.co | |m -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Fridolin Somers <fridolin.somers@biblibre.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|22.05.00 | released in| | --- Comment #10 from Fridolin Somers <fridolin.somers@biblibre.com> --- I revert from master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 --- Comment #11 from Fridolin Somers <fridolin.somers@biblibre.com> --- Also fails : t_db_dependent_Reports_Guided_t.Email report test See https://jenkins.koha-community.org/view/master/job/Koha_Master_D10/529/testR... -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|pasi.kallinen@koha-suomi.fi |kyle@bywatersolutions.com CC| |kyle@bywatersolutions.com -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|ASSIGNED |Needs Signoff -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #130538|0 |1 is obsolete| | --- Comment #12 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 137989 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=137989&action=edit Bug 29579: Show saved SQL report ID in database query When trying to figure out which saved SQL report caused too much load, it's useful to have the report id show in the mysql process list. This patch adds the saved SQL ID number as a comment line in front of the SQL before passing it to the database. Test Plan: 1) Apply this patch 2) Restart all the things! 3) Create a long running query like: SELECT * FROM borrowers a, borrowers b, borrowers c, borrowers d 4) Connect to the database using koha-mysql 5) Execute "show processlist;" 6) Note the Info column looks something like: SELECT * FROM borrowers a, borrowers b, borrowers c, borrowers d /*saved_sql.id: 1*/ -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 --- Comment #13 from Kyle M Hall <kyle@bywatersolutions.com> --- I was working on this very same feature when I found this bug report. My version does pass unit tests, probably due to the different commenting style I chose. I changed up my string to more closely match what paxed did. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #137989|0 |1 is obsolete| | --- Comment #14 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 137990 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=137990&action=edit Bug 29579: Show saved SQL report ID in database query When trying to figure out which saved SQL report caused too much load, it's useful to have the report id show in the mysql process list. This patch adds the saved SQL ID number as a comment line in front of the SQL before passing it to the database. Test Plan: 1) Apply this patch 2) Restart all the things! 3) Create a long running query like: SELECT * FROM borrowers a, borrowers b, borrowers c, borrowers d 4) Connect to the database using koha-mysql 5) Execute "show processlist;" 6) Note the Info column looks something like: SELECT * FROM borrowers a, borrowers b, borrowers c, borrowers d /* saved_sql.id: 1 */ -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Keywords|additional_work_needed | -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 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=29579 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #137990|0 |1 is obsolete| | --- Comment #15 from David Nind <david@davidnind.com> --- Created attachment 138068 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=138068&action=edit Bug 29579: Show saved SQL report ID in database query When trying to figure out which saved SQL report caused too much load, it's useful to have the report id show in the mysql process list. This patch adds the saved SQL ID number as a comment line in front of the SQL before passing it to the database. Test Plan: 1) Apply this patch 2) Restart all the things! 3) Create a long running query like: SELECT * FROM borrowers a, borrowers b, borrowers c, borrowers d 4) Connect to the database using koha-mysql 5) Execute "show processlist;" 6) Note the Info column looks something like: SELECT * FROM borrowers a, borrowers b, borrowers c, borrowers d /* saved_sql.id: 1 */ 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=29579 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Failed QA --- Comment #16 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- You need to adjust 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=29579 Kyle M Hall <kyle@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Failed QA |Signed Off -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 --- Comment #17 from Kyle M Hall <kyle@bywatersolutions.com> --- Created attachment 138460 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=138460&action=edit Bug 29579: (QA follow-up) Fix up 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=29579 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Signed Off |Passed QA -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #138068|0 |1 is obsolete| | Attachment #138460|0 |1 is obsolete| | --- Comment #18 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 138466 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=138466&action=edit Bug 29579: Show saved SQL report ID in database query When trying to figure out which saved SQL report caused too much load, it's useful to have the report id show in the mysql process list. This patch adds the saved SQL ID number as a comment line in front of the SQL before passing it to the database. Test Plan: 1) Apply this patch 2) Restart all the things! 3) Create a long running query like: SELECT * FROM borrowers a, borrowers b, borrowers c, borrowers d 4) Connect to the database using koha-mysql 5) Execute "show processlist;" 6) Note the Info column looks something like: SELECT * FROM borrowers a, borrowers b, borrowers c, borrowers d /* saved_sql.id: 1 */ Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 --- Comment #19 from Jonathan Druart <jonathan.druart+koha@gmail.com> --- Created attachment 138467 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=138467&action=edit Bug 29579: (QA follow-up) Fix up t/db_dependent/Koha/Reports.t Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Jonathan Druart <jonathan.druart+koha@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Text to go in the|This enhancement shows a |This enhancement shows a release notes|saved SQL report's ID in |saved SQL report's ID in |the database process list |the database process list |(from example, "-- |(from example, "/* |saved_sql.id=123"). This |saved_sql.id: 123 */). This |can help when |can help when |troubleshooting reports |troubleshooting reports |that are causing issues, |that are causing issues, |such as taking too long to |such as taking too long to |run or taking up too many |run or taking up too many |system resources. |system resources. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Tomás Cohen Arazi <tomascohen@gmail.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |22.11.00 released in| | Status|Passed QA |Pushed to master -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 --- Comment #20 from Tomás Cohen Arazi <tomascohen@gmail.com> --- Pushed to master for 22.11. Nice work everyone, thanks! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=29579 Lucas Gass <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|Pushed to master |RESOLVED CC| |lucas@bywatersolutions.com Resolution|--- |FIXED --- Comment #21 from Lucas Gass <lucas@bywatersolutions.com> --- Enhancement will not be backported to 22.05.x series -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org