I have a decently large wiki on 1.31.10, and am testing an upgrade to 1.39.5 (allowed per the release notes). To test this, I set up small DB and web server VMs and went through the upgrade process. After 15+ hours, update.php was still running and seemed to be stuck with "Deduplicating ar_rev_id..." as the last output line.

Investigating the DB I can see that the following query is running and has been for pretty much the whole time:

SELECT /* DeduplicateArchiveRevId::doDBUpdates  */ DISTINCT rev_id,rev_timestamp,rev_sha1,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,rev_actor
FROM `archive`,`revision`
JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = rev_actor))
WHERE (ar_rev_id >= 2) AND (ar_rev_id <= 10001)

Eventually the query finishes and then PHP runs out of memory trying to handle the result set.

That query isn't right, though. There is no join condition for revision, so this query pretty much selects all rows from revision and creates a cartesian product. My wiki has 7.5 million rows in revision.

That query comes from here:

// Figure out the ar_rev_ids we actually need to look at
$res = $dbw->newSelectQueryBuilder()
	->select( [ 'rev_id', 'rev_timestamp', 'rev_sha1' ] + $revActorQuery['fields'] )
	->tables( [ 'archive', 'revision' ] + $revActorQuery['tables'] )
	->where( [ 'ar_rev_id >= ' . (int)$id, 'ar_rev_id <= ' . (int)$endId ] )
	->caller( __METHOD__ )
	->distinct()
	->joinConds( [ 'revision', [ 'ar_rev_id = rev_id' ] ] + $revActorQuery['joins'] )
	->fetchResultSet();

After digging through this for a while, I believe the joinConds syntax, changed in 9a12b88c4472, is wrong.

Steps to replicate the issue (include links if applicable):

• Upgrade a mediawiki site from 1.31 to 1.39.
• Unsure if other source versions are affected.

What happens?:
When upgrade runs deduplicateArchiveRevId.php, it doesn't complete successfully.

What should have happened instead?:
When upgrade runs deduplicateArchiveRevId.php, it should complete fairly quickly.

Software version (skip for WMF-hosted wikis like Wikipedia):
1.39

Versions 1.40+ seemingly not affected because deduplicateArchiveRevId.php was deleted: 1d8d24d4dc6a