Page MenuHomePhabricator
Create Task
Maniphest T243987

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

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

ProjectBranchLines +/-Subject
mediawiki/extensions/GlobalUsagemaster+14 -4Convert `globalimagelinks_to_wiki_page` from unique index to primary key
Customize query in gerrit

Related Objects
Search...

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.
StatusSubtypeAssignedTask
· · ·
ResolvedLSobanskiT17441 Some tables lack unique or primary keys, may allow confusing duplicate data
ResolvedDannyS712T243987 GlobalUsage table `globalimagelinks` lacks a primary key
· · ·

Event Timeline

DannyS712 created this task.Jan 31 2020, 5:51 AM
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
gerritbot added a comment.Feb 11 2020, 12:55 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

gerritbot added a project: Patch-For-Review.Feb 11 2020, 12:55 AM
DannyS712 moved this task from Next to Awaiting review and deployment on the User-DannyS712 board.Feb 11 2020, 1:05 AM
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

jcrespo added a project: Schema-change-in-production.Feb 20 2020, 3:54 PM
Marostegui changed the task status from Open to Stalled.Feb 21 2020, 6:25 AM
Marostegui triaged this task as Medium priority.
Marostegui moved this task from Backlog to Unclear/blocked to proceed on the Schema-change-in-production board.

Stalling until the change is merged and ready to go.

Bawolff added a subscriber: Bawolff.Feb 21 2020, 1:04 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.

Reedy added a subscriber: Reedy.Feb 21 2020, 1:06 PM
In T243987#5906709, @Bawolff wrote:

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

Marostegui added a comment.Feb 21 2020, 1:07 PM
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.Mar 18 2020, 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)
gerritbot added a comment.Mar 18 2020, 5:31 PM

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

DannyS712 removed a project: Patch-For-Review.Mar 18 2020, 5:31 PM
Marostegui added a comment.Mar 19 2020, 6:44 AM

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

Stashbot added a comment.Mar 19 2020, 6:46 AM

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.Mar 19 2020, 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!

ReleaseTaggerBot added a project: MW-1.35-notes (1.35.0-wmf.25; 2020-03-24).Mar 19 2020, 8:47 PM
DannyS712 moved this task from Awaiting review and deployment to Merged on the User-DannyS712 board.May 27 2020, 8:34 AM
Content licensed under Creative Commons Attribution-ShareAlike 3.0 (CC-BY-SA) unless otherwise noted; code licensed under GNU General Public License (GPL) or 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