Page MenuHomePhabricator

Remove USE INDEX usertext_timestamp and other references from code
Open, HighPublic

Description

As @Ladsgroup pointed out here: T238966#6152150 we are seeing errors as there's a piece of code that forces to use user_timestamp index.

A database query error has occurred. Did you forget to run your application's database schema updater after upgrading? 
Query: SELECT  rev_id,rev_page,rev_timestamp,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_sha1,comment_rev_comment.comment_text AS `rev_comment_text`,comment_rev_comment.comment_data AS `rev_comment_data`,comment_rev_comment.comment_id AS `rev_comment_cid`,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,temp_rev_user.revactor_actor AS `rev_actor`,page_namespace,page_title,page_id,page_latest,page_is_redirect,page_len,user_name,page_is_new,(SELECT  GROUP_CONCAT(ctd_name SEPARATOR ',')  FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id))   WHERE ct_rev_id=rev_id  ) AS `ts_tags`  FROM `revision` FORCE INDEX (usertext_timestamp) JOIN `revision_comment_temp` `temp_rev_comment` ON ((temp_rev_comment.revcomment_rev = rev_id)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = temp_rev_comment.revcomment_comment_id)) JOIN `revision_actor_temp` `temp_rev_user` ON ((temp_rev_user.revactor_rev = rev_id)) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = temp_rev_user.revactor_actor)) JOIN `page` ON ((page_id = rev_page)) LEFT JOIN `user` ON ((actor_rev_user.actor_user != 0) AND (user_id = actor_rev_user.actor_user))   WHERE (1=0) AND ((rev_deleted & 4) = 0)  ORDER BY rev_timestamp DESC,rev_id DESC LIMIT 51  
Function: IndexPager::buildQueryInfo (contributions page unfiltered)
Error: 1176 Key 'usertext_timestamp' doesn't exist in table 'revision' (10.64.16.7)

As new wikis do not include this index per the schema change merged at https://gerrit.wikimedia.org/r/#/c/mediawiki/core/+/552339/19/maintenance/archives/patch-revision-actor-comment-MCR.sql this is producing errors.

This force needs to be removed in code before we can proceed with the schema change in production.
Ideally we should review all the index that are being dropped to make sure none of those are being forced as well.

From revision, user_timestamp, page_user_timestamp and usertext_timestamp should all be evaluated and cleaned up, including various comment usages

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 21 2020, 7:53 AM
CCicalese_WMF triaged this task as High priority.May 21 2020, 11:50 AM
Reedy renamed this task from Remove USE INDEX user_timestamp from code to Remove USE INDEX user_timestamp and other references from code.May 26 2020, 11:55 PM
Reedy updated the task description. (Show Details)

Possible references/comments

Targets
    Occurrences of '(usertext_timestamp|page_user_timestamp|usertext_timestamp)' in directory /Users/reedy/PhpstormProjects/mediawiki/core
