How I corrected my Opps of allowing moodle to create duplicate user accounts

We are synched with an external database for account creation.  I set this up a few months back.  We had a problem with the sync_users.php script a couple weeks ago.

The sync_users.php script was failing because there was a record coming from the external DB, with the same username of an account that already existed in the mdl_user table. The only difference between the accounts was the authentication method.  I assumed INCORRECTLY, that the accounts coming from the external DB, that had a matching account (username) in the mdl_user table would synch.

In my attempt to solve this conflict, I removed an index on the mdl_user table.  The index is called mdl_user_mneuse_uix.  It consists of the username and mnethostid fields.

As soon as I removed the index from the table, the sync_users.php worked, BUT, created about 30 duplicate accounts.  My co-worker came up with this nifty sql script to show the account dups.

SELECT * FROM
  (SELECT username,COUNT(id) as duplicateCount
  FROM `student`.`mdl_user`
 
  GROUP BY username
 
  HAVING COUNT(id) > 1) as table1
 
  INNER JOIN `student`.`mdl_user` as table2
 
  ON table1.username = table2.username
 
  ORDER BY table2.username

 We found a tracker issue that explained this error and a solution for the removal of the dup accounts.

Once we verified that the accounts duplicated in mdl_user from the external DB had no course progress associated with them - moodle reported them as never having been accessed, I deleted them one at a time then on the remaining account, renamed the authentication method from manual acounts to external authentication.

Manually created authentication method

External database created authentication method

The manual accounts that were changed to external database did not lose any data associated with the account.  I was a little nervous about this initially, but after we tested this assertion on a test instance of the live site, I felt much better.  The account ID never changed, just the authentication method.

Once I had removed the duplicate accounts, I re-created the index on the mdl_user table.

  alter table mdl_user add constraint mdl_user_mneuse_uix unique (`mnethostid`,`username`)

Now, no more dups in moodle and the sync_users.php script is working fine.