Page MenuHomePhabricator

Index uniqueness mismatches in links tables in wikis that were moved from s3 to s5
Closed, ResolvedPublic

Description

enwikivoyage seems to have issues with T256680: imagelinks has index mismatch on s8 and T250060: tl_namespace index on templatelinks is unique only in s8 that supposed to be done but since these wikis were moved from s3 to s5, they fell into cracks. I assume there are other wikis that templatelinks.tl_namespace and imagelinks.il_to mismatched.

Progress:

  • enwikivoyage.imagelinks
    • eqiad
    • codfw
  • enwikivoyage.templatelinks
    • eqiad
    • codfw

Event Timeline

Marostegui added a project: Data-Persistence.
Marostegui moved this task from Triage to Ready on the DBA board.

So this affects only enwikivoyage.templatelinks and enwikivoyage.imagelinks. The rest of wikis are ok for both tables.

Marostegui triaged this task as Medium priority.Oct 14 2020, 5:41 AM

Mentioned in SAL (#wikimedia-operations) [2020-10-14T06:12:58Z] <marostegui> Change UNIQUE into KEY on enwikivoyage.imagelinks T265445

Executed the following on enwikivoyage.imagelinks:

alter table imagelinks drop key il_to, add key `il_to` (`il_to`,`il_from`);

That table is now fixed:

labsdb1012.eqiad.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
labsdb1011.eqiad.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
) ENGINE=InnoDB DEFAULT CHARSET=binary ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8
labsdb1010.eqiad.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
labsdb1009.eqiad.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
dbstore1003.eqiad.wmnet:3315
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db2139.codfw.wmnet:3315
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db2137.codfw.wmnet:3315
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db2128.codfw.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db2123.codfw.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db2113.codfw.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db2111.codfw.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db2099.codfw.wmnet:3315
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db2094.codfw.wmnet:3315
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db2089.codfw.wmnet:3315
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db2075.codfw.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db1150.eqiad.wmnet:3315
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db1145.eqiad.wmnet:3315
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db1144.eqiad.wmnet:3315
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db1130.eqiad.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db1124.eqiad.wmnet:3315
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db1113.eqiad.wmnet:3315
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db1110.eqiad.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db1100.eqiad.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db1096.eqiad.wmnet:3315
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)
db1082.eqiad.wmnet:3306
  PRIMARY KEY (`il_from`,`il_to`),
  KEY `il_backlinks_namespace` (`il_from_namespace`,`il_to`,`il_from`),
  KEY `il_to` (`il_to`,`il_from`)

The following has been run on s5 master

alter table templatelinks drop key tl_namespace, add key `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`);

The index is now fixed:

# ./section s5 | while read host port; do echo "$host:$port"; mysql.py -h$host:$port enwikivoyage -e "show create table templatelinks\G" | grep -w KEY   ; done
labsdb1012.eqiad.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
labsdb1011.eqiad.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
labsdb1010.eqiad.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
labsdb1009.eqiad.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
dbstore1003.eqiad.wmnet:3315
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db2139.codfw.wmnet:3315
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db2137.codfw.wmnet:3315
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db2128.codfw.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db2123.codfw.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db2113.codfw.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db2111.codfw.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db2099.codfw.wmnet:3315
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db2094.codfw.wmnet:3315
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db2089.codfw.wmnet:3315
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db2075.codfw.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db1150.eqiad.wmnet:3315
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db1145.eqiad.wmnet:3315
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db1144.eqiad.wmnet:3315
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db1130.eqiad.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db1124.eqiad.wmnet:3315
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db1113.eqiad.wmnet:3315
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db1110.eqiad.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db1100.eqiad.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db1096.eqiad.wmnet:3315
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
db1082.eqiad.wmnet:3306
  PRIMARY KEY (`tl_from`,`tl_namespace`,`tl_title`),
  KEY `tl_backlinks_namespace` (`tl_from_namespace`,`tl_namespace`,`tl_title`,`tl_from`),
  KEY `tl_namespace` (`tl_namespace`,`tl_title`,`tl_from`)
Marostegui updated the task description. (Show Details)