User:Pathoschild/2015 sul rename confusion
Are you worried your account will be renamed?
- Check your global account status to make sure you're fully unified.
- Another account that redirects to yours might be renamed. Search this list for your user name to find accounts that redirect to your talk page, and check their global account status.
Still confused? Post a message on User talk:Keegan (WMF) and we'll help!
As part of the 2015 SUL unification, local users who will be renamed due to a name collision were notified on their talk page. Unfortunately the bot that delivered the message followed redirects, which means many users were notified of a pending rename to their global account instead of their secondary local account. This has caused much confusion and tumult.
Affected users
editSee:
- page 1: afwiki to elwiktionary (1413 users)
- page 2: enwiki to enwiktionary (660 users)
- page 3: eowiki to zh-yuewiki (2629 users)
Query
editAssumptions
editThe query below reconstructs the list of affected users based on the following assumptions:
- If a user received a message from User:MediaWiki message delivery with the edit summary "Your account will be renamed" (or translated variants), they were notified about a SUL rename for their account.
- If that user's status before March 2015 was...
- detached: they are not affected even if the bot followed a redirect to their talk page. (The message applies to them anyway.)
- unified: they are affected if there's an incoming redirect from a detached user's talk page on the same wiki.
The list does not include these edge cases:
- Users affected due to a chain of three+ redirects where the second redirect is a detached account (e.g., detached user → detached user → unified user). User:MediaWiki message delivery will post on the second redirect page, so it won't be detected per assumption #2 (notified user isn't unified).
- Users affected due to an interwiki redirect (e.g. detached user@enwiki → unified user@metawiki). User:MediaWiki message delivery will ignore the redirect, so it won't be detected per assumption #2 (notified user isn't unified).
SQL
editSELECT DISTINCT
actual_page.page_title AS redirected_to,
intended_page.page_title AS intended,
lu_attached_method AS intended_sul_status,
CASE WHEN intended_user.user_id IS NOT NULL THEN 1 ELSE 0 END AS intended_user_exists
FROM
{dbname}.revision
INNER JOIN {dbname}.page actual_page ON page_namespace = 3 AND page_id = rev_page
INNER JOIN {dbname}.redirect ON rd_namespace = 3 AND rd_title = actual_page.page_title
INNER JOIN {dbname}.page intended_page ON intended_page.page_namespace = 3 AND intended_page.page_id = rd_from
LEFT JOIN {dbname}.user intended_user ON REPLACE(intended_page.page_title, '_', ' ') = intended_user.user_name
LEFT JOIN centralauth_p.localuser ON lu_wiki = '{dbname}' AND intended_page.page_title = lu_name
WHERE
SUBSTRING(rev_timestamp, 1, 6) = '201503'
AND rev_user_text = 'MediaWiki message delivery'
AND rev_comment IN (
'A suya cuenta se renombrará', -- an
'سوف يتم إعادة تسمية حسابك', -- ar
'Ваш рахунак будзе перайменаваны', -- be-tarask
'Предстои промяна на потребителското Ви име', -- bg
'আপনার অ্যাকাউন্ট পুনঃনামকরণ করা হবে', -- bn
'Vaš korisnički račun će biti preimenovan', -- bs
'Танай бүридхэлэй бэшэлгэ шэнээр нэрлэгдэхэ', -- bxr
'El teu compte serà reanomenat', -- ca
'Váš účet bude přejmenován', -- cs
'Bydd eich cyfri\'n cael ei ailenwi', -- cy
'Din konto vil blive omdøbt', -- da
'Dein Konto wird umbenannt', -- de and de-formal
'Ο λογαριασμός σας θα μετονομασθεί', -- el
'Al tó nòm utàint al s\'rà cambiê', -- eml
'Your account will be renamed', -- en
'Via uzantonomo estos ŝanĝata', -- eo
'Tu cuenta será renombrada', -- es
'Sinu kasutajakonto nimetatakse ümber', -- et
'Sinun tunnuksesi tullaan nimeämään uudelleen', -- fi
'Tín konta fær navnabroyting', -- fo
'Votre compte va être renommé', -- fr
'Din brükerkonto woort amnäämd', -- frr
'Vai cambiar o seu nome de usuario', -- gl
'Nde kuatia omboherajeýta', -- gn
'השם של חשבונך ישונה', -- he
'A felhasználóneved meg fog változni', -- hu
'Notandanafni þínu verður breytt', -- is
'Il tuo nome utente sarà cambiato', -- it
'あなたのアカウントの利用者名が変更されます', -- ja
'თქვენს ანგარიშს სახელი გადაერქმევა', -- ka
'ನಿಮ್ಮ ಖಾತೆಯ ಹೆಸರನ್ನು ಬದಲಾಯಿಸಲಾಗುತ್ತದೆ', -- kn
'당신의 계정 이름이 변경될 것입니다', -- ko
'Сизни тергеу джазыуугъузну аты тюрленникди', -- krc
'Äre Benotzerkont gëtt ëmbenannt', -- lb
'Jūsu konts taps pārsaukts', -- lv
'Вашата сметка ќе биде преименувана', -- mk
'താങ്കളുടെ അംഗത്വം പുനർനാമകരണം ചെയ്യപ്പെടുന്നതാണ്', -- ml
'Akaun anda akan dinamakan semula', -- ms
'तपाईको खाताको नाम परिवर्तन हुनेछ', -- ne
'Uw gebruikersnaam wordt gewijzigd', -- nl
'Brukarkontoen din vil få nytt namn', -- nn
'Nazwa Twojego konta zostanie zmieniona', -- pl
'Sò cont a sarà arbatià', -- pms
'Sua conta será renomeada', -- pt-br
'A sua conta será renomeada', -- pt
'Ваша учётная запись будет переименована', -- ru
'Yer accoont will be renamed', -- sco
'Vaš račun će biti preimenovan', -- sh
'ඔබගේ ගිණුම යළි නම් කෙරේවි', -- si
'Vaš uporabniški račun bo preimenovan', -- sl
'Llogaria juaj do të riemërohet', -- sq
'Ваш кориснички налог ће бити преименован', -- sr
'Ditt konto kommer att byta namn', -- sv
'మీ ఖాతా పేరు మారబోతోంది', -- te
'Ваш обліковий запис буде перейменовано', -- uk
'Tài khoản của bạn sẽ được đổi tên', -- vi
'Suq pruukjakonto nimetedäs ümbre', -- vro
'מען גייט ענדערן דעם נאמען פון אייער קאנטע', -- yi
'您的賬戶將被重新命名' -- zh
)
AND intended_page.page_title NOT LIKE '%/%' -- ignore redirects from subpages (not targeted by notification)
AND (lu_attached_timestamp IS NULL OR SUBSTRING(lu_attached_timestamp, 1, 6) = '201503') -- ignore potential intended users unified before message was sent out (not targeted by notification)
AND (rd_interwiki IS NULL OR rd_interwiki = '') -- only match local redirects (interwiki redirects are out of scope for this query)
ORDER BY actual_page.page_title, intended_page.page_title