Page MenuHomePhabricator

Special:AbuseFilter on all large Wikipedias is returning errors
Closed, ResolvedPublic

Description

English Wikipedia Special:AbuseFilter is reporting:

A database query error has occurred. This may indicate a bug in the software.

Function: IndexPager::buildQueryInfo (AbuseFilterPager)
Error: 2013 Lost connection to MySQL server during query (10.64.48.28)

According to ops, the query seems to simply take too long, so the special page will have to be smarter with it's queries ?

Edit: [this happens on all wikipedias, with a large enough abuse filter log]

Event Timeline

TheDJ raised the priority of this task from to Needs Triage.
TheDJ updated the task description. (Show Details)
TheDJ added a project: AbuseFilter.
TheDJ subscribed.
jcrespo triaged this task as High priority.Jul 24 2015, 9:57 AM
jcrespo added a project: User-notice.
jcrespo set Security to None.

Change 226692 had a related patch set uploaded (by Legoktm):
Revert "Add "Last hit" timestamp column to Special:AbuseFilter list view"

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

jcrespo renamed this task from Special:AbuseFilter on English Wikipedia is returning errors to Special:AbuseFilter on all large Wikipedias is returning errors.Jul 24 2015, 10:13 AM
jcrespo updated the task description. (Show Details)

This is the current explain plan for that query:

mysql> EXPLAIN SELECT af_id,af_enabled,af_deleted,af_global,af_public_comments,af_hidden,af_hit_count,af_timestamp,af_user_text,af_user,af_actions,af_group,MAX(afl_timestamp) AS afl_timestamp FROM `abuse_filter` LEFT JOIN `abuse_filter_log` ON ((af_id=afl_filter)) WHERE af_deleted = '0' GROUP BY af_id ORDER BY af_id LIMIT 51\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: abuse_filter
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 1
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: abuse_filter_log
         type: ALL
possible_keys: afl_filter
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10802528
        Extra: Range checked for each record (index map: 0x80)
2 rows in set (0.00 sec)

The key here is Extra: Range checked for each record (index map: 0x80), which means the index afl_filter will sometimes be used and somtimes not, having just a bit better effect that a full table scan. Forcing the index use is ignored:

mysql> EXPLAIN SELECT af_id,af_enabled,af_deleted,af_global,af_public_comments,af_hidden,af_hit_count,af_timestamp,af_user_text,af_user,af_actions,af_group,MAX(afl_timestamp) AS afl_timestamp FROM `abuse_filter` LEFT JOIN `abuse_filter_log` FORCE INDEX(afl_filter) ON ((af_id=afl_filter)) WHERE af_deleted = '0' GROUP BY af_id ORDER BY af_id LIMIT 51\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: abuse_filter
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 1
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: abuse_filter_log
         type: ALL
possible_keys: afl_filter
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10802528
        Extra: Range checked for each record (index map: 0x80)
2 rows in set (0.00 sec)

Change 226695 had a related patch set uploaded (by Legoktm):
Revert "Add "Last hit" timestamp column to Special:AbuseFilter list view"

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

Change 226692 merged by jenkins-bot:
Revert "Add "Last hit" timestamp column to Special:AbuseFilter list view"

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

Change 226695 merged by jenkins-bot:
Revert "Add "Last hit" timestamp column to Special:AbuseFilter list view"

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

Legoktm claimed this task.

Backported and deployed. https://en.wikipedia.org/wiki/Special:AbuseFilter now loads for me.

I wonder if a calculated column via a subquery would help here.

And why is a varchar(64) <> BIGINT unsigned equality appearing in a query? :/