[Koha-devel] Out of sort memory, consider increasing server sort buffer size

[Philippe Blouin]

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 at inLibro.com <mailto:philippe.blouin at 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 at 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 at 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 at 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 at inLibro.com
>>
>> inLibro | pour esprit libre | www.inLibro.com
>> _______________________________________________
>> Koha-devel mailing list
>> Koha-devel at 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/
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.koha-community.org/pipermail/koha-devel/attachments/20191213/66f24a70/attachment.html>