Page MenuHomePhabricator

Key 'change_tag_rev_tag' doesn't exist in table 'change_tag'
Closed, ResolvedPublic

Description

Seems like a schema confusion error with either ProblemPager (not sure if that is a thing), flagged reviews or change tags. Example error:

{
  "_index": "logstash-2018.10.01",
  "_type": "mediawiki",
  "_id": "AWYwqQJtccmrnEbcIcos",
  "_version": 1,
  "_score": null,
  "_source": {
    "server": "de.wikipedia.org",
    "db_server": "10.192.48.18",
    "wiki": "dewiki",
    "channel": "DBQuery",
    "type": "mediawiki",
    "error": "Key 'change_tag_rev_tag' doesn't exist in table 'change_tag' (10.192.48.18)",
    "http_method": "GET",
    "@version": 1,
    "host": "mw2175",
    "shard": "s5",
    "sql1line": "SELECT  /*! STRAIGHT_JOIN */ page_namespace,page_title,page_latest,fp_stable AS stable,fp_quality AS quality,fp_pending_since AS pending_since,fp_pending_since  FROM `flaggedpages` FORCE INDEX (fp_pending_since),`revision`,`change_tag` FORCE INDEX (change_tag_rev_tag),`page`    WHERE (fp_pending_since IS NOT NULL) AND (rev_page = fp_page_id) AND (rev_id > fp_stable) AND (ct_rev_id = rev_id) AND (page_id = fp_page_id) AND page_namespace IN ('0','6','10','828','14')   GROUP BY fp_pending_since,fp_page_id ORDER BY fp_pending_since,fp_page_id LIMIT 51  ",
    "fname": "IndexPager::buildQueryInfo (ProblemChangesPager)",
    "errno": 1176,
    "unique_id": "W7JYUgrAID8AACQkPaIAAAAW",
    "method": "Wikimedia\\Rdbms\\Database::makeQueryException",
    "level": "ERROR",
    "ip": "10.192.16.133",
    "mwversion": "1.32.0-wmf.23",
    "message": "IndexPager::buildQueryInfo (ProblemChangesPager)\t10.192.48.18\t1176\tKey 'change_tag_rev_tag' doesn't exist in table 'change_tag' (10.192.48.18)\tSELECT  /*! STRAIGHT_JOIN */ page_namespace,page_title,page_latest,fp_stable AS stable,fp_quality AS quality,fp_pending_since AS pending_since,fp_pending_since  FROM `flaggedpages` FORCE INDEX (fp_pending_since),`revision`,`change_tag` FORCE INDEX (change_tag_rev_tag),`page`    WHERE (fp_pending_since IS NOT NULL) AND (rev_page = fp_page_id) AND (rev_id > fp_stable) AND (ct_rev_id = rev_id) AND (page_id = fp_page_id) AND page_namespace IN ('0','6','10','828','14')   GROUP BY fp_pending_since,fp_page_id ORDER BY fp_pending_since,fp_page_id LIMIT 51  ",
    "normalized_message": "{fname}\t{db_server}\t{errno}\t{error}\t{sql1line}",
    "url": "/wiki/Spezial:Seiten_mit_problematischen_Versionen",
    "tags": [
      "syslog",
      "es",
      "es"
    ],
    "reqId": "W7JYUgrAID8AACQkPaIAAAAW",
    "referrer": "https://<external site>'",
    "@timestamp": "2018-10-01T17:24:34.000Z",
    "db_name": "dewiki",
    "db_user": "wikiuser"
  },
  "fields": {
    "@timestamp": [
      1538414674000
    ]
  },
  "sort": [
    1538414674000
  ]
}

Not the same than T176232.

CC @Marostegui @Ladsgroup Any ongoing schema change related to change tag? CC T185355

Event Timeline

jcrespo created this task.Oct 1 2018, 5:40 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 1 2018, 5:40 PM
Ladsgroup added a comment.EditedOct 1 2018, 5:48 PM

It's forcing using an index that I'm dropping to use the normalized version: https://gerrit.wikimedia.org/g/mediawiki/extensions/FlaggedRevs/+/1a477c4249291e2e20cd8e3adf888b95779673ec/frontend/specialpages/reports/ProblemChanges_body.php
I hate this extension.
We have two options now: completely drop using that index or move it behind the config variable in mediawiki core.

I think you are not asking me specifically, but just FYI I am merely reporting the issue, do not have an opinion on how to fix it (don't event understand what is the issue).

Yeah - I don't have an answer on how to proceed either, whatever is easier to mitigate that impact, and then do a proper fix I would say - once the fire is extinguished

For the record and to give some context - the index is basically being renamed:
Old one:

UNIQUE KEY `change_tag_rev_tag` (`ct_rev_id`,`ct_tag`),

New one:

UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),

Change 463816 had a related patch set uploaded (by Ladsgroup; owner: Amir Sarabadani):
[mediawiki/extensions/FlaggedRevs@master] Use proper index on change_tag table

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

New one:

UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),

