Page MenuHomePhabricator

GlobalUsage table `globalimagelinks` lacks a primary key
Closed, ResolvedPublic

Description

Current table
CREATE TABLE /*_*/globalimagelinks (
	-- Wiki id
	gil_wiki varchar(32) not null,
	-- page_id on the local wiki
	gil_page int unsigned not null,
	-- Namespace, since the foreign namespaces may not match the local ones
	gil_page_namespace_id int not null,
	gil_page_namespace varchar(255) not null,
	-- Page title
	gil_page_title varchar(255) binary not null,
	-- Image name
	gil_to varchar(255) binary not null
) /*$wgDBTableOptions*/;

CREATE UNIQUE INDEX globalimagelinks_to_wiki_page 
	ON /*_*/globalimagelinks (gil_to, gil_wiki, gil_page);
CREATE INDEX globalimagelinks_wiki 
	ON /*_*/globalimagelinks (gil_wiki, gil_page);
CREATE INDEX globalimagelinks_wiki_nsid_title
	ON /*_*/globalimagelinks (gil_wiki, gil_page_namespace_id, gil_page_title);

The unique index should be converted to a primary key.
There appear to be no direct references to the index, see https://codesearch.wmflabs.org/search/?q=globalimagelinks_to_wiki_page&i=nope&files=&repos=

Details

Related Gerrit Patches:

Related Objects

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.

Event Timeline

Restricted Application added a project: User-DannyS712. · View Herald TranscriptJan 31 2020, 5:51 AM
Restricted Application added a subscriber: Aklapper. · View Herald Transcript
DannyS712 moved this task from Unsorted to Next on the User-DannyS712 board.Jan 31 2020, 5:53 AM

Change 571398 had a related patch set uploaded (by DannyS712; owner: DannyS712):
[mediawiki/extensions/GlobalUsage@master] Convert globalimagelinks_to_wiki_page from unique index to primary key

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

jcrespo added subscribers: Marostegui, jcrespo.EditedFeb 20 2020, 3:53 PM

Do you know which WMF wikis this is deployed to? I am guessing commonswiki and testcommonswiki?

Thanks for the work, BTW, this helps with the cleaning up :-D

Marostegui changed the task status from Open to Stalled.Feb 21 2020, 6:25 AM
Marostegui triaged this task as Medium priority.

Stalling until the change is merged and ready to go.

Do you know which WMF wikis this is deployed to? I am guessing commonswiki and testcommonswiki?

Should be commons only. If the table exists on testcommonswiki, there is no config anywhere suggesting it is used.

Reedy added a subscriber: Reedy.Feb 21 2020, 1:06 PM

Do you know which WMF wikis this is deployed to? I am guessing commonswiki and testcommonswiki?

Should be commons only. If the table exists on testcommonswiki, there is no config anywhere suggesting it is used.

Table exists but zero rows

root@cumin1001:/home/marostegui# for i in db2112 db2107 db2105 db2090  db2123 db2129 db2118 db2079; do echo $i; mysql.py -h$i information_schema -e "select table_schema from tables where table_name like 'globalimagelinks';"; done
db2112
db2107
db2105
db2090
+-----------------+
| table_schema    |
+-----------------+
| commonswiki     |
| testcommonswiki |
+-----------------+
db2123
db2129
db2118
db2079
Reedy moved this task from Unsorted to Change on the Schema-change board.Feb 22 2020, 5:23 PM
Reedy changed the task status from Stalled to Open.Wed, Mar 18, 5:20 PM

Patch C+2'd, marking open.

Noting it's already a PK in WMF prod for commons

MariaDB [commonswiki]> explain globalimagelinks;
+-----------------------+------------------+------+-----+---------+-------+
| Field                 | Type             | Null | Key | Default | Extra |
+-----------------------+------------------+------+-----+---------+-------+
| gil_wiki              | varbinary(32)    | NO   | PRI |         |       |
| gil_page              | int(10) unsigned | NO   | PRI | 0       |       |
| gil_page_namespace_id | int(11)          | NO   |     | 0       |       |
| gil_page_namespace    | varbinary(255)   | NO   |     |         |       |
| gil_page_title        | varbinary(255)   | NO   |     |         |       |
| gil_to                | varbinary(255)   | NO   | PRI |         |       |
+-----------------------+------------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

