After modifying the query to match my database prefix I could not replicate this

Mysql settings are

sort_buffer_size = 512K

Setting to 32k does give the error you describe (139k worked for me) but changing it to 512k resolves it
Did you remember to restart mysql AFTER you made the change?

Not sure where you got 139k as the default value from? Its not the mysql own default https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_sort_buffer_size

You stated that your version of mysql is 5.0.10
If that is correct then it is below the minimum requirement (see https://downloads.joomla.org/technical-requirements)
5.0.10 was released July 2005

I stand to be corrected - the mysql version is actually 5.5.33.
Subsequently, I have also installed the Joomla 3.6.2 under latest issue of Uniform server V 13.2.1 (Zero13). But I get the same problem.

Regarding the fresh setting of the sort value, yes - I did stop and restart the both servers (Apache and Mysql).

Not really sure what to say. With sensible mysql default values everything
seems ok to me

One further comment, if the reduce the extent of the group expression and leave off the last three fields i.e.
change from

$query->group('a.id, a.menutype, a.description, a.title, b.menutype,b.language,l.image,l.sef,l.title_native');
to
$query->group('a.id, a.menutype, a.description, a.title, b.menutype,b.language');

then the error condition goes away.

So - a question, is it really necessary in the sql design to include all those fields in the 'group' statement?
What will 'break' (elsewhere) if the last three fields are omitted?

For Joomla's claimed support of other databases, the extra fields have to be in the group clause. IIRC it was SQL Server that was more strict about all fields that are SELECTed needing to be in the GROUP clause if used.

SELECT has the aggregate function

So GROUP-BY is required to be there,

now what happens if you leave it out ?

If you leave out the GROUP-BY,
and you do have an AGGREGATE (Group) function in the SELECT

• then SQL Server (and PostgreSQL ??) will complain
• in MySQL , (please correct me here) there is a setting "ONLY_FULL_GROUP_BY" which by default it is OFF, which means it is not required,then it is equivalent to grouping on all columns of the SELECT

So in any case for ALL DBs the Group-by will happen ...,
just in MySQL it could have been skipped and thus be "silently" implied

[EDIT]
and if you change the group-by to make it shorter then it is a different query, than it would have been if you had not written it at all

To be honest this looks like another example where uniformserver just isnt
very good

On 23 November 2016 at 16:31, Georgios Papadakis notifications@github.com
wrote:

SELECT has the aggregate function

So GROUP-BY is required to be there,

now what happens if you leave it out ?

If you leave out the GROUP-BY,
and you do have an AGGREGATE (Group) function in the SELECT

• then SQL Server (and PostgreSQL ??) will complain
• in MySQL , (please correct me here) there is a setting "ONLY_FULL_GROUP_BY" which by default it is OFF, which means it is not required,then it is equivalent to grouping on all columns of the SELECT

So in any case for ALL DBs the Group-by will happen ...,
just in MySQL it could have been "silently" skipped

—
You are receiving this because you commented.
Reply to this email directly, view it on GitHub
#12983 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/ABPH8SXiXw6I55VDzqTiR15obqO_sULPks5rBGpmgaJpZM4K6R1h
.

Brian Teeman
Co-founder Joomla! and OpenSourceMatters Inc.
https://brian.teeman.net/ http://brian.teeman.net/

One further comment, if the reduce the extent of the group expression and leave off the last three fields i.e. change from

->group('a.id,a.menutype,a.description,a.title,b.menutype,b.language,l.image,l.sef,l.title_native');
to
->group('a.id,a.menutype,a.description,a.title,b.menutype,b.language');

It can be done

• but the LEFT Join with the languages table will need to be removed, (maybe be done and it can be done keep the LEFT Join, i am not sure without further investigation, but removing the LEFT join on language will give even more benefits, see below)

and then a 2nd separate query will be executed, to get the language data
and then add them to the menus array, via a PHP loop

this will not only reduce SQL memory usage,
but it should also give a measurable performance difference to the overall execution of the page

a lot of duplicated data (the language) are returned by the SELECT statement without real need,
but most important is not their effect on the SELECT but their participation in the GROUP-BY

I have made a PR here: #12991

A new PR at #14011

Can this be closed as we have a pr @zero-24