Can't get a list of my User talk page edits on Wikidata
Closed, ResolvedPublicBUG REPORT
Actions

Assigned To

Authored By

	Magnus
	Aug 9 2024, 7:54 AM

Description

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

https://www.wikidata.org/w/index.php?title=Special%3AContributions&target=Magnus+Manske&namespace=3&tagfilter=&start=&end=&limit=50

What happens?:

Database error

To avoid creating high database load, this query was aborted because the duration exceeded the limit. If you are reading many items at once, try doing multiple smaller operations instead.
[e9d3dae1-b738-4d94-94eb-91d1eff42705] 2024-08-09 07:48:12: Fatal exception of type "Wikimedia\Rdbms\DBQueryTimeoutError"

What should have happened instead?:
List of my edits on user talk pages

Other information (browser name/version, screenshots, etc.):
Works fine on de.wikipedia:
https://de.wikipedia.org/w/index.php?title=Spezial:Beitr%C3%A4ge&end=&limit=50&namespace=3&start=&tagfilter=&target=Magnus+Manske

Event Timeline

Magnus created this task.Aug 9 2024, 7:54 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 9 2024, 7:54 AM

This is still happening, reproducibly, so not a stale server or something

@Ladsgroup I am not sure what the best tags are for this ticket. Can you help?

Arian_Bozorg moved this task from Incoming to Product Backlog on the [Archived]Wikidata Dev Team board.Aug 27 2024, 2:10 PM

This sounds like another one of optimizer bugs. The easiest way is to force the proper index if the certain conditions are met.

Restricted Application added a project: wmde-wikidata-tech. · View Herald TranscriptAug 28 2024, 11:29 AM

The query is:

SELECT  rev_id,rev_page,rev_actor,actor_rev_user.actor_user AS `rev_user`,actor_rev_user.actor_name AS `rev_user_text`,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`,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`,ores_damaging_cls.oresc_probability AS `ores_damaging_score`,0.385 AS `ores_damaging_threshold`  FROM `revision` FORCE INDEX (rev_actor_timestamp) JOIN `actor` `actor_rev_user` ON ((actor_rev_user.actor_id = rev_actor)) JOIN `comment` `comment_rev_comment` ON ((comment_rev_comment.comment_id = rev_comment_id)) 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)) LEFT JOIN `ores_classification` `ores_damaging_cls` ON (ores_damaging_cls.oresc_model = 11 AND (ores_damaging_cls.oresc_rev=rev_id) AND ores_damaging_cls.oresc_class = 1)   WHERE actor_name = 'Magnus Manske' AND (page_namespace = 3) AND ((rev_deleted & 4) = 0)  ORDER BY rev_timestamp DESC,rev_id DESC LIMIT 51

The explain is:

cumin2024@db1209.eqiad.wmnet[(none)]> show explain for 2992841715;
+------+--------------------+---------------------+--------+----------------------------+-----------------------+---------+------------------------------------------+---------+-------------+
| id   | select_type        | table               | type   | possible_keys              | key                   | key_len | ref                                      | rows    | Extra       |
+------+--------------------+---------------------+--------+----------------------------+-----------------------+---------+------------------------------------------+---------+-------------+
|    1 | PRIMARY            | actor_rev_user      | const  | PRIMARY,actor_name         | actor_name            | 257     | const                                    |       1 |             |
|    1 | PRIMARY            | user                | const  | PRIMARY                    | PRIMARY               | 4       | const                                    |       1 |             |
|    1 | PRIMARY            | revision            | ref    | rev_actor_timestamp        | rev_actor_timestamp   | 8       | const                                    | 2803744 | Using where |
|    1 | PRIMARY            | page                | eq_ref | PRIMARY,page_name_title    | PRIMARY               | 4       | wikidatawiki.revision.rev_page           |       1 | Using where |
|    1 | PRIMARY            | ores_damaging_cls   | eq_ref | oresc_rev_model_class      | oresc_rev_model_class | 7       | wikidatawiki.revision.rev_id,const,const |       1 |             |
|    1 | PRIMARY            | comment_rev_comment | eq_ref | PRIMARY                    | PRIMARY               | 8       | wikidatawiki.revision.rev_comment_id     |       1 |             |
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rev_tag_id,ct_tag_id_id | ct_rev_tag_id         | 5       | wikidatawiki.revision.rev_id             |       1 | Using index |
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                    | PRIMARY               | 4       | wikidatawiki.change_tag.ct_tag_id        |       1 |             |
+------+--------------------+---------------------+--------+----------------------------+-----------------------+---------+------------------------------------------+---------+-------------+
8 rows in set, 1 warning (0.063 sec)

