Fixing emails that vary only by case

Due to a bug in the way that Drupal validates that email addresses associated with user accounts are unique, it was possible for multiple user accounts to have an email address that differs only by case.

Please note that until https://www.drupal.org/project/drupal/issues/3490639 is fixed, this report might show false positives if you have users translated into multiple languages.

For example, one account might have alice@example.com and another might have Alice@Example.com.

This SQL query will find any such problems in the database:

SELECT ufd.uid, ufd.name, ufd.mail, ufd.status
FROM users_field_data ufd
JOIN (
  SELECT LOWER(mail) AS lower_mail
  FROM users_field_data
  GROUP BY LOWER(mail), langcode
  HAVING COUNT(uid) > 1
) duplicates ON LOWER(ufd.mail) = duplicates.lower_mail
ORDER BY LOWER(ufd.mail), ufd.uid;

uid 	name        	mail                	status
2   	alice       	alice@example.com   	1
3   	also-alice  	Alice@Example.com   	1

With the bug fixed, Drupal should enforce the uniqueness of the email field if any changes are made to the user accounts. This will prevent affected users from updating their account information, unless they update their email address as well.

Details vary by email host (see this discussion for example), however in most cases an email sent to either of the addresses in the example above would arrive in the same mailbox. Each site will need to decide how to resolve this problem for existing accounts.

The best approach to solve the problem may differ depending on who the users are and what service providers are used for email.

For some sites, it may be appropriate to block affected user accounts. Customizing the message visitors receive when their account is blocked may be beneficial. The message can be customized by overriding the string 'The username %name has not been activated or is blocked.' in settings.php: see the "String overrides" section in default.settings.php.

If you have user accounts without email addresses (for whatever reason, perhaps localhost development), then the warning "Conflicting user emails" will also be triggered on the status page even if there are no email conflicts. The SQL query above will return Empty set (0.00 sec). If you add emails to all these accounts, then the warning will stop, as long as they are all different.