Page MenuHomePhabricator

Fail to load more topics above current topic due to pseudo-column being queried
Closed, ResolvedPublic

Description

Steps:

  1. Go to a large board ( http://en.wikipedia.beta.wmflabs.org/wiki/Talk:Flow_QA )
  2. In the ToC, scroll for a while and click on a topic in order to navigate to a topic that is not yet loaded
  3. When the topic is loaded and scrolled to, scroll up just a little bit to trigger backfilling of topics above the one you clicked
  4. Database query error

This is the error when reproduced locally:

[d570267b] Exception Caught: A database error has occurred. Did you forget to run maintenance/update.php after upgrading? 
See: https://www.mediawiki.org/wiki/Manual:Upgrading#Run_the_update_script 
Query: SELECT * FROM `flow_topic_list` WHERE topic_list_id = '�=~��?@��' ORDER BY workflow_last_update_timestamp desc LIMIT 11 
Function: Flow\Data\Storage\BasicDbStorage::find (flow_topic_list) 
Error: 1054 Unknown column 'workflow_last_update_timestamp' in 'order clause' (127.0.0.1)

Event Timeline

SBisson created this task.Dec 16 2015, 3:47 PM
SBisson raised the priority of this task from to Needs Triage.
SBisson updated the task description. (Show Details)
Restricted Application added subscribers: StudiesWorld, Aklapper. · View Herald TranscriptDec 16 2015, 3:47 PM
Mattflaschen-WMF triaged this task as High priority.Dec 16 2015, 9:08 PM
jcrespo added a subscriber: jcrespo.

I can see this error on mediawikiwiki logs, generated by api calls (I arrived here independently):

{
  "_index": "logstash-2015.12.17",
  "_type": "mediawiki",
  "_id": "AVGvy9OKMRv_gmyxU30n",
  "_score": null,
  "_source": {
    "message": "Flow\\Data\\Storage\\BasicDbStorage::find (flow_topic_list)\t10.64.16.20\t1054\tUnknown column 'workflow_last_update_timestamp' in 'order clause' (10.64.16.20)\tSELECT  *  FROM `flow_topic_list`   WHERE topic_list_id = '\u00059ÕŸ*žŠ1ùå\u0005'  ORDER BY workflow_last_update_timestamp desc LIMIT 11  ",
    "@version": 1,
    "@timestamp": "2015-12-17T11:55:48.000Z",
    "type": "mediawiki",
    "host": "mw1201",
    "level": "ERROR",
    "tags": [
      "syslog",
      "es",
      "es",
      "normalized_message_trimmed"
    ],
    "channel": "wfLogDBError",
    "url": "/w/api.php?action=flow&format=json&page=Extension_talk%3ACollection&submodule=view-topiclist&vtloffset-dir=rev&vtllimit=10&vtloffset=20150504134619&vtlsortby=updated",
    "ip": "10.64.32.106",
    "http_method": "GET",
    "server": "www.mediawiki.org",
    "referrer": "https://www.mediawiki.org/wiki/Extension_talk:Collection",
    "uid": "fee722d",
    "process_id": 31016,
    "wiki": "mediawikiwiki",
    "db_server": "10.64.16.20",
    "db_name": "flowdb",
    "db_user": "wikiuser",
    "method": "DatabaseBase::reportQueryError",
    "errno": 1054,
    "error": "Unknown column 'workflow_last_update_timestamp' in 'order clause' (10.64.16.20)",
    "sql1line": "SELECT  *  FROM `flow_topic_list`   WHERE topic_list_id = '\u00059ÕŸ*žŠ1ùå\u0005'  ORDER BY workflow_last_update_timestamp desc LIMIT 11  ",
    "fname": "Flow\\Data\\Storage\\BasicDbStorage::find (flow_topic_list)",
    "normalized_message": "Flow\\Data\\Storage\\BasicDbStorage::find (flow_topic_list)\t10.64.16.20\t1054\tUnknown column 'workflow_last_update_timestamp' in 'order clause' (10.64.16.20)\tSELECT  *  FROM `flow_topic_list`   WHERE topic_list_id = '\u00059ÕŸ*žŠ1ùå\u0005'  ORDER BY workflow_last_updat"
  },
  "sort": [
    1450353348000
  ]
}

Only 7 failures in the last day, though.

Been reported yesterday by @Elitre while browsing the office wiki:

[8f733e14] Database query error while scrolling https://office.wikimedia.org/wiki/Village_pump . I should join Collaboration's #, I guess they have one.)

Still an order by close referencing the non existent workflow_last_update_timestamp field

Function: Flow\Data\Storage\BasicDbStorage::find (flow_topic_list)
Error: 1054 Unknown column 'workflow_last_update_timestamp' in 'order clause' (10.64.16.24)
Mattflaschen-WMF renamed this task from Fail to load more topics above current topic to Fail to load more topics above current topic due to psuedo-column being queried.Feb 4 2016, 9:00 PM
Mattflaschen-WMF set Security to None.
{
  "_index": "logstash-2016.05.19",
  "_type": "mediawiki",
  "_id": "AVTIL9cMDxp7yus2XfK0",
  "_score": null,
  "_source": {
    "message": "Flow\\Data\\Storage\\BasicDbStorage::find (flow_topic_list)\t10.64.16.18\t1054\tUnknown column 'workflow_last_update_timestamp' in 'order clause' (10.64.16.18)\tSELECT  *  FROM `flow_topic_list`   WHERE topic_list_id = '\u0005Jšä颪\u0018Ӓ†'  ORDER BY workflow_last_update_timestamp desc LIMIT 11  ",
    "@version": 1,
    "@timestamp": "2016-05-19T08:44:18.000Z",
    "type": "mediawiki",
    "host": "mw1139",
    "level": "ERROR",
    "tags": [
      "syslog",
      "es",
      "es"
    ],
    "channel": "wfLogDBError",
    "normalized_message": "{fname}\t{db_server}\t{errno}\t{error}\t{sql1line}",
    "url": "/w/api.php?action=flow&format=json&page=Discussion_utilisateur%3AH4stings&submodule=view-topiclist&vtloffset-dir=rev&vtllimit=10&vtloffset=20160309223857&vtlsortby=updated",
    "ip": "10.64.0.103",
    "http_method": "GET",
    "server": "fr.wikipedia.org",
    "referrer": "https://fr.wikipedia.org/wiki/Discussion_utilisateur:H4stings",
    "unique_id": "Vz183wpAEHcAAEsqjVcAAAAX",
    "wiki": "frwiki",
    "mwversion": "1.28.0-wmf.1",
    "reqId": "Vz183wpAEHcAAEsqjVcAAAAX",
    "db_server": "10.64.16.18",
    "db_name": "flowdb",
    "db_user": "wikiuser",
    "method": "DatabaseBase::reportQueryError",
    "errno": 1054,
    "error": "Unknown column 'workflow_last_update_timestamp' in 'order clause' (10.64.16.18)",
    "sql1line": "SELECT  *  FROM `flow_topic_list`   WHERE topic_list_id = '\u0005Jšä颪\u0018Ӓ†'  ORDER BY workflow_last_update_timestamp desc LIMIT 11  ",
    "fname": "Flow\\Data\\Storage\\BasicDbStorage::find (flow_topic_list)"
  },
  "sort": [
    1463647458000
  ]
}
Elitre renamed this task from Fail to load more topics above current topic due to psuedo-column being queried to Fail to load more topics above current topic due to pseudo-column being queried.May 19 2016, 11:11 AM
jcrespo added a comment.EditedMay 19 2016, 11:36 AM

Sorry, I think my comment got lost, and I only posted the error. I meant that I saw a small, but noticeable spike of this errors on frwiki, and I showed an example including a timestamp if you want to look at the rest.

hashar removed a subscriber: hashar.May 19 2016, 1:17 PM

Checked in 1.28.0-alpha (b5e308c). The page http://en.wikipedia.beta.wmflabs.org/wiki/Talk:Flow_QA consistently shows 'Database query error' , for exmple:

{servedby: "deployment-mediawiki02",…}
error
:
{code: "internal_api_error_DBQueryError", info: "[V7N@SwpEEH8AAE1DMKMAAAAG] Database query error",…}
*
:
"#0 /srv/mediawiki/php-master/includes/db/Database.php(911): 
DatabaseBase->reportQueryError(string, integer, string, string, 
boolean)
#1 /srv/mediawiki/php-
master/includes/db/Database.php(1278): DatabaseBase->query(string, string)
#2 /srv/mediawiki/php-master/extensions/Flow/includes/Data/Storage/BasicDbStorage.php(148): DatabaseBase->select(string, string, array, string, array)
#3 /srv/mediawiki/php-master/extensions/Flow/includes/Data/Storage/BasicDbStorage.php(163): Flow\Data\Storage\BasicDbStorage->find(array, array)
#4 /srv/mediawiki/php-master/extensions/Flow/includes/Data/Storage/BasicDbStorage.php(180): Flow\Data\Storage\BasicDbStorage->fallbackFindMulti(array, array)
#5 /srv/mediawiki/php-master/extensions/Flow/includes/Data/ObjectLocator.php(103): Flow\Data\Storage\BasicDbStorage->findMulti(array, array)
#6 /srv/mediawiki/php-master/extensions/Flow/includes/Data/ObjectLocator.php(64): Flow\Data\ObjectLocator->findMulti(array, array)
#7 /srv/mediawiki/php-master/extensions/Flow/includes/Data/Pager/Pager.php(130): Flow\Data\ObjectLocator->find(array, array)
#8 /srv/mediawiki/php-master/extensions/Flow/includes/Block/TopicList.php(479): Flow\Data\Pager\Pager->getPage(Closure$Flow\Block\TopicListBlock::getPage;30357559)
#9 /srv/mediawiki/php-master/extensions/Flow/includes/Block/TopicList.php(266): Flow\Block\TopicListBlock->getPage(array)
#10 /srv/mediawiki/php-master/extensions/Flow/includes/Api/ApiFlowBaseGet.php(33): Flow\Block\TopicListBlock->renderApi(array)
#11 /srv/mediawiki/php-master/extensions/Flow/includes/Api/ApiFlow.php(106): Flow\Api\ApiFlowBaseGet->execute()
#12 /srv/mediawiki/php-master/includes/api/ApiMain.php(1435): Flow\Api\ApiFlow->execute()
#13 /srv/mediawiki/php-master/includes/api/ApiMain.php(508): ApiMain->executeAction()
#14 /srv/mediawiki/php-master/includes/api/ApiMain.php(479): ApiMain->executeActionWithErrorHandling()
#15 /srv/mediawiki/php-master/api.php(83): ApiMain->execute()
#16 /srv/mediawiki/w/api.php(3): include(string)
#17 {main}"
code
:
"internal_api_error_DBQueryError"
info
:
"[V7N@SwpEEH8AAE1DMKMAAAAG] Database query error"
servedby
:
"deployment-mediawiki02"
Krinkle removed a subscriber: Krinkle.Sep 29 2016, 7:37 PM

I can still see this error on mediawiki.org, for example with url /w/api.php?action=flow&format=json&page=Extension_talk%3ALDAP_Authentication&submodule=view-topiclist&vtloffset-dir=rev&vtllimit=10&vtloffset=20161122183706&vtlsortby=updated

Unknown column 'workflow_last_update_timestamp' in 'order clause'

Reedy added a comment.Mar 21 2017, 9:56 PM

Full stracktrace:

#0 /srv/mediawiki/php-1.29.0-wmf.17/includes/libs/rdbms/database/Database.php(919): Database->reportQueryError(string, integer, string, string, boolean)
#1 /srv/mediawiki/php-1.29.0-wmf.17/includes/libs/rdbms/database/Database.php(1255): Database->query(string, string)
#2 /srv/mediawiki/php-1.29.0-wmf.17/extensions/Flow/includes/Data/Storage/BasicDbStorage.php(148): Database->select(string, string, array, string, array)
#3 /srv/mediawiki/php-1.29.0-wmf.17/extensions/Flow/includes/Data/Storage/BasicDbStorage.php(163): Flow\Data\Storage\BasicDbStorage->find(array, array)
#4 /srv/mediawiki/php-1.29.0-wmf.17/extensions/Flow/includes/Data/Storage/BasicDbStorage.php(180): Flow\Data\Storage\BasicDbStorage->fallbackFindMulti(array, array)
#5 /srv/mediawiki/php-1.29.0-wmf.17/extensions/Flow/includes/Data/ObjectLocator.php(103): Flow\Data\Storage\BasicDbStorage->findMulti(array, array)
#6 /srv/mediawiki/php-1.29.0-wmf.17/extensions/Flow/includes/Data/ObjectLocator.php(64): Flow\Data\ObjectLocator->findMulti(array, array)
#7 /srv/mediawiki/php-1.29.0-wmf.17/extensions/Flow/includes/Data/Pager/Pager.php(130): Flow\Data\ObjectLocator->find(array, array)
#8 /srv/mediawiki/php-1.29.0-wmf.17/extensions/Flow/includes/Block/TopicList.php(479): Flow\Data\Pager\Pager->getPage(Closure$Flow\Block\TopicListBlock::getPage;495966428)
#9 /srv/mediawiki/php-1.29.0-wmf.17/extensions/Flow/includes/Block/TopicList.php(266): Flow\Block\TopicListBlock->getPage(array)
#10 /srv/mediawiki/php-1.29.0-wmf.17/extensions/Flow/includes/Api/ApiFlowBaseGet.php(33): Flow\Block\TopicListBlock->renderApi(array)
#11 /srv/mediawiki/php-1.29.0-wmf.17/extensions/Flow/includes/Api/ApiFlow.php(98): Flow\Api\ApiFlowBaseGet->execute()
#12 /srv/mediawiki/php-1.29.0-wmf.17/includes/api/ApiMain.php(1544): Flow\Api\ApiFlow->execute()
#13 /srv/mediawiki/php-1.29.0-wmf.17/includes/api/ApiMain.php(545): ApiMain->executeAction()
#14 /srv/mediawiki/php-1.29.0-wmf.17/includes/api/ApiMain.php(516): ApiMain->executeActionWithErrorHandling()
#15 /srv/mediawiki/php-1.29.0-wmf.17/api.php(83): ApiMain->execute()
#16 /srv/mediawiki/w/api.php(3): include(string)
#17 {main}

I can reproduce this locally, it looks like it happens whenever api.php?action=flow&submodule=view-topic is invoked with vtloffset-dir=rev.

This appears to have been caused by rEFLW699c5d86fcea: Fix history pagination and give user the number of entries they requested which made TopKIndex explicitly not match any reverse queries. This breaks a hack whereby order-by-timestamp queries are directed to a special instance of TopKIndex that uses a special storage backend (TopicListLastUpdatedStorage instead of TopicListStorage) which knows to do the join against the workflow table. This worked in forward mode, rEFLW699c5d86fcea: Fix history pagination and give user the number of entries they requested made it so that TopKIndex never matches reverse-ordered queries, so in reverse mode the query wouldn't match any of the indexes, which would throw a NoIndexException, which then gets caught and the query falls back to using the backend storage class directly, but because that is TopicListStorage rather than TopicListLastUpdatedStorage here, it doesn't know to do the join and explodes.

In other words, the code was trying to be too clever and only do the join in exactly the right circumstances, and that came crumbling down when Matt made a slight (and on its face sensible) change to how index matching works.

Change 344073 had a related patch set uploaded (by Catrope):
[mediawiki/extensions/Flow] Make sure topiclist queries always join against workflow table

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

Change 344073 merged by jenkins-bot:
[mediawiki/extensions/Flow@master] Make sure topiclist queries always join against workflow table

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

Change 344188 had a related patch set uploaded (by Catrope):
[mediawiki/extensions/Flow@wmf/1.29.0-wmf.17] Make sure topiclist queries always join against workflow table

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

Change 344191 had a related patch set uploaded (by Catrope):
[mediawiki/extensions/Flow@master] Don't attempt to do reverse pagination in flow-loadmore

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

Change 344188 merged by jenkins-bot:
[mediawiki/extensions/Flow@wmf/1.29.0-wmf.17] Make sure topiclist queries always join against workflow table

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

Mentioned in SAL (#wikimedia-operations) [2017-03-22T19:55:44Z] <thcipriani@tin> Synchronized php-1.29.0-wmf.17/extensions/Flow: [[gerrit:344188|Make sure topiclist queries always join against workflow table]] T121644 (duration: 00m 59s)

Change 344191 merged by jenkins-bot:
[mediawiki/extensions/Flow@master] Don't attempt to do reverse pagination in flow-loadmore

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

Checked in betalabs following the description of steps for http://en.wikipedia.beta.wmflabs.org/wiki/Talk:Flow_QA . The issue seems to be fixed.

QA Recommendation: Resolve

jmatazzoni closed this task as Resolved.Mar 23 2017, 8:28 PM
jmatazzoni claimed this task.
mmodell changed the subtype of this task from "Task" to "Production Error".Aug 28 2019, 11:11 PM