We are actually forcing rev_actor_timestamp and the query takes 2 minutes (51 rows in set (2 min 0.885 sec))
Without the force the explain isn't a lot better anyway (less scans on revision but scans page table) and it actually takes longer for the query to run:

cumin2024@db1209.eqiad.wmnet[wikidatawiki]> show explain for 2992861210;
+------+--------------------+---------------------+--------+-----------------------------------------------------------------+--------------------------+---------+------------------------------------------+--------+---------------------------------+
| id   | select_type        | table               | type   | possible_keys                                                   | key                      | key_len | ref                                      | rows   | Extra                           |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------+--------------------------+---------+------------------------------------------+--------+---------------------------------+
|    1 | PRIMARY            | actor_rev_user      | const  | PRIMARY,actor_name                                              | actor_name               | 257     | const                                    |      1 | Using temporary; Using filesort |
|    1 | PRIMARY            | user                | const  | PRIMARY                                                         | PRIMARY                  | 4       | const                                    |      1 |                                 |
|    1 | PRIMARY            | page                | ref    | PRIMARY,page_name_title                                         | page_name_title          | 4       | const                                    | 231690 |                                 |
|    1 | PRIMARY            | revision            | ref    | rev_actor_timestamp,rev_page_actor_timestamp,rev_page_timestamp | rev_page_actor_timestamp | 12      | wikidatawiki.page.page_id,const          |      1 | Using where                     |
|    1 | PRIMARY            | ores_damaging_cls   | eq_ref | oresc_rev_model_class                                           | oresc_rev_model_class    | 7       | wikidatawiki.revision.rev_id,const,const |      1 |                                 |
|    1 | PRIMARY            | comment_rev_comment | eq_ref | PRIMARY                                                         | PRIMARY                  | 8       | wikidatawiki.revision.rev_comment_id     |      1 |                                 |
|    2 | DEPENDENT SUBQUERY | change_tag          | ref    | ct_rev_tag_id,ct_tag_id_id                                      | ct_rev_tag_id            | 5       | wikidatawiki.revision.rev_id             |      1 | Using index                     |
|    2 | DEPENDENT SUBQUERY | change_tag_def      | eq_ref | PRIMARY                                                         | PRIMARY                  | 4       | wikidatawiki.change_tag.ct_tag_id        |      1 |                                 |
+------+--------------------+---------------------+--------+-----------------------------------------------------------------+--------------------------+---------+------------------------------------------+--------+---------------------------------+
8 rows in set, 1 warning (0.011 sec)

51 rows in set (2 min 45.128 sec)

ABran-WMF triaged this task as Medium priority.Aug 29 2024, 6:22 AM

ABran-WMF moved this task from Triage to Pending comment on the DBA board.

ItamarWMDE moved this task from Incoming to Radar on the wmde-wikidata-tech board.Aug 29 2024, 1:08 PM

I've tried this and it seems to be working now. The query now takes around 10 seconds (the explain is the same). We can reopen if needed, but resolving for now.

Can't get a list of my User talk page edits on WikidataClosed, ResolvedPublicBUG REPORTActions

Description

Event Timeline

Can't get a list of my User talk page edits on Wikidata
Closed, ResolvedPublicBUG REPORT
Actions