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]
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
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
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? :/