[Bug 42225] New: On sites with many vendors spent.pl cannot load
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 Bug ID: 42225 Summary: On sites with many vendors spent.pl cannot load Initiative type: --- Sponsorship --- status: Product: Koha Version: Main Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Acquisitions Assignee: koha-bugs@lists.koha-community.org Reporter: nick@bywatersolutions.com QA Contact: testopia@bugs.koha-community.org When attempting to click on the spent amount we found that a site with many vendors (~260k) there is a MySQL error: ERROR 1038 (HY001): Out of sort memory, consider increasing server sort buffer size spent.pl uses a large query: 57 my $query = <<EOQ; 58 SELECT 59 aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber, 60 quantity-quantityreceived AS tleft, 61 budgetdate, entrydate, 62 aqbasket.booksellerid, 63 GROUP_CONCAT(DISTINCT itype SEPARATOR '|') as itypes, 64 title, 65 aqorders.invoiceid, 66 aqinvoices.invoicenumber, 67 quantityreceived, 68 $unitprice_field, 69 datereceived, 70 aqbooksellers.name as vendorname 71 FROM (aqorders, aqbasket) 72 LEFT JOIN biblio ON 73 biblio.biblionumber=aqorders.biblionumber 74 LEFT JOIN aqorders_items ON 75 aqorders.ordernumber = aqorders_items.ordernumber 76 LEFT JOIN items ON 77 aqorders_items.itemnumber = items.itemnumber 78 LEFT JOIN aqinvoices ON 79 aqorders.invoiceid = aqinvoices.invoiceid 80 LEFT JOIN aqbooksellers ON 81 aqbasket.booksellerid = aqbooksellers.id 82 WHERE 83 aqorders.basketno=aqbasket.basketno AND 84 budget_id=? AND 85 datecancellationprinted IS NULL AND 86 datereceived IS NOT NULL 87 GROUP BY aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber, 88 tleft, 89 budgetdate, entrydate, 90 aqbasket.booksellerid, 91 title, 92 aqorders.invoiceid, 93 aqinvoices.invoicenumber, 94 quantityreceived, 95 $unitprice_field, 96 datereceived, 97 aqbooksellers.name 98 99 EOQ It seems to work if I join the booksellers after the grouping: SELECT biblionumber, basketno, ordernumber, tleft, budgetdate, entrydate, booksellerid, itypes, title, invoiceid, invoicenumber, quantityreceived, unitprice_tax_included, datereceived, aqbooksellers.name as vendorname FROM (SELECT aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber, quantity-quantityreceived AS tleft, budgetdate, entrydate, aqbasket.booksellerid, GROUP_CONCAT(DISTINCT itype SEPARATOR '|') as itypes, title, aqorders.invoiceid, aqinvoices.invoicenumber, quantityreceived, unitprice_tax_included, datereceived FROM (aqorders, aqbasket) LEFT JOIN biblio ON biblio.biblionumber=aqorders.biblionumber LEFT JOIN aqorders_items ON aqorders.ordernumber = aqorders_items.ordernumber LEFT JOIN items ON aqorders_items.itemnumber = items.itemnumber LEFT JOIN aqinvoices ON aqorders.invoiceid = aqinvoices.invoiceid WHERE aqorders.basketno=aqbasket.basketno AND budget_id=44 AND datecancellationprinted IS NULL AND datereceived IS NOT NULL GROUP BY aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber, tleft, budgetdate, entrydate, aqbasket.booksellerid, title, aqorders.invoiceid, aqinvoices.invoicenumber, quantityreceived, unitprice_tax_included, datereceived ) orders LEFT JOIN aqbooksellers ON orders.booksellerid = aqbooksellers.id -- 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=42225 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Status|NEW |Needs Signoff -- 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=42225 --- Comment #1 from Nick Clemens (kidclamp) <nick@bywatersolutions.com> --- Created attachment 196745 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=196745&action=edit Bug 42225: Improve SQL on spent.pl To test: 1 - Have some received orders and some invoice adjustments 2 - View spent.pl for a fund 3 - Apply patch, restart_all 4 - Reload 5 - Confirm the display has not changed -- 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=42225 Nick Clemens (kidclamp) <nick@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@gmail.com, | |mark@hofstetter.at -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 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. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #196745|0 |1 is obsolete| | -- 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=42225 --- Comment #2 from David Nind <david@davidnind.com> --- Created attachment 197114 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=197114&action=edit Bug 42225: Improve SQL on spent.pl To test: 1 - Have some received orders and some invoice adjustments 2 - View spent.pl for a fund 3 - Apply patch, restart_all 4 - Reload 5 - Confirm the display has not changed Signed-off-by: David Nind <david@davidnind.com> -- 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=42225 David Nind <david@davidnind.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Assignee|koha-bugs@lists.koha-commun |nick@bywatersolutions.com |ity.org | CC| |david@davidnind.com Text to go in the| |This improves the SQL for release notes| |the spent by fund report | |(Acquisitions > [All | |available funds section of | |the page] > [select amount | |in the spent column for a | |fund that has a link]). | | | |This fixes an issue where | |there was a VERY large | |number of vendors in a | |system (260k + !!!), which | |resulted in a database | |error: | | ERROR 1038 (HY001): Out | |of sort memory, consider | | increasing server sort | |buffer size --- Comment #3 from David Nind <david@davidnind.com> --- Testing notes (using KTD): 1. To view the amount spent by funds report (spent.pl): 1.1 Go to the acquisitions page 1.2 Scroll down to the "All available funds" section of the page 1.3 In the "Spent" column, click on the linked $ amount (for example, for the Fund 1_2 row) 1.4 The "Spent - Fund: Fund 1_2" report is shown. (Added assignee.) -- 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=42225 --- Comment #4 from Jonathan Druart <jonathan.druart@gmail.com> --- Just an idea: wouldn't be easier to fetch the vendor's ids, then run one query per vendor? Do we expect a lot of vendors per fund? -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 Angela Berrett <angela.berrett@familysearch.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |angela.berrett@familysearch | |.org --- Comment #5 from Angela Berrett <angela.berrett@familysearch.org> --- We create our funds by international regions, which does end up putting a lot of vendors into a single fund. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 Paul Derscheid <paul.derscheid@lmscloud.de> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|me@paulderscheid.xyz |paul.derscheid@lmscloud.de Status|Signed Off |Passed QA Patch complexity|--- |Small patch -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 --- Comment #6 from Paul Derscheid <paul.derscheid@lmscloud.de> --- Created attachment 201858 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=201858&action=edit Bug 42225: Improve SQL on spent.pl To test: 1 - Have some received orders and some invoice adjustments 2 - View spent.pl for a fund 3 - Apply patch, restart_all 4 - Reload 5 - Confirm the display has not changed Signed-off-by: David Nind <david@davidnind.com> Signed-off-by: Paul Derscheid <paul.derscheid@lmscloud.de> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 Paul Derscheid <paul.derscheid@lmscloud.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Attachment #197114|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=42225 --- Comment #7 from Paul Derscheid <paul.derscheid@lmscloud.de> --- Created attachment 201859 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=201859&action=edit Bug 42225: (QA follow-up) Use configured unitprice field in outer SELECT The outer SELECT hardcoded unitprice_tax_included while the inner subquery selects the field returned by FieldsForCalculatingFundValues. With CalculateFundValuesIncludingTax disabled the derived table only exposes unitprice_tax_excluded, so spent.pl dies with: Unknown column 'unitprice_tax_included' in 'field list' To test: 1. Set CalculateFundValuesIncludingTax to 'Exclude' 2. View spent.pl for a fund with received orders 3. Observe error 500 4. Apply patch, restart_all 5. Reload, page renders correctly 6. Set CalculateFundValuesIncludingTax back to 'Include' 7. Reload, page still renders correctly Signed-off-by: Paul Derscheid <paul.derscheid@lmscloud.de> -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 --- Comment #8 from Paul Derscheid <paul.derscheid@lmscloud.de> --- Not sure about the best approach but could and should we cover this with tests? Would mean moving the inlined sql into a module, not sure whether C4::Budgets (probably not) or Koha namespace. But think this is out of scope here and should be done in a follow-up bug. -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 Lisette Scheer <lisette@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- QA Contact|testopia@bugs.koha-communit |me@paulderscheid.xyz |y.org | CC| |lisette@bywatersolutions.co | |m -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 Pedro Amorim (ammopt) <pedro.amorim@openfifth.co.uk> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)| |26.11.00 released in| | Status|Passed QA |Pushed to main -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 --- Comment #9 from Pedro Amorim (ammopt) <pedro.amorim@openfifth.co.uk> --- Thanks everyone! Pushed to main for 26.11! -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 Lucas Gass (lukeg) <lucas@bywatersolutions.com> changed: What |Removed |Added ---------------------------------------------------------------------------- Version(s)|26.11.00 |26.11.00,26.05.03 released in| | Status|Pushed to main |Pushed to stable -- You are receiving this mail because: You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=42225 --- Comment #10 from Lucas Gass (lukeg) <lucas@bywatersolutions.com> --- Nice work everyone! Pushed to 26.05.x for the upcoming 26.05.03 release. -- You are receiving this mail because: You are watching all bug changes.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org