MariaDB [commonswiki]> show indexes from globalimagelinks;
+------------------+------------+----------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name                         | Seq_in_index | Column_name           | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| globalimagelinks |          0 | PRIMARY                          |            1 | gil_to                | A         |   133360133 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          0 | PRIMARY                          |            2 | gil_wiki              | A         |   200040200 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          0 | PRIMARY                          |            3 | gil_page              | A         |   400080401 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          1 | globalimagelinks_wiki            |            1 | gil_wiki              | A         |     3101398 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          1 | globalimagelinks_wiki            |            2 | gil_page              | A         |   400080401 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          1 | globalimagelinks_wiki_nsid_title |            1 | gil_wiki              | A         |     2469632 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          1 | globalimagelinks_wiki_nsid_title |            2 | gil_page_namespace_id | A         |     4041216 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          1 | globalimagelinks_wiki_nsid_title |            3 | gil_page_title        | A         |   400080401 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+------------+----------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)

MariaDB [commonswiki]>

It needs doing for testcommonswiki if we want to keep things consistent, but the table is empty, so it's trivial/cheap

MariaDB [testcommonswiki]> show indexes from globalimagelinks;
+------------------+------------+----------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table            | Non_unique | Key_name                         | Seq_in_index | Column_name           | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------+------------+----------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| globalimagelinks |          0 | globalimagelinks_to_wiki_page    |            1 | gil_to                | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          0 | globalimagelinks_to_wiki_page    |            2 | gil_wiki              | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          0 | globalimagelinks_to_wiki_page    |            3 | gil_page              | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          1 | globalimagelinks_wiki            |            1 | gil_wiki              | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          1 | globalimagelinks_wiki            |            2 | gil_page              | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          1 | globalimagelinks_wiki_nsid_title |            1 | gil_wiki              | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          1 | globalimagelinks_wiki_nsid_title |            2 | gil_page_namespace_id | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| globalimagelinks |          1 | globalimagelinks_wiki_nsid_title |            3 | gil_page_title        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+------------------+------------+----------------------------------+--------------+-----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)

Change 571398 merged by jenkins-bot:
[mediawiki/extensions/GlobalUsage@master] Convert globalimagelinks_to_wiki_page from unique index to primary key

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

confirmed that the PK exists on all s4 hosts for commonswiki.
confirmed that the PK doesn't exist on any s4 host for testcommonswiki

Mentioned in SAL (#wikimedia-operations) [2020-03-19T06:46:45Z] <marostegui> Deploy schema change on testcommonswiki.globalimagelinks (empty table) on the s4 master T243987

Marostegui closed this task as Resolved.Thu, Mar 19, 6:51 AM

Change deployed on s4 primary master for testcommonswiki:

labsdb1012.eqiad.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
labsdb1011.eqiad.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
labsdb1010.eqiad.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
labsdb1009.eqiad.wmnet:3306
dbstore1004.eqiad.wmnet:3314
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db2119.codfw.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db2110.codfw.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db2106.codfw.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db2099.codfw.wmnet:3314
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db2095.codfw.wmnet:3314
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db2091.codfw.wmnet:3314
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db2090.codfw.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db2084.codfw.wmnet:3314
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db2073.codfw.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db1138.eqiad.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db1125.eqiad.wmnet:3314
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db1121.eqiad.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db1103.eqiad.wmnet:3314
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db1102.eqiad.wmnet:3314
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db1097.eqiad.wmnet:3314
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db1091.eqiad.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db1084.eqiad.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),
db1081.eqiad.wmnet:3306
  PRIMARY KEY (`gil_to`,`gil_wiki`,`gil_page`),

labsdb1009 still didn't get the change cause s4 replication thread is lagging a bit there

Thanks for getting this patch merged!