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

[Jonathan Druart]

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/