Summary: | Show saved SQL report ID in database query | ||
---|---|---|---|
Product: | Koha | Reporter: | paxed <pasi.kallinen> |
Component: | Reports | Assignee: | Kyle M Hall (khall) <kyle> |
Status: | CLOSED FIXED | QA Contact: | Testopia <testopia> |
Severity: | enhancement | ||
Priority: | P5 - low | CC: | david, fridolin.somers, jonathan.druart, kyle, lucas |
Version: | unspecified | ||
Hardware: | All | ||
OS: | All | ||
Change sponsored?: | --- | Patch complexity: | Trivial patch |
Documentation contact: | Documentation submission: | ||
Text to go in the release notes: |
This enhancement shows a 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.
|
Version(s) released in: |
22.11.00
|
Circulation function: | |||
Attachments: |
Bug 29579: Show saved SQL report ID in database query
Bug 29579: Show saved SQL report ID in database query Bug 29579: Show saved SQL report ID in database query Bug 29579: Show saved SQL report ID in database query Bug 29579: Show saved SQL report ID in database query Bug 29579: Show saved SQL report ID in database query Bug 29579: (QA follow-up) Fix up t/db_dependent/Koha/Reports.t Bug 29579: Show saved SQL report ID in database query Bug 29579: (QA follow-up) Fix up t/db_dependent/Koha/Reports.t |
Description
paxed
2021-11-26 11:24:18 UTC
Created attachment 128037 [details] [review] 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. 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? Created attachment 130496 [details] [review] 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> Tested using the query "select sleep(10)" Created attachment 130538 [details] [review] 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> (In reply to Owen Leonard from comment #4) > Tested using the query "select sleep(10)" Nice tip - thanks Owen! Pushed to master for 22.05, thanks to everybody involved [U+1F984] t/db_dependent/Reports/Guided.t is failing because of this patch. (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. I revert from master Also fails : t_db_dependent_Reports_Guided_t.Email report test See https://jenkins.koha-community.org/view/master/job/Koha_Master_D10/529/testReport/ Created attachment 137989 [details] [review] 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*/ 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. Created attachment 137990 [details] [review] 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 */ Created attachment 138068 [details] [review] 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 need to adjust t/db_dependent/Koha/Reports.t Created attachment 138460 [details] [review] Bug 29579: (QA follow-up) Fix up t/db_dependent/Koha/Reports.t Created attachment 138466 [details] [review] 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> Created attachment 138467 [details] [review] Bug 29579: (QA follow-up) Fix up t/db_dependent/Koha/Reports.t Signed-off-by: Jonathan Druart <jonathan.druart@bugs.koha-community.org> Pushed to master for 22.11. Nice work everyone, thanks! Enhancement will not be backported to 22.05.x series |