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: [email protected]
Reporter: [email protected]
QA Contact: [email protected]
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.
_______________________________________________
Koha-bugs mailing list
[email protected]
https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-bugs
website : http://www.koha-community.org/
git : http://git.koha-community.org/
bugs : http://bugs.koha-community.org/
