I suspect you want to disable that mysql mode

You mean sql_mode=only_full_group_by? I must however note that this server is using the MySQL 5.7+ default settings.

And it's seemingly a breaking change made in a point release, which is weird. Quick glance suggests it's the same in mariadb et al in new enough versions

Is this a new install? Or a recent upgrade of mysql?

I'm imagining there might be a lot more broken queries, and needs more work than just fixing this special page...

As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by

Even if this is a compatibility issue that can be "fixed" by changing SQL mode, it's actually non-standard, and conceptually erroneous, to not specify all non-aggregate fields in the GROUP BY clause. Consider it a flaw in mysql to not check against this. It would error out on mssql server as well, for example. This is an easy fix as well.

The wiki as such is about 9 years old. The upgrade history is as follows (minor version updates excluded)

Indeed the upgrade to MySQL caused several issues with extensions. Hopefully the situation is not too bad.

Even if this is a compatibility issue that can be "fixed" by changing SQL mode

@Ciencia_Al_Poder For me this is not a problem since I can just disable it but think of all the shared hosting users which will inevitably profit from this "cool" mode.

Do they disable changing of SQL Mode? I thought it was fairly well accepted that it can be set/changed at connection time by most apps as appropriate

I have never seen a shared hosting provider allowing to change SQL settings. Admittedly I never looked for that. Usually I am happy if I can change a couple of PHP settings myself.

In T160298#3094396, @Kghbln wrote:

I have never seen a shared hosting provider allowing to change SQL settings. Admittedly I never looked for that. Usually I am happy if I can change a couple of PHP settings myself.

Usually it's changed at the application level, with the SQL command it sends the server upon connection, rather than changing the actual sql server settings

It was "incorrectly" fixed for PostgreSQL for T70087, by adding a condition to "only" produce valid SQL statements if the RDBMS supports implicitGroupby(), as a petition of @aaron in gerrit 147647. Quote from @aaron:

Can you only do this if implicitGroupby() is false? that way it want hurt the mysql query plan,

I'm trying to understand how adding those fields in the group by (which should be there, because that's what the results of the query expect, and because it's required for all serious RDBMS) can negatively impact in the query plan. This can probably be tagged as Technical-Debt as well...

Anybody up for at least triaging this database query issue?

Still happening with:

• MediaWiki 1.27.3 (2fd9800) 06:05, 7. Jul. 2017
• PHP 7.0.22-0ubuntu0.16.04.1 (apache2handler)
• MySQL 5.7.19-0ubuntu0.16.04.1

@aaron should probably reply T160298#3094400, or somebody just write a patch to remove the $dbr->implicitGroupby() logic, leaving all 3 columns in the group by inconditionally.

Change 377015 had a related patch set uploaded (by Aaron Schulz; owner: Aaron Schulz):
[mediawiki/core@master] Remove use of implicitGroupBy() in ActiveUsersPager

https://gerrit.wikimedia.org/r/377015

Note: The same error can be reproduced on mysql/mariadb if someone sets sql_mode to ONLY_FULL_GROUP_BY

In T160298#3597707, @Ciencia_Al_Poder wrote:

Note: The same error can be reproduced on mysql/mariadb if someone sets sql_mode to ONLY_FULL_GROUP_BY

Another note: I am using the MySQL defaults at least for this mode so everybody doing so too will get the error.

Change 377015 merged by jenkins-bot:
[mediawiki/core@master] Remove use of implicitGroupBy() in ActiveUsersPager

https://gerrit.wikimedia.org/r/377015

Change 384549 had a related patch set uploaded (by Krinkle; owner: Aaron Schulz):
[mediawiki/core@REL1_29] Remove use of implicitGroupBy() in ActiveUsersPager

https://gerrit.wikimedia.org/r/384549

Change 384569 had a related patch set uploaded (by Reedy; owner: Aaron Schulz):
[mediawiki/core@REL1_28] Remove use of implicitGroupBy() in ActiveUsersPager

https://gerrit.wikimedia.org/r/384569

Change 384571 had a related patch set uploaded (by Reedy; owner: Aaron Schulz):
[mediawiki/core@REL1_27] Remove use of implicitGroupBy() in ActiveUsersPager

https://gerrit.wikimedia.org/r/384571

^ And there's two more cherry picked

Appropriate RELEASE-NOTES should be added and then the patches merged

Change 384569 merged by jenkins-bot:
[mediawiki/core@REL1_28] Remove use of implicitGroupBy() in ActiveUsersPager

https://gerrit.wikimedia.org/r/384569

Change 384549 merged by jenkins-bot:
[mediawiki/core@REL1_29] Remove use of implicitGroupBy() in ActiveUsersPager

