F7. Check for other missing users on MediaWiki.org in revision or LQT tables
Closed, ResolvedPublic

Mattflaschen-WMF updated the task description. (Show Details)
Mattflaschen-WMF raised the priority of this task from to High.
Mattflaschen-WMF claimed this task.
DannyH renamed this task from Check for other missing users on MediaWiki.org in revision or LQT tables to F7. Check for other missing users on MediaWiki.org in revision or LQT tables.Jul 23 2015, 12:42 AM

The regexp syntax is to approximate the detection of IP addresses (both IPv4 and IPv6).

There were none in thread, but there were many in revision when I just did the general query. These may be caused by imports.

So I also queried revision for the specific two remaining pages, and it seems we're not affected:

1​mysql:research@s3-analytics-slave [mediawikiwiki]> SELECT thread_author_name, user_name FROM thread LEFT OUTER JOIN user ON thread_author_name = user_name WHERE user_name IS NULL AND thread_author_name NOT REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$' AND thread_author_name NOT REGEXP '^[0-9A-F]*.*:([0-9A-F])*$' LIMIT 1;
2​Empty set (0.10 sec)
3
4​mysql:research@s3-analytics-slave [mediawikiwiki]> SELECT DISTINCT rev_user_text, user_name FROM revision LEFT OUTER JOIN user ON rev_user_text = user_name WHERE user_name IS NULL AND rev_user_text NOT REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$' AND rev_user_text NOT REGEXP '^[0-9A-F]*.*:([0-9A-F])*$' LIMIT 100;
5
6​; Output not shown in case any are suppressed user names, but there were rows, so the below filters to only pages we care about, plus all LQT namespaces
7
8​mysql:research@s3-analytics-slave [mediawikiwiki]> SELECT page_namespace, page_title, rev_user_text, user_name FROM page JOIN revision ON page_id = rev_page LEFT OUTER JOIN user ON rev_user_text = user_name WHERE user_name IS NULL AND rev_user_text NOT REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$' AND rev_user_text NOT REGEXP '^[0-9A-F]*.*:([0-9A-F])*$' AND ( ( page_namespace = 4 AND page_title = 'Support_desk' ) OR ( page_namespace = 0 AND page_title = 'VisualEditor/Feedback' ) OR page_namespace IN ( 90, 91, 92, 93 ) ) LIMIT 100;
9​Empty set (1.31 sec)
10
11​mysql:research@s3-analytics-slave [mediawikiwiki]> SELECT th_user_text, user_name FROM thread_history LEFT OUTER JOIN user ON th_user_text = user_name WHERE user_name IS NULL AND th_user_text NOT REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$' AND th_user_text NOT REGEXP '^[0-9A-F]*.*:([0-9A-F])*$' LIMIT 100;
12​Empty set (0.11 sec)
13
14​; Shouldn't affect us, but just in case.
15​mysql:research@s3-analytics-slave [mediawikiwiki]> SELECT tr_user_text, user_name FROM thread_reaction LEFT OUTER JOIN user ON tr_user_text = user_name WHERE user_name IS NULL AND tr_user_text NOT REGEXP '^[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}\\.[0-9]{1,3}$' AND tr_user_text NOT REGEXP '^[0-9A-F]*.*:([0-9A-F])*$' LIMIT 100;
16​Empty set (0.01 sec)

'Needs Review' column is to request someone look over my SQL.

Glaisher removed a subscriber: Glaisher.Jul 23 2015, 5:03 AM

Looks good to me.

Catrope closed this task as Resolved.Jul 23 2015, 6:42 PM
Catrope moved this task from Needs Review to Done on the Collaboration-Team-Archive-2015-2016 board.