We should not have ct_rev_id, it should be change_tag_rev_tag_nonuniq and after switching to read new, it should use the change_tag_rev_tag_id (note the extra _id). the patch

Ladsgroup claimed this task.Oct 1 2018, 6:16 PM

Change 463816 had a related patch set uploaded (by Ladsgroup; owner: Amir Sarabadani):
[mediawiki/extensions/FlaggedRevs@master] Use proper index on change_tag table

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

This should fix the issue.

Restricted Application added a project: User-Ladsgroup. · View Herald TranscriptOct 1 2018, 6:16 PM
Marostegui added a comment.EditedOct 1 2018, 6:30 PM

New one:

UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),

We should not have ct_rev_id, it should be change_tag_rev_tag_nonuniq and after switching to read new, it should use the change_tag_rev_tag_id (note the extra _id). the patch

This is how the table looks like after the patch is applied:

PRIMARY KEY (`ct_id`),
UNIQUE KEY `ct_rc_id` (`ct_rc_id`,`ct_tag`),
UNIQUE KEY `ct_log_id` (`ct_log_id`,`ct_tag`),
UNIQUE KEY `ct_rev_id` (`ct_rev_id`,`ct_tag`),
UNIQUE KEY `change_tag_rc_tag_id` (`ct_rc_id`,`ct_tag_id`),
UNIQUE KEY `change_tag_log_tag_id` (`ct_log_id`,`ct_tag_id`),
UNIQUE KEY `change_tag_rev_tag_id` (`ct_rev_id`,`ct_tag_id`),
KEY `ct_tag` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
KEY `change_tag_tag_id` (`ct_tag`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
KEY `change_tag_tag_id_id` (`ct_tag_id`,`ct_rc_id`,`ct_rev_id`,`ct_log_id`),
KEY `change_tag_rc_tag_nonuniq` (`ct_rc_id`,`ct_tag`),
KEY `change_tag_log_tag_nonuniq` (`ct_log_id`,`ct_tag`),
KEY `change_tag_rev_tag_nonuniq` (`ct_rev_id`,`ct_tag`)

ct_rev_id is not being touched by your patch.

but ct_rev_id supposed to be renamed to change_tag_rev_tag in this patch that got merged nine years ago. Probably it never got deployed. All of indexes starting with ct_ need to be dropped before I can move on with normalizing the table...

Created T205913 to get rid of those ct_ indexes that should not be there

Change 463816 merged by jenkins-bot:
[mediawiki/extensions/FlaggedRevs@master] Use proper index on change_tag table

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

Change 463917 had a related patch set uploaded (by Ladsgroup; owner: Amir Sarabadani):
[mediawiki/extensions/FlaggedRevs@wmf/1.32.0-wmf.23] Use proper index on change_tag table

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

Change 463917 merged by jenkins-bot:
[mediawiki/extensions/FlaggedRevs@wmf/1.32.0-wmf.23] Use proper index on change_tag table

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

Mentioned in SAL (#wikimedia-operations) [2018-10-02T11:19:01Z] <ladsgroup@deploy1001> Synchronized php-1.32.0-wmf.23/extensions/FlaggedRevs/frontend/specialpages/reports/ProblemChanges_body.php: SWAT: [[gerrit:463917|Use proper index on change_tag table (T205904)]] (duration: 00m 57s)

Oops, it's for another reason, I fix it right now.

Change 463941 had a related patch set uploaded (by Ladsgroup; owner: Amir Sarabadani):
[mediawiki/extensions/FlaggedRevs@master] Fix using the old index when new indexes are not there

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

Change 463941 merged by jenkins-bot:
[mediawiki/extensions/FlaggedRevs@master] Fix using the old index when new indexes are not there

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

Change 463943 had a related patch set uploaded (by Ladsgroup; owner: Amir Sarabadani):
[mediawiki/extensions/FlaggedRevs@wmf/1.32.0-wmf.23] Fix using the old index when new indexes are not there

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

Ladsgroup moved this task from Incoming to In progress on the User-Ladsgroup board.Oct 2 2018, 8:18 PM

Change 463943 merged by jenkins-bot:
[mediawiki/extensions/FlaggedRevs@wmf/1.32.0-wmf.23] Fix using the old index when new indexes are not there

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

Mentioned in SAL (#wikimedia-operations) [2018-10-02T23:16:41Z] <ladsgroup@deploy1001> Synchronized php-1.32.0-wmf.23/extensions/FlaggedRevs/frontend/specialpages/reports/ProblemChanges_body.php: SWAT: [[gerrit:463943|Fix using the old index when new indexes are not there (T205904)]] (duration: 00m 57s)

Ladsgroup closed this task as Resolved.Oct 3 2018, 9:55 AM

No error since the last patch got deployed \o/

mmodell changed the subtype of this task from "Task" to "Production Error".Aug 28 2019, 11:08 PM

Change 592306 had a related patch set uploaded (by Zoranzoki21; owner: Zoranzoki21):
[operations/mediawiki-config@master] Add two domains in wgCopyUploadDomains

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

Zoranzoki21 added a subscriber: Zoranzoki21.

Oops, wrong task number... Sorry..