Our account creation process relies on syncing with an external database, a setup I implemented a few months ago. A few weeks back, we encountered issues with the sync_users.php script.
The script was failing because the external database contained a record with the same username as an existing account in the Moodle user table (mdl_user), but with a different authentication method. My incorrect assumption was that accounts from the external database with matching usernames in the mdl_user table would automatically synchronize.
To resolve this, I removed an index named mdl_user_mneuse_uix from the mdl_user table. This index was based on the username and mnethostid fields. While removing the index allowed the sync_users.php script to function, it unfortunately led to the creation of approximately 30 duplicate accounts. To identify these duplicates, my colleague created an SQL script:
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
Through a tracker issue, we discovered the cause of this error and a solution for removing the duplicate accounts.
After confirming that the duplicated accounts (originating from the external database) had no associated course progress (Moodle indicated they were never accessed), I deleted them individually. On the remaining account, I renamed the authentication method from manual accounts to external authentication.
Manually created authentication method
External database created authentication method
Changing the manual accounts to use external database authentication did not result in any data loss. This was initially a concern, but testing on a test instance of the live site confirmed that only the authentication method changed, not the account ID.
After removing the duplicate accounts, I recreated the index on the mdl_user table using the following command:
alter table mdl_user add constraint mdl_user_mneuse_uix unique (`mnethostid`,`username`)
With these changes, the duplicate account issue is resolved, and the sync_users.php script is functioning correctly.