Found Occurrences  (113 usages found)
    Unclassified occurrence  (80 usages found)
        mediawiki  (80 usages found)
            core  (1 usage found)
                HISTORY  (1 usage found)
                    3634 * (T154872) Fix incorrect ar_usertext_timestamp index names in new 1.28
            core/maintenance/archives  (16 usages found)
                patch-archive-user-index.sql  (1 usage found)
                    4 ADD INDEX usertext_timestamp ( ar_user_text , ar_timestamp );
                patch-drop-archive-ar_usertext_timestamp.sql  (1 usage found)
                    7 DROP INDEX ar_usertext_timestamp ON /*_*/archive;
                patch-drop-archive-usertext_timestamp.sql  (1 usage found)
                    7 DROP INDEX usertext_timestamp ON /*_*/archive;
                patch-drop-user-fields.sql  (2 usages found)
                    18 DROP INDEX /*i*/img_usertext_timestamp,
                    24 DROP INDEX /*i*/oi_usertext_timestamp,
                patch-image-user-index.sql  (1 usage found)
                    8 ADD INDEX img_usertext_timestamp (img_user_text,img_timestamp);
                patch-inverse_timestamp.sql  (2 usages found)
                    12 DROP INDEX usertext_timestamp,
                    15 ADD  INDEX usertext_timestamp (rev_user_text,rev_timestamp);
                patch-oldimage-user-index.sql  (1 usage found)
                    8 ADD INDEX oi_usertext_timestamp (oi_user_text,oi_timestamp);
                patch-random-dateindex.sql  (2 usages found)
                    34 ADD INDEX usertext_timestamp (cur_user_text,inverse_timestamp);
                    46 ADD INDEX usertext_timestamp (old_user_text,inverse_timestamp);
                patch-rename-ar_usertext_timestamp.sql  (2 usages found)
                    6 DROP INDEX ar_usertext_timestamp,
                    7 ADD INDEX usertext_timestamp (ar_user_text,ar_timestamp);
                patch-revision-actor-comment-MCR.sql  (2 usages found)
                    6 DROP INDEX /*i*/page_user_timestamp,
                    7 DROP INDEX /*i*/usertext_timestamp,
                patch-revision-user-page-index.sql  (1 usage found)
                    4 CREATE INDEX /*i*/page_user_timestamp ON /*_*/revision  (rev_page,rev_user,rev_timestamp);
            core/maintenance/sqlite/archives  (23 usages found)
                initial-indexes.sql  (5 usages found)
                    102 CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision_tmp (rev_user_text,rev_timestamp);
                    297 CREATE INDEX /*$wgDBprefix*/ar_usertext_timestamp ON /*$wgDBprefix*/archive_tmp(ar_user_text,ar_timestamp);
                    419 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                    423 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                    427 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
                patch-ar_rev_id-not-null.sql  (1 usage found)
                    43 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                patch-archive-ar_actor.sql  (1 usage found)
                    39 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                patch-archive-ar_comment_id.sql  (1 usage found)
                    42 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                patch-archive-ar_id.sql  (1 usage found)
                    38 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                patch-archive-drop-ar_comment.sql  (1 usage found)
                    43 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                patch-drop-ar_text.sql  (1 usage found)
                    41 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                patch-image-drop-img_description.sql  (1 usage found)
                    40 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                patch-image-img_actor.sql  (1 usage found)
                    36 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                patch-image-img_description-default.sql  (1 usage found)
                    34 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                patch-image-img_description_id.sql  (1 usage found)
                    41 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                patch-oldimage-drop-oi_description.sql  (1 usage found)
                    41 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
                patch-oldimage-oi_actor.sql  (1 usage found)
                    37 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
                patch-oldimage-oi_description_id.sql  (1 usage found)
                    36 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
                patch-rev_text_id-default.sql  (2 usages found)
                    50 CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
                    51 CREATE INDEX /*i*/page_user_timestamp ON /*_*/revision (rev_page,rev_user,rev_timestamp);
                patch-revision-rev_comment-default.sql  (2 usages found)
                    37 CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
                    38 CREATE INDEX /*i*/page_user_timestamp ON /*_*/revision (rev_page,rev_user,rev_timestamp);
                patch-revision-user-page-index.sql  (1 usage found)
                    4 CREATE INDEX /*i*/page_user_timestamp ON /*_*/revision  (rev_page,rev_user,rev_timestamp);
            core/tests/phpunit/data/db/sqlite  (40 usages found)
                tables-1.15.sql  (4 usages found)
                    70 CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
                    94 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                    206 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                    228 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
                tables-1.16.sql  (4 usages found)
                    77 CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
                    101 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                    211 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                    233 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
                tables-1.17.sql  (4 usages found)
                    77 CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
                    101 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                    223 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                    245 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
                tables-1.18.sql  (4 usages found)
                    83 CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
                    107 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                    229 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                    251 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
                tables-1.19.sql  (4 usages found)
                    84 CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
                    109 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                    236 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                    258 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
                tables-1.20.sql  (5 usages found)
                    84 CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
                    85 CREATE INDEX /*i*/page_user_timestamp ON /*_*/revision (rev_page,rev_user,rev_timestamp);
                    110 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                    238 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                    260 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
                tables-1.21.sql  (5 usages found)
                    87 CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
                    88 CREATE INDEX /*i*/page_user_timestamp ON /*_*/revision (rev_page,rev_user,rev_timestamp);
                    115 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                    242 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                    265 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
                tables-1.22.sql  (5 usages found)
                    87 CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
                    88 CREATE INDEX /*i*/page_user_timestamp ON /*_*/revision (rev_page,rev_user,rev_timestamp);
                    116 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                    240 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                    263 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
                tables-1.23.sql  (5 usages found)
                    89 CREATE INDEX /*i*/usertext_timestamp ON /*_*/revision (rev_user_text,rev_timestamp);
                    90 CREATE INDEX /*i*/page_user_timestamp ON /*_*/revision (rev_page,rev_user,rev_timestamp);
                    118 CREATE INDEX /*i*/ar_usertext_timestamp ON /*_*/archive (ar_user_text,ar_timestamp);
                    242 CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                    265 CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
    Usage in comments  (20 usages found)
        mediawiki  (20 usages found)
            core/includes/api  (10 usages found)
                ApiQueryAllDeletedRevisions.php  (3 usages found)
                    226 // Don't query by user ID here, it might be able to use the ar_usertext_timestamp index.
                    233 // Here there's no chance of using ar_usertext_timestamp.
                    312 // Targeting index usertext_timestamp
                ApiQueryAllRevisions.php  (1 usage found)
                    184 // Targeting index rev_timestamp, user_timestamp, usertext_timestamp, or actor_timestamp.
                ApiQueryDeletedRevisions.php  (2 usages found)
                    122 // Don't query by user ID here, it might be able to use the ar_usertext_timestamp index.
                    129 // Here there's no chance of using ar_usertext_timestamp.
                ApiQueryDeletedrevs.php  (2 usages found)
                    193 // Don't query by user ID here, it might be able to use the ar_usertext_timestamp index.
                    200 // Here there's no chance of using ar_usertext_timestamp.
                ApiQueryRevisions.php  (2 usages found)
                    220 //  page_user_timestamp if we have a logged-in rvuser
                    221 //  page_timestamp or usertext_timestamp if we have an IP rvuser
            core/includes/specials/pagers  (2 usages found)
                ImageListPager.php  (2 usages found)
                    210 		 * On the image table: img_user_timestamp/img_usertext_timestamp/img_actor_timestamp,
                    212 		 * On oldimage: oi_usertext_timestamp/oi_actor_timestamp, oi_name_timestamp
            core/maintenance/archives  (6 usages found)
                patch-drop-archive-ar_usertext_timestamp.sql  (2 usages found)
                    1 -- T233221: The index on `archive` variously known as `ar_usertext_timestamp`
                    2 -- and `usertext_timestamp` has a long and sordid history. We're dropping the
                patch-drop-archive-usertext_timestamp.sql  (2 usages found)
                    1 -- T233221: The index on `archive` variously known as `ar_usertext_timestamp`
                    2 -- and `usertext_timestamp` has a long and sordid history. We're dropping the
                patch-rename-ar_usertext_timestamp.sql  (2 usages found)
                    1 -- Rename the archive.ar_usertext_timestamp index to usertext_timestamp.
                    4 -- 1.29 since we plan on renaming the index properly to ar_usertext_timestamp.
            core/maintenance/sqlite/archives  (2 usages found)
                patch-add-3d.sql  (2 usages found)
                    69 --CREATE INDEX /*i*/img_usertext_timestamp ON /*_*/image (img_user_text,img_timestamp);
                    117 --CREATE INDEX /*i*/oi_usertext_timestamp ON /*_*/oldimage (oi_user_text,oi_timestamp);
    Usage in string constants  (13 usages found)
        mediawiki  (13 usages found)
            core/includes/installer  (11 usages found)
                MysqlUpdater.php  (10 usages found)
                    135 [ 'ifTableNotExists', 'actor', 'addIndex', 'archive', 'usertext_timestamp',
                    137 [ 'ifTableNotExists', 'actor', 'addIndex', 'image', 'img_usertext_timestamp',
                    139 [ 'ifTableNotExists', 'actor', 'addIndex', 'oldimage', 'oi_usertext_timestamp',
                    212 'addIndex', 'revision', 'page_user_timestamp', 'patch-revision-user-page-index.sql' ],
                    299 [ 'ifTableNotExists', 'actor', 'addIndex', 'archive', 'usertext_timestamp',
                    300 'patch-rename-ar_usertext_timestamp.sql' ],
                    414 [ 'dropIndex', 'archive', 'ar_usertext_timestamp',
                    415 'patch-drop-archive-ar_usertext_timestamp.sql' ],
                    416 [ 'dropIndex', 'archive', 'usertext_timestamp', 'patch-drop-archive-usertext_timestamp.sql' ],
                    729 			INDEX usertext_timestamp (rev_user_text,rev_timestamp)
                SqliteUpdater.php  (1 usage found)
                    87 'addIndex', 'revision', 'page_user_timestamp', 'patch-revision-user-page-index.sql' ],
            core/includes/specials/pagers  (1 usage found)
                ContribsPager.php  (1 usage found)
                    295 isset( $conds['orconds']['userid'] ) ? 'user_timestamp' : 'usertext_timestamp';
            core/maintenance  (1 usage found)
                cleanupAncientTables.php  (1 usage found)
                    80 'usertext_timestamp',
`

Change 598887 had a related patch set uploaded (by Reedy; owner: Reedy):
[mediawiki/core@master] Remove USE INDEX user_timestamp or usertext_timestamp

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

Quicker search view suggests there's just two live uses, in ContribsPager and FlaggedRev's getQueryData() hook method.

Change 598887 merged by jenkins-bot:
[mediawiki/core@master] Remove USE INDEX user_timestamp or usertext_timestamp

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

daniel renamed this task from Remove USE INDEX user_timestamp and other references from code to Remove USE INDEX usertext_timestamp and other references from code.May 29 2020, 9:40 AM

Change 599767 had a related patch set uploaded (by Daniel Kinzler; owner: Daniel Kinzler):
[mediawiki/extensions/FlaggedRevs@master] getQueryData: remove reference to user_timestamp index

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

Change 599767 merged by jenkins-bot:
[mediawiki/extensions/FlaggedRevs@master] getQueryData: remove reference to user_timestamp index

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

anything else left here after that change was merged?