Clean up accounts with case-insensitive duplicate names

(This combined with #1033360: bakery_request_account() needs to check for existing email and be better at cleanup causes uid 0 to be deleted, which was lots of fun to debug.)

We should go ahead and put binary collations on everything since these duplicate accounts aren't going away quickly. Then proceed with cleanups as killes described.

I ran ALTER TABLE users CHANGE name name varchar(60) collate utf8_bin default NULL; on

http://localize.drupal.org/
http://api.drupal.org/
http://groups.drupal.org/
http://association.drupal.org/
http://association.drupal.org/intranet/
https://security.drupal.org/
http://chicago2011.drupal.org/

And verified each had uid 0, g.d.o did not. I made a note of this at http://drupal.org/node/1030588.

This issue is now about preventing more duplicate accounts.

Properly supporting old accounts which only vary by case isn't just bakery module. The root cause is the database's binary or not collation on the user name column. Bakery (correctly) works with what core does, assuming usernames aren't case sensitive.

Patches and modifications for speed on Drupal.org unintentionally opened up this situation. We always want to get back to vanilla Drupal core when practical, and more-vanilla subsites are running into this right now.

Since we are upgrading Drupal.org to D7, the version doesn't matter. If we want Drupal.org to handle this on D6, we need it there. And on D7 regardless.

What happens is that each subsite will get one user or the other in their users table. Since the core default is non-binary collation, the subsite loads that user for any cases used. Luckily, this doesn't result in you becoming the other user or anything. It does prevent bakery from functioning correctly.

Really, we should never have allowed this on Drupal.org, but it was an unintended side effect of performance improvements. We can't just switch back because we would need to stop new duplicates from happening and resolve the existing ones.

Looks good, committed.

Now that Drupal.org on D7 will prevent new users from using case-insensitive duplicate names, stopping the source of the problem, we need to resolve existing duplicates. We need a plan along the lines of:

1. Block/rename or delete duplicates who have never logged in.
2. Rename duplicates who are blocked.
3. Make a policy & process to handle duplicates between two active people.

Getting ready to kick back to infra.

This currently affects just over 48,280 accounts.

We're (Drupal Association staff) are starting by cleaning up the duplicates of users with elevated roles. They can be found with a query like:

SELECT DISTINCT u.uid, u.name, dupe.name, dupe.uid, dupe.status, from_unixtime(dupe.access), from_unixtime(dupe.login) FROM users_roles ur INNER JOIN users u ON u.uid = ur.uid INNER JOIN users dupe ON lower(dupe.name) = lower(u.name) AND dupe.uid <> u.uid WHERE ur.rid IN (3, 26, 16, 4, 38, 12, 30) ORDER BY u.uid;

Since this is borderline-sensitive information, there is a slim chance someone made a duplicate with the intent of impersonation, I'm not posting them here. We're only getting a handful of accounts fixed manually at this time.

This initial round has been cleaned up, the next is including Git vetted users, (3, 26, 16, 4, 38, 12, 30, 14, 22, 28, 32, 34, 7, 24). 383 more duplicates.

I see quite a few of the duplicates have not logged in, their value in users.access is 0. Should we rename or delete their accounts?

Specifically, 88 out of 379 have users.access = 0.

(4 more were blocked accounts that I renamed.)

The count right now is 48,556.

Notice this has gone up since #18. I believe this is due to #2299919: Ensure accounts created on subsites can not have duplicate usernames. That is closed up as of earlier today, with #2319463: Redirect to Drupal.org for new accounts being backported and deployed don the D6 sites.

Specifically, 88 out of 379 have users.access = 0.

I was mistaken, there were only 9, which are now deleted. The 88, now ~79, have user.login = 0. Are those okay to delete or rename?

There are currently 62 accounts duplicating Git Vetted users with user.login = 0. The most-recent access time is over a year ago, 2013-05-06.

(The query I'm using now is SELECT DISTINCT u.uid, u.name, dupe.name, dupe.uid, dupe.status, from_unixtime(dupe.access), from_unixtime(dupe.login) FROM users_roles ur INNER JOIN users u ON u.uid = ur.uid INNER JOIN users dupe ON lower(dupe.name) = lower(u.name) AND dupe.uid > u.uid AND dupe.login = 0 WHERE ur.rid IN (3, 26, 16, 4, 38, 12, 30, 14, 22, 28, 32, 34, 7, 24) ORDER BY dupe.access;)

Done. I can get a new list once everything is synced down to staging.

We've been consistently choosing to rename blocked accounts that are duplicates of other accounts. Adding the query to find those to the issue summary.

Adding the query from #27 to the issue summary.

This currently affects just over 47,828, we've made a bit of progress. Users with role administrator, content moderator, Documentation moderator, drupal.org issue queue squad, Full HTML user, Git administrator, Git vetted user, list maintainer, Packaging whitelist maintainer, security team, spam fighter, testing administrator, user administrator, and webmaster have had their duplicates cleaned up.

The next roles to tackle are Git user and Not a spammer

For duplicates of accounts with the Git user role, there are 87 accounts, all of which have not actually logged in, and were created over a year ago. We've been deleting those, so I'll continue doing that. It would be good to make this into a general policy, so we can free up unused usernames.

This currently affects 45,486 users.

This currently affects 44,970 users.

Putting in a new “Find blocked duplicates to rename” query that’s much faster. We have 19 cases of that we could resolve easily. 2,2087 total.

Putting in faster query for “Summarize duplicates with elevated roles.” These and duplicates involving blocked accounts have been resolved, except for one where the two accounts appear to be owned by the same person.

We will be renaming accounts starting with those who have not logged in the longest.

I am currently renaming conflicts where an account that has not logged in conflicts with one that has. This will remove roughly 1/3 of the duplicates.

Next round of automatic renames are for people who have not logged in since Jan 1, 2010. That will resolve 8,409 of the 12,322 remaining conflicts.

We’re down the the last 5 conflicts which we’re resolving manually. The queries used to pick users to rename were:

$result = db_query('SELECT count(1) c, sum(u.login = 0) stale, u.name, group_concat(if(u.login = 0, u.uid, NULL)) uids FROM {users} u WHERE u.uid > 0 GROUP BY lower(u.name) HAVING c > 1 AND stale > 0 AND c > stale');
$result = db_query("SELECT count(1) c, sum(u.login = 0) stale, u.name, substr(regexp_substr(group_concat(u.uid ORDER BY u.uid), ',.*'), 2) uids FROM {users} u WHERE u.uid > 0 GROUP BY lower(u.name) HAVING c > 1 AND stale > 0");
$result = db_query("SELECT count(1) c, sum(u.login < unix_timestamp('2010-01-01')) stale, u.name, group_concat(if(u.login < unix_timestamp('2010-01-01'), u.uid, NULL) ORDER BY u.uid) uids FROM {users} u WHERE u.uid > 0 GROUP BY lower(u.name) HAVING c > 1 AND stale > 0");
$result = db_query("SELECT count(1) c, sum(u.login < unix_timestamp('2015-01-01')) stale, u.name, group_concat(if(u.login < unix_timestamp('2015-01-01'), u.uid, NULL) ORDER BY u.uid) uids FROM {users} u WHERE u.uid > 0 GROUP BY lower(u.name) HAVING c > 1 AND stale > 0 AND stale < c");
$result = db_query("SELECT count(1) c, sum(u.login < unix_timestamp('2015-01-01')) stale, u.name, substr(regexp_substr(group_concat(if(u.login < unix_timestamp('2015-01-01'), u.uid, NULL) ORDER BY u.uid), ',.*'), 2) uids FROM {users} u WHERE u.uid > 0 GROUP BY lower(u.name) HAVING c > 1 AND stale > 0");

And the actual renaming was done with

foreach ($result as $row) {
  $uids[] = explode(',', $row->uids);
}
$uids = call_user_func_array('array_merge', $uids);
foreach (array_chunk($uids, 50) as $uids_chunk) {
  foreach (user_load_multiple($uids_chunk) as $account) {
    // Find usable username.
    $n = 0;
    do {
      $n += 1;
      $new_name = $account->name . '-' . $n;
    }
    while (db_query_range('SELECT 1 FROM {users} WHERE lower(name) = lower(:new_name)', 0, 1, [':new_name' => $new_name])->fetchField());
    drush_log(dt('@uid @old_name → @new_name', [
      '@uid' => $account->uid,
      '@old_name' => $account->name,
      '@new_name' => $new_name,
    ]));
    $account->name = $new_name;
    $account_wrapper = entity_metadata_wrapper('user', $account);
    $account_wrapper->field_notes = ltrim($account_wrapper->field_notes->value() . ' ' . dt('Renamed due to https://www.drupal.org/project/infrastructure/issues/1034852'));
    user_save($account);
  }
}