Page MenuHomePhabricator
Create Task
Maniphest T78276

allpages filterlanglinks DBQueryError
Closed, ResolvedPublic
Actions

Description

The following all return DBQueryError

https://en.wikipedia.org/w/api.php?action=query&generator=allpages&gapfilterlanglinks=withlanglinks&gaplimit=5&format=json&continue=
https://ar.wikipedia.org/w/api.php?action=query&generator=allpages&gapfilterlanglinks=withlanglinks&gaplimit=5&format=json&continue=
https://fr.wiktionary.org/w/api.php?action=query&generator=allpages&gapfilterlanglinks=withlanglinks&gaplimit=5&format=json&continue=

www.wikidata takes a long time, but does return a result

https://www.wikidata.org/w/api.php?action=query&generator=allpages&gapfilterlanglinks=withlanglinks&gaplimit=5&format=json&continue=

test.wikidata and test.wikipedia do not

https://test.wikipedia.org/w/api.php?action=query&generator=allpages&gapfilterlanglinks=withlanglinks&gaplimit=5&format=json&continue=
https://test.wikidata.org/w/api.php?action=query&generator=allpages&gapfilterlanglinks=withlanglinks&gaplimit=5&format=json&continue=

Note: this is a continuation of T18549, which was opened because pywikibot tests needed to be disabled, and I have tried to re-enable them now only to see the problem has not been fixed, or has since regressed. The tests are http://git.wikimedia.org/blob/pywikibot%2Fcore.git/c731cdb7b58adaf0f42c86bb838257af36e6e2f6/tests%2Fsite_tests.py#L410 , and they were commented out in this commit: http://git.wikimedia.org/commit/pywikibot%2Fcore.git/0da05211576892e91ee0e5e24ff3bb87828e2aaa

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
API: More avoiding MySQL filesorts in list=allpagesmediawiki/coremaster+8 -4
API: Avoid MySQL filesort with list=allpages&apfilterlanglinks=withlanglinksmediawiki/coremaster+17 -3
Customize query in gerrit

Related Objects
Search...

Event Timeline

jayvdb created this task.Dec 11 2014, 2:06 PM
jayvdb raised the priority of this task from to Needs Triage.
jayvdb updated the task description. (Show Details)
jayvdb added a project: MediaWiki-Action-API.
jayvdb changed Security from none to None.
jayvdb subscribed.
jayvdb added a project: Pywikibot-tests.Dec 11 2014, 2:34 PM
jayvdb moved this task from Backlog to Test failures on the Pywikibot-tests board.
gerritbot added a project: Patch-For-Review.Dec 11 2014, 4:14 PM

Change 179146 had a related patch set uploaded (by Anomie):
API: Avoid MySQL filesort with list=allpages&apfilterlanglinks=withlanglinks

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

Patch-For-Review

Anomie claimed this task.Dec 11 2014, 4:14 PM
Anomie added a project: MediaWiki-Core-Team.
Anomie moved this task from Backlog to Needs Review/Feedback on the MediaWiki-Core-Team board.Dec 11 2014, 4:55 PM
jayvdb added a comment.Dec 12 2014, 4:13 AM

There is a very similar, but less frequently occurring case in T73971 : generator imageusage iufilterredir=redirects

Anomie added a comment.Dec 12 2014, 3:51 PM
In T78276#843514, @jayvdb wrote:

There is a very similar, but less frequently occurring case in T73971 : generator imageusage iufilterredir=redirects

Only similar in that it's another query that's slow. The issue there seems to be T78403.

gerritbot added a comment.Dec 19 2014, 12:17 AM

Change 179146 merged by jenkins-bot:
API: Avoid MySQL filesort with list=allpages&apfilterlanglinks=withlanglinks

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

tstarling mentioned this in rMWc394cdc04dd1: API: Avoid MySQL filesort with list=allpages&apfilterlanglinks=withlanglinks.Dec 19 2014, 12:17 AM
aaron moved this task from Needs Review/Feedback to Done on the MediaWiki-Core-Team board.Dec 19 2014, 12:35 AM
Anomie closed this task as Resolved.Dec 19 2014, 3:17 PM

The fix should be deployed to WMF wikis with 1.25wmf14, see https://www.mediawiki.org/wiki/MediaWiki_1.25/Roadmap for the schedule.

bd808 moved this task from Done to Archive on the MediaWiki-Core-Team board.Dec 22 2014, 10:45 PM
jayvdb mentioned this in rPWBC33de47f4f43e: Re-enable allpages langlinks tests.Mar 4 2015, 8:46 AM
jayvdb reopened this task as Open.Aug 24 2015, 10:06 AM

The following still fail