https://gerrit.wikimedia.org/r/384549

Change 384571 merged by jenkins-bot:
[mediawiki/core@REL1_27] Remove use of implicitGroupBy() in ActiveUsersPager

https://gerrit.wikimedia.org/r/384571

Awesome to get the backports! Thanks a lot! Please do not be angry with me but I just updated the wiki to use:

• MediaWiki 1.27.3 (5b97740) 20:03, 16 October 2017
• PHP 7.0.22-0ubuntu0.16.04.1 (apache2handler)
• MySQL 5.7.19-0ubuntu0.16.04.1

... and I am still getting the very same error.

It will really be nice to get a fix for this into 1.31 LTS. I know that this is apparently not a front-burner thing but an LTS is always a good opportunity. :)

@CCicalese_WMF FYI

Current broken setup:

• MediaWiki 1.27.4 (3aae510) 22:32, 29 November 2017
• PHP 7.0.22-0ubuntu0.16.04.1 (apache2handler)
• MySQL 5.7.20-0ubuntu0.16.04.1

Can you post the sql it's failing on now? As with that patch... there should've been some difference (and aliasing user_name as qcc_title)

Or not, in 1.27/1.28... Why did the field alias disappear? (does it mean this is actually fixed in REL1_29/REL1_30?)

Change 396551 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/core@REL1_27] Copy in Field changes from Id8963924f036e9ed3fcdde3a8e54b7126b72356e

https://gerrit.wikimedia.org/r/396551

That's the current situation. Have not checked whether this is different now:

A database query error has occurred. This may indicate a bug in the software.

Query:

SELECT  user_name,user_id,COUNT(*) AS `recentedits`,qcc_title  FROM `querycachetwo`,`user`,`recentchanges`   WHERE qcc_type = 'activeusers' AND qcc_namespace = '2' AND (user_name = qcc_title) AND (rc_user_text = qcc_title) AND (rc_type != '5') AND (rc_type != '6') AND (rc_log_type IS NULL OR rc_log_type != 'newusers') AND (rc_timestamp >= '20171108235347') AND (NOT EXISTS (SELECT  1  FROM `ipblocks`   WHERE (ipb_user=user_id) AND ipb_deleted = '1'  ))  GROUP BY qcc_title ORDER BY qcc_title LIMIT 301

Function:

IndexPager::buildQueryInfo (ActiveUsersPager)

Error:

1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'DB0231020151216.user.user_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (localhost)

Backtrace:

#0 /../w/includes/db/Database.php(901): DatabaseBase->reportQueryError('Expression #1 o...', 1055, 'SELECT  user_na...', 'IndexPager::bui...', false)
#1 /../w/includes/db/Database.php(1234): DatabaseBase->query('SELECT  user_na...', 'IndexPager::bui...')
#2 /../w/includes/pager/IndexPager.php(365): DatabaseBase->select(Array, Array, Array, 'IndexPager::bui...', Array, Array)
#3 /../w/includes/pager/IndexPager.php(221): IndexPager->reallyDoQuery('', 301, true)
#4 /../w/includes/pager/IndexPager.php(419): IndexPager->doQuery()
#5 /../w/includes/specials/SpecialActiveusers.php(77): IndexPager->getBody()
#6 /../w/includes/specialpage/SpecialPage.php(479): SpecialActiveUsers->execute(NULL)
#7 /../w/includes/specialpage/SpecialPageFactory.php(577): SpecialPage->run(NULL)
#8 /../w/includes/MediaWiki.php(282): SpecialPageFactory::executePath(Object(Title), Object(RequestContext))
#9 /../w/includes/MediaWiki.php(735): MediaWiki->performRequest()
#10 /../w/includes/MediaWiki.php(509): MediaWiki->main()
#11 /../w/index.php(43): MediaWiki->run()
#12 {main}

Fancy testing that patch?

Fancy testing that patch?

@Reedy Works for me! Thank you!

Change 396551 merged by jenkins-bot:
[mediawiki/core@REL1_27] Copy in Field changes from Id8963924f036e9ed3fcdde3a8e54b7126b72356e

https://gerrit.wikimedia.org/r/396551

Change 407695 had a related patch set uploaded (by Ejegg; owner: Reedy):
[mediawiki/core@fundraising/REL1_27] Copy in Field changes from Id8963924f036e9ed3fcdde3a8e54b7126b72356e

https://gerrit.wikimedia.org/r/407695

Change 407695 merged by jenkins-bot:
[mediawiki/core@fundraising/REL1_27] Copy in Field changes from Id8963924f036e9ed3fcdde3a8e54b7126b72356e

https://gerrit.wikimedia.org/r/407695