[$100] Duplicate accounts created on prod

jmgasper commented 3 years ago

@atelomycterus - We had some duplicate accounts created on prod, with the same email and username. Any reason why this would happen? Is there any way to detect them and fix them? These seem to cause problems with the challenge forum processor when adding folks to the groups when they register for a challenge.

jmgasper commented 3 years ago

Contest https://www.topcoder.com/challenges/30156326 has been created for this ticket.

This is an automated message for ghostar via Topcoder X

jmgasper commented 3 years ago

Contest https://www.topcoder.com/challenges/30156326 has been updated - it has been assigned to obog.

This is an automated message for ghostar via Topcoder X

atelomycterus commented 3 years ago

@jmgasper Is PROD configured with Memcached? What branch is deployed on PROD?

There can be one account created by a challenge processor and the other by Topcoder plugin. But I'm not sure. Checks 'if $username and $email are already in use by another member' is done in UserModel only. I've checked User table, there are no unique constraints in DB.

How many duplicate accounts created on prod? select count(*) c, u.Email from GDN_User u group by u.Email having c > 1 order by u.Email
How many duplicate accounts created on prod? select count(*) c, u.Name from GDN_User u group by u.Name having c > 1 order by u.Name
Could we check challenge forum processor logs from 17 November?
- Execute this query select u.UserID from GDN_User u where u.Name='Dylan_Liu'

Check challenge forum processor logs using userID from sql query. New user with UserID=<userID> was added, e.g 'New user with UserID=5 was added'

How many records found? If no records or only one then one or both records are created by Topcoder plugin.

How many groups does he participate in? select ug.GroupID, g.Name, u.UserID, u.Name from GDN_UserGroup ug left join GDN_Group g on ug.GroupID = g.GroupID left join GDN_User u on ug.UserID = u.UserID where u.Name = 'Dylan_Liu'

jmgasper commented 3 years ago

@atelomycterus - Prod is not currently using memcached (we haven't deployed that update yet), and it's running the master branch of the forums. It hasn't been deployed in a couple weeks.

I'm working on running the SQL, thanks.

jmgasper commented 3 years ago

@atelomycterus:

select count(*) c, u.Email from GDN_User u group by u.Email having c > 1 order by u.Email
```
2   ajinkyakolekar.p@gmail.com
2   dylanliuofficial@gmail.com
```
select count(*) c, u.Name from GDN_User u group by u.Name having c > 1 order by u.Name
```
2   Dylan_Liu
2   koleum
```

select ug.GroupID, g.Name, u.UserID, u.Name from GDN_UserGroup ug left join GDN_Group g on ug.GroupID = g.GroupID left join GDN_User u on ug.UserID = u.UserID where u.Name = 'Dylan_Liu'

7   Topcoder Challenge API - Improve testing coverage   44  Dylan_Liu
8   Topcoder Resources API - Improve testing coverage   44  Dylan_Liu
7   Topcoder Challenge API - Improve testing coverage   45  Dylan_Liu

atelomycterus commented 3 years ago

@jmgasper I've reviewed code + tested different cases, but I can't reproduce it. If an issue still exists, in order to avoid duplicate accounts, we need to add unique constraints to email and name on User table. But first we need to decide how to fix the existing duplicates.

Option 1. Using Vanilla 'Delete User'

Click on 'Delete'. There are 3 options. 'Delete the user' means removing user'details. Need to fix Name because after deleting records 'Name' column have duplicates +deploy it to PROD. Deleted Users are not displayed.

Option 2. Write sql script and update tables

Write sql script, use min UserID of duplicated account and update data:

update InsertUserID in Discussion, Comment, Draft, Media and other tables
replace duplicated UserID with min UserID in UserCategory, UserComment, UserGroup, UserDiscussion and other tables
delete duplicated records from UserRole
delete duplicated records from User

Option 3. Write simple sql script and update user records

The same as Option1 but using sql script

set Deleted =1, Name = <handle>.deleted and Email =<email>.deleted for duplicated accounts
don't change other tables
add unique indexes on User Table

Add unique indexes on User Table

Replace indexes with unique indexes on User Table

DROP index FK_User_Name ON GDN_User;
DROP index IX_User_Email ON GDN_User;
CREATE UNIQUE index UX_User_Name ON GDN_User (Name);
CREATE UNIQUE index UX_User_Email ON GDN_User (Email);

DEV

I've checked DEV env. Two duplicated accounts (admin, tc_admin). They were created in February and September. No duplicate accounts since September.

jmgasper commented 3 years ago

@atelomycterus - Ok, thanks. I'll go through and clean this up. It only happened twice, so we'll keep an eye on it and try to see if it happens again, otherwise I'll just chalk it up to a fluke.

jmgasper commented 3 years ago

Payment task has been updated: https://software.topcoder.com/review/actions/ViewProjectDetails?pid=30156326

This is an automated message for ghostar via Topcoder X

topcoder-platform / forums