Out of sort memory, consider increasing server sort buffer size
Good morning Koha, Newbie question: why do we have 16 fields in GROUP BYs ? Are they really all necessary to eliminate duplicate lines in budgets? Genuine question. When in acqui-home.pl, clicking certain entries linking to spent.pl just crashes with the error listed in the mail's object: Out of memory. Although I hate that solution, I went with it, increased sort_buffer_size 64x fold (I'm a generous guy). Still not enough. Removing most lines in GROUP BY of spent.pl solves it all, of course. -- Philippe Blouin, Directeur de la technologie Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com <mailto:philippe.blouin@inLibro.com> inLibro | pour esprit libre | www.inLibro.com <http://www.inLibro.com>
Hi Philippe, See bug 21622. It is necessary to not explode with "$field is not in group by" (when sql_mode has ONLY_FULL_GROUP_BY) However we may need to discuss the need of this flag if it brings us performance issues. Cheers, Jonathan Le jeu. 12 déc. 2019 à 23:23, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
Good morning Koha,
Newbie question: why do we have 16 fields in GROUP BYs ? Are they really all necessary to eliminate duplicate lines in budgets? Genuine question.
When in acqui-home.pl, clicking certain entries linking to spent.pl just crashes with the error listed in the mail's object: Out of memory. Although I hate that solution, I went with it, increased sort_buffer_size 64x fold (I'm a generous guy). Still not enough.
Removing most lines in GROUP BY of spent.pl solves it all, of course.
-- Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
Thanks, Ok, I've never seen the "$field is not in group by" error. Interesting. I've followed the bread crumbs: 21622 -> 20182 -> 21723 -> 22260... read it all. It's "damned if you do, damned if you don't". ONLY_FULL_GROUP_BY mode is the SQL Standard, I don't think it would be wise to stray from that. (I can do it locally, but I don't think Koha should, philosophically) But my client can't click on spent.pl. And no memory setting seems to fix it. So what's my solution? Could the query be reworked in two steps? This is not a screen where 0.2s will make a difference in the user experience. Suggestions welcomed. Philippe Blouin, Directeur de la technologie Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com <mailto:philippe.blouin@inLibro.com> inLibro | pour esprit libre | www.inLibro.com <http://www.inLibro.com> On 2019-12-13 4:33 a.m., Jonathan Druart wrote:
Hi Philippe,
See bug 21622. It is necessary to not explode with "$field is not in group by" (when sql_mode has ONLY_FULL_GROUP_BY) However we may need to discuss the need of this flag if it brings us performance issues.
Cheers, Jonathan
Le jeu. 12 déc. 2019 à 23:23, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
Good morning Koha,
Newbie question: why do we have 16 fields in GROUP BYs ? Are they really all necessary to eliminate duplicate lines in budgets? Genuine question.
When in acqui-home.pl, clicking certain entries linking to spent.pl just crashes with the error listed in the mail's object: Out of memory. Although I hate that solution, I went with it, increased sort_buffer_size 64x fold (I'm a generous guy). Still not enough.
Removing most lines in GROUP BY of spent.pl solves it all, of course.
-- Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
How big is your tables? As you can see SQL query is really ugly, and can be good to split it anyway... Le ven. 13 déc. 2019 à 15:26, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
Thanks,
Ok, I've never seen the "$field is not in group by" error. Interesting.
I've followed the bread crumbs: 21622 -> 20182 -> 21723 -> 22260... read it all. It's "damned if you do, damned if you don't".
ONLY_FULL_GROUP_BY mode is the SQL Standard, I don't think it would be wise to stray from that. (I can do it locally, but I don't think Koha should, philosophically)
But my client can't click on spent.pl. And no memory setting seems to fix it. So what's my solution? Could the query be reworked in two steps? This is not a screen where 0.2s will make a difference in the user experience.
Suggestions welcomed.
Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com On 2019-12-13 4:33 a.m., Jonathan Druart wrote:
Hi Philippe,
See bug 21622. It is necessary to not explode with "$field is not in group by" (when sql_mode has ONLY_FULL_GROUP_BY) However we may need to discuss the need of this flag if it brings us performance issues.
Cheers, Jonathan
Le jeu. 12 déc. 2019 à 23:23, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
Good morning Koha,
Newbie question: why do we have 16 fields in GROUP BYs ? Are they really all necessary to eliminate duplicate lines in budgets? Genuine question.
When in acqui-home.pl, clicking certain entries linking to spent.pl just crashes with the error listed in the mail's object: Out of memory. Although I hate that solution, I went with it, increased sort_buffer_size 64x fold (I'm a generous guy). Still not enough.
Removing most lines in GROUP BY of spent.pl solves it all, of course.
-- Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
minuscule: * 2000 biblio, 12000 items * 8 aqbudgets, 200 invoices, 25 booksellers, 60 baskets, 240 orders. That makes little sense... Philippe Blouin, Directeur de la technologie Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com <mailto:philippe.blouin@inLibro.com> inLibro | pour esprit libre | www.inLibro.com <http://www.inLibro.com> On 2019-12-13 9:49 a.m., Jonathan Druart wrote:
How big is your tables? As you can see SQL query is really ugly, and can be good to split it anyway...
Le ven. 13 déc. 2019 à 15:26, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
Thanks,
Ok, I've never seen the "$field is not in group by" error. Interesting.
I've followed the bread crumbs: 21622 -> 20182 -> 21723 -> 22260... read it all. It's "damned if you do, damned if you don't".
ONLY_FULL_GROUP_BY mode is the SQL Standard, I don't think it would be wise to stray from that. (I can do it locally, but I don't think Koha should, philosophically)
But my client can't click on spent.pl. And no memory setting seems to fix it. So what's my solution? Could the query be reworked in two steps? This is not a screen where 0.2s will make a difference in the user experience.
Suggestions welcomed.
Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com On 2019-12-13 4:33 a.m., Jonathan Druart wrote:
Hi Philippe,
See bug 21622. It is necessary to not explode with "$field is not in group by" (when sql_mode has ONLY_FULL_GROUP_BY) However we may need to discuss the need of this flag if it brings us performance issues.
Cheers, Jonathan
Le jeu. 12 déc. 2019 à 23:23, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
Good morning Koha,
Newbie question: why do we have 16 fields in GROUP BYs ? Are they really all necessary to eliminate duplicate lines in budgets? Genuine question.
When in acqui-home.pl, clicking certain entries linking to spent.pl just crashes with the error listed in the mail's object: Out of memory. Although I hate that solution, I went with it, increased sort_buffer_size 64x fold (I'm a generous guy). Still not enough.
Removing most lines in GROUP BY of spent.pl solves it all, of course.
-- Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
A month later... Maryse found a (recommanded) solution for the aqspent.pl crashes: - aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber, - quantity-quantityreceived AS tleft, - budgetdate, entrydate, - aqbasket.booksellerid, + MAX(aqorders.biblionumber) AS biblionumber, MAX(aqorders.basketno) AS basketno, aqorders.ordernumber, + MAX(quantity)-MAX(quantityreceived) AS tleft, + MAX(budgetdate) AS budgetdate, MAX(entrydate) AS entrydate, + MAX(aqbasket.booksellerid) AS booksellerid, GROUP_CONCAT(DISTINCT itype SEPARATOR '|') as itypes, - title, - aqorders.invoiceid, - aqinvoices.invoicenumber, - quantityreceived, - unitprice_tax_included, - datereceived, - aqbooksellers.name as vendorname + MAX(title) AS title, + MAX(aqorders.invoiceid) AS invoiceid, + MAX(aqinvoices.invoicenumber) AS invoicenumber, + MAX(quantityreceived) AS quantityreceived, + MAX(unitprice_tax_included) AS unitprice_tax_included, + MAX(datereceived) AS datereceived, + MAX(aqbooksellers.name) as vendorname We'll hack that in all our distributions. But I'd really like to have an official distributed solution to the problem. To me, it's not that much uglier than the fix adding all the column to avoid the problem with ONLY_FULL_GROUP_BY. What's your take on it? Regards, Philippe Blouin, Directeur de la technologie Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com <mailto:philippe.blouin@inLibro.com> inLibro | pour esprit libre | www.inLibro.com <http://www.inLibro.com> On 2019-12-13 11:25 a.m., Philippe Blouin wrote:
minuscule:
* 2000 biblio, 12000 items * 8 aqbudgets, 200 invoices, 25 booksellers, 60 baskets, 240 orders.
That makes little sense...
Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com <mailto:philippe.blouin@inLibro.com>
inLibro | pour esprit libre | www.inLibro.com <http://www.inLibro.com> On 2019-12-13 9:49 a.m., Jonathan Druart wrote:
How big is your tables? As you can see SQL query is really ugly, and can be good to split it anyway...
Le ven. 13 déc. 2019 à 15:26, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
Thanks,
Ok, I've never seen the "$field is not in group by" error. Interesting.
I've followed the bread crumbs: 21622 -> 20182 -> 21723 -> 22260... read it all. It's "damned if you do, damned if you don't".
ONLY_FULL_GROUP_BY mode is the SQL Standard, I don't think it would be wise to stray from that. (I can do it locally, but I don't think Koha should, philosophically)
But my client can't click on spent.pl. And no memory setting seems to fix it. So what's my solution? Could the query be reworked in two steps? This is not a screen where 0.2s will make a difference in the user experience.
Suggestions welcomed.
Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre |www.inLibro.com On 2019-12-13 4:33 a.m., Jonathan Druart wrote:
Hi Philippe,
See bug 21622. It is necessary to not explode with "$field is not in group by" (when sql_mode has ONLY_FULL_GROUP_BY) However we may need to discuss the need of this flag if it brings us performance issues.
Cheers, Jonathan
Le jeu. 12 déc. 2019 à 23:23, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
Good morning Koha,
Newbie question: why do we have 16 fields in GROUP BYs ? Are they really all necessary to eliminate duplicate lines in budgets? Genuine question.
When in acqui-home.pl, clicking certain entries linking to spent.pl just crashes with the error listed in the mail's object: Out of memory. Although I hate that solution, I went with it, increased sort_buffer_size 64x fold (I'm a generous guy). Still not enough.
Removing most lines in GROUP BY of spent.pl solves it all, of course.
-- Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre |www.inLibro.com _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website :http://www.koha-community.org/ git :http://git.koha-community.org/ bugs :http://bugs.koha-community.org/
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
Hi Philippe, I really do not understand the fix. If think you are going to get random data. My understanding of this change is that you are going to get only 1 row that will be populated with the MAX of each column. What am I missing? Le ven. 10 janv. 2020 à 22:30, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
A month later...
Maryse found a (recommanded) solution for the aqspent.pl crashes:
- aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber, - quantity-quantityreceived AS tleft, - budgetdate, entrydate, - aqbasket.booksellerid, + MAX(aqorders.biblionumber) AS biblionumber, MAX(aqorders.basketno) AS basketno, aqorders.ordernumber, + MAX(quantity)-MAX(quantityreceived) AS tleft, + MAX(budgetdate) AS budgetdate, MAX(entrydate) AS entrydate, + MAX(aqbasket.booksellerid) AS booksellerid, GROUP_CONCAT(DISTINCT itype SEPARATOR '|') as itypes, - title, - aqorders.invoiceid, - aqinvoices.invoicenumber, - quantityreceived, - unitprice_tax_included, - datereceived, - aqbooksellers.name as vendorname + MAX(title) AS title, + MAX(aqorders.invoiceid) AS invoiceid, + MAX(aqinvoices.invoicenumber) AS invoicenumber, + MAX(quantityreceived) AS quantityreceived, + MAX(unitprice_tax_included) AS unitprice_tax_included, + MAX(datereceived) AS datereceived, + MAX(aqbooksellers.name) as vendorname
We'll hack that in all our distributions. But I'd really like to have an official distributed solution to the problem. To me, it's not that much uglier than the fix adding all the column to avoid the problem with ONLY_FULL_GROUP_BY.
What's your take on it?
Regards,
Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com On 2019-12-13 11:25 a.m., Philippe Blouin wrote:
minuscule:
2000 biblio, 12000 items 8 aqbudgets, 200 invoices, 25 booksellers, 60 baskets, 240 orders.
That makes little sense...
Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com On 2019-12-13 9:49 a.m., Jonathan Druart wrote:
How big is your tables? As you can see SQL query is really ugly, and can be good to split it anyway...
Le ven. 13 déc. 2019 à 15:26, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
Thanks,
Ok, I've never seen the "$field is not in group by" error. Interesting.
I've followed the bread crumbs: 21622 -> 20182 -> 21723 -> 22260... read it all. It's "damned if you do, damned if you don't".
ONLY_FULL_GROUP_BY mode is the SQL Standard, I don't think it would be wise to stray from that. (I can do it locally, but I don't think Koha should, philosophically)
But my client can't click on spent.pl. And no memory setting seems to fix it. So what's my solution? Could the query be reworked in two steps? This is not a screen where 0.2s will make a difference in the user experience.
Suggestions welcomed.
Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com On 2019-12-13 4:33 a.m., Jonathan Druart wrote:
Hi Philippe,
See bug 21622. It is necessary to not explode with "$field is not in group by" (when sql_mode has ONLY_FULL_GROUP_BY) However we may need to discuss the need of this flag if it brings us performance issues.
Cheers, Jonathan
Le jeu. 12 déc. 2019 à 23:23, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
Good morning Koha,
Newbie question: why do we have 16 fields in GROUP BYs ? Are they really all necessary to eliminate duplicate lines in budgets? Genuine question.
When in acqui-home.pl, clicking certain entries linking to spent.pl just crashes with the error listed in the mail's object: Out of memory. Although I hate that solution, I went with it, increased sort_buffer_size 64x fold (I'm a generous guy). Still not enough.
Removing most lines in GROUP BY of spent.pl solves it all, of course.
-- Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
Hi Philippe, same here as Jonathan, it looks to me like you will get quite different data than the original query. Katrin On 13.01.20 12:26, Jonathan Druart wrote:
Hi Philippe, I really do not understand the fix. If think you are going to get random data. My understanding of this change is that you are going to get only 1 row that will be populated with the MAX of each column. What am I missing?
Le ven. 10 janv. 2020 à 22:30, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
A month later...
Maryse found a (recommanded) solution for the aqspent.pl crashes:
- aqorders.biblionumber, aqorders.basketno, aqorders.ordernumber, - quantity-quantityreceived AS tleft, - budgetdate, entrydate, - aqbasket.booksellerid, + MAX(aqorders.biblionumber) AS biblionumber, MAX(aqorders.basketno) AS basketno, aqorders.ordernumber, + MAX(quantity)-MAX(quantityreceived) AS tleft, + MAX(budgetdate) AS budgetdate, MAX(entrydate) AS entrydate, + MAX(aqbasket.booksellerid) AS booksellerid, GROUP_CONCAT(DISTINCT itype SEPARATOR '|') as itypes, - title, - aqorders.invoiceid, - aqinvoices.invoicenumber, - quantityreceived, - unitprice_tax_included, - datereceived, - aqbooksellers.name as vendorname + MAX(title) AS title, + MAX(aqorders.invoiceid) AS invoiceid, + MAX(aqinvoices.invoicenumber) AS invoicenumber, + MAX(quantityreceived) AS quantityreceived, + MAX(unitprice_tax_included) AS unitprice_tax_included, + MAX(datereceived) AS datereceived, + MAX(aqbooksellers.name) as vendorname
We'll hack that in all our distributions. But I'd really like to have an official distributed solution to the problem. To me, it's not that much uglier than the fix adding all the column to avoid the problem with ONLY_FULL_GROUP_BY.
What's your take on it?
Regards,
Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com On 2019-12-13 11:25 a.m., Philippe Blouin wrote:
minuscule:
2000 biblio, 12000 items 8 aqbudgets, 200 invoices, 25 booksellers, 60 baskets, 240 orders.
That makes little sense...
Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com On 2019-12-13 9:49 a.m., Jonathan Druart wrote:
How big is your tables? As you can see SQL query is really ugly, and can be good to split it anyway...
Le ven. 13 déc. 2019 à 15:26, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
Thanks,
Ok, I've never seen the "$field is not in group by" error. Interesting.
I've followed the bread crumbs: 21622 -> 20182 -> 21723 -> 22260... read it all. It's "damned if you do, damned if you don't".
ONLY_FULL_GROUP_BY mode is the SQL Standard, I don't think it would be wise to stray from that. (I can do it locally, but I don't think Koha should, philosophically)
But my client can't click on spent.pl. And no memory setting seems to fix it. So what's my solution? Could the query be reworked in two steps? This is not a screen where 0.2s will make a difference in the user experience.
Suggestions welcomed.
Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com On 2019-12-13 4:33 a.m., Jonathan Druart wrote:
Hi Philippe,
See bug 21622. It is necessary to not explode with "$field is not in group by" (when sql_mode has ONLY_FULL_GROUP_BY) However we may need to discuss the need of this flag if it brings us performance issues.
Cheers, Jonathan
Le jeu. 12 déc. 2019 à 23:23, Philippe Blouin <philippe.blouin@inlibro.com> a écrit :
Good morning Koha,
Newbie question: why do we have 16 fields in GROUP BYs ? Are they really all necessary to eliminate duplicate lines in budgets? Genuine question.
When in acqui-home.pl, clicking certain entries linking to spent.pl just crashes with the error listed in the mail's object: Out of memory. Although I hate that solution, I went with it, increased sort_buffer_size 64x fold (I'm a generous guy). Still not enough.
Removing most lines in GROUP BY of spent.pl solves it all, of course.
-- Philippe Blouin, Directeur de la technologie
Tél. : (833) 465-4276, poste 230 philippe.blouin@inLibro.com
inLibro | pour esprit libre | www.inLibro.com _______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
_______________________________________________ Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
Koha-devel mailing list Koha-devel@lists.koha-community.org https://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel website : http://www.koha-community.org/ git : http://git.koha-community.org/ bugs : http://bugs.koha-community.org/
participants (3)
-
Jonathan Druart -
Katrin Fischer -
Philippe Blouin