Page MenuHomePhabricator

Clean up revision UNIQUE indexes
Closed, ResolvedPublic



Currently the revision tables either have:
PRIMARY KEY (rev_page,rev_id)
UNIQUE KEY (rev_id)

UNIQUE KEY(rev_page,rev_id)


Only the last variant makes sense to me. All DBs should be normalized to this.

Event Timeline

Change 282807 had a related patch set uploaded (by Aaron Schulz):
Remove redundant UNIQUE from rev_page_id

Change 282807 merged by jenkins-bot:
Remove redundant UNIQUE from rev_page_id

Wait, this is not so direct; there is partitioning on the revision tables which on some wikis it is the difference between things directly not working and working, did you have that into account?

Note that I do not agree with mediawiki not knowing about that partitioning, but what should be the plan to support that (if any). It can be "not an issue for now", but eventually this will have to be handled.

To clarify, the scope of the ticket can be done with no issue; however, the "special slaves" have to maintain PRIMARY KEY (rev_id,rev_user) as they partition by user (or alternatively, get rid of the partitioning and search for an alternative for those multiple queries that would fail.

An update on what's going on on the work we have been doing lately to try to unify our revision table and its PK and getting rid of the UNIQUE keys.
The following shards are done:

s1 (codfw is done, currently working on eqiad - T132416)
s4 (commons)
s5 (dewiki, wikidata) (wikidata is pending on the master and will be done once the DC switchover is done)
s6 (frwiki,jawiki,ruwiki)

Edit 11th April:
s7 (arwiki cawiki eswiki fawiki hewiki huwiki kowiki metawiki rowiki ukwiki viwiki) is done

Jaime has unified the recentchanges slaves across the board, so all those are done too.

Edit 25th May
s2 is done

Change 347386 had a related patch set uploaded (by Marostegui):
[operations/mediawiki-config@master] db-eqiad.php: Depool db1073

s7 (arwiki cawiki eswiki fawiki hewiki huwiki kowiki metawiki rowiki ukwiki viwiki) is done

For the record we have faced this: T162774 while cleaning up some UNIQUE keys

Marostegui claimed this task.

All the shards are now done.

Thousands of alters have been done and checked but we could have missed some, if someone finds a wiki which is still not altered, please let me know.