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.