https://ar.wikipedia.org/w/api.php?action=query&generator=allpages&gapfilterlanglinks=withlanglinks&gaplimit=5&format=json&continue=
https://fr.wiktionary.org/w/api.php?action=query&generator=allpages&gapfilterlanglinks=withlanglinks&gaplimit=5&format=json&continue=
https://www.wikidata.org/w/api.php?action=query&generator=allpages&gapfilterlanglinks=withlanglinks&gaplimit=5&format=json&continue=

jayvdb added a comment.Aug 24 2015, 10:18 AM

"jayvdb mentioned this in rPWBC33de47f4f43e: Re-enable allpages langlinks tests.Via Daemons · Mar 4 2015, 8:46 AM"

That is wrong; rPWBC33de47f4f43e shows "jayvdb committed rPWBC33de47f4f43e: Re-enable allpages langlinks tests (authored by jayvdb).Via Daemons · Dec 11 2014, 3:08 PM"

Anomie added a project: DBA.Aug 24 2015, 4:14 PM

Wikidata is T97797.

As for the rest, I'm seeing some weirdness in the database.

The query here is

SELECT  /*! STRAIGHT_JOIN */ page_namespace,page_title,page_id,page_content_model  FROM `page`,`langlinks`   WHERE page_namespace = '0' AND (page_id=ll_from)  GROUP BY page_title, page_id ORDER BY page_title LIMIT 6

For both arwiki and frwiktionary, it seems to work fine on db1033:

+------+-------------+-----------+------+--------------------+------------+---------+---------------------+---------+-------------+
| id   | select_type | table     | type | possible_keys      | key        | key_len | ref                 | rows    | Extra       |
+------+-------------+-----------+------+--------------------+------------+---------+---------------------+---------+-------------+
|    1 | SIMPLE      | page      | ref  | PRIMARY,name_title | name_title | 4       | const               | 1245075 | Using where |
|    1 | SIMPLE      | langlinks | ref  | ll_from            | ll_from    | 4       | arwiki.page.page_id |      12 | Using index |
+------+-------------+-----------+------+--------------------+------------+---------+---------------------+---------+-------------+

On the rest of the s7 DBs (db1028, db1034, db1041, db1039, db1062), the same query has a different plan:

+------+-------------+-----------+------+--------------------+------------+---------+---------------------+---------+----------------------------------------------+
| id   | select_type | table     | type | possible_keys      | key        | key_len | ref                 | rows    | Extra                                        |
+------+-------------+-----------+------+--------------------+------------+---------+---------------------+---------+----------------------------------------------+
|    1 | SIMPLE      | page      | ref  | PRIMARY,name_title | name_title | 4       | const               | 1161329 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | langlinks | ref  | ll_from            | ll_from    | 4       | arwiki.page.page_id |       8 | Using index                                  |
+------+-------------+-----------+------+--------------------+------------+---------+---------------------+---------+----------------------------------------------+

Any ideas, database gurus?

I also note that changing the query to just GROUP BY page_title makes all the DBs here happy, so that's an option to fix it.

gerritbot subscribed.Aug 24 2015, 4:14 PM

Change 233427 had a related patch set uploaded (by Anomie):
API: More avoiding MySQL filesorts in list=allpages

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

Anomie moved this task from Unsorted to Needs Review on the MediaWiki-Action-API board.Aug 24 2015, 4:15 PM
jayvdb moved this task from Test failures to Upstream issues on the Pywikibot-tests board.Aug 24 2015, 10:55 PM
gerritbot added a comment.Aug 25 2015, 10:23 PM

Change 233427 merged by jenkins-bot:
API: More avoiding MySQL filesorts in list=allpages

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

Anomie mentioned this in rMWcdaa984197be: API: More avoiding MySQL filesorts in list=allpages.Aug 25 2015, 10:24 PM
• Forrestbot added projects: MW-1.26-release, WMF-deploy-2015-09-01_(1.26wmf21).Aug 25 2015, 11:00 PM
Anomie closed this task as Resolved.Aug 26 2015, 4:28 PM

The part of this bug that isn't T97797 should be fixed now, although if the DB people have anything to add it would still be welcome.

Xqt mentioned this in T145319: Re-enable test_allpages_langlinks_enabled without allowed_failure decorator.Sep 11 2016, 7:02 AM
Xqt added a subtask: T145319: Re-enable test_allpages_langlinks_enabled without allowed_failure decorator.Sep 11 2016, 7:04 AM
Xqt closed subtask T145319: Re-enable test_allpages_langlinks_enabled without allowed_failure decorator as Resolved.Sep 11 2016, 4:40 PM
Anomie added a project: mariadb-optimizer-bug.Oct 4 2019, 1:42 PM
Restricted Application added a project: Pywikibot. · View Herald TranscriptOct 4 2019, 1:42 PM
Anomie moved this task from Currently not fixed to Worked around in code on the mariadb-optimizer-bug board.Oct 4 2019, 1:42 PM
Aklapper removed a subscriber: Anomie.Oct 16 2020, 5:39 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits