Page MenuHomePhabricator
Paste P1046

Pre-check of missing users
ActivePublic

Authored by Mattflaschen-WMF on Jul 23 2015, 2:20 AM.
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;
Empty set (0.10 sec)
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;
; 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
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;
Empty set (1.31 sec)
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;
Empty set (0.11 sec)
; Shouldn't affect us, but just in case.
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;
Empty set (0.01 sec)

Event Timeline

Mattflaschen-WMF changed the title of this paste from untitled to Pre-check of missing users.
Mattflaschen-WMF updated the paste's language from autodetect to autodetect.