XF 1.5 Help with mysql query to edit users with duplicate email address in phpmyadmin

[island]

Working on importing a 20-year old forum, an issue is that the forum previously did allow more than one username with the same email address. (one reason being members who posted on behalf of a company, who then wished to post a question or comment that didn't represent the view of the company.) In any case, now I need to deal with these since xenforo doesn't allow any duplicate email addresses. I can't simply merge them, as in the above scenario it would be inappropriate as it could merge the personal into the company member name. So I need to go one by one and decide what to do with them, merge, or substitute email address.

I tried with the import script, but after modifying ~200, selecting merge or modify, the script gave me an error when I hit submit at the end and trying again it was gone. The humanity

So second try, is to go through using phpmyadmin.

select userid, email, count(email) as myCount from user group by email having myCount > 1 order by email;

but this doesn't allow me to click to edit in phpmyadmin.

Can anyone help me with an sql query I can use to generate an editable screen in phpmyadmin so I can manually modify any duplicated email addresses (preferably giving me the second username with the same email address where I can click it to edit that duplicate email address.)

Thanks!

 

[island]

Or, is there possibly an sql query that would replace the second (duplicate) occurrence of an email address with emailaddress.theirdomain.com@mydomain.com

This is what I'm trying to actually do so if I anyone would be amazingly kind who knows how to do this programatically, it would save me a lot of clicks! (this way they can either login to xenforo and change it once I've migrated, or if they can't, I can identify it's them based on the email address and help them from there.) Thanks very, very much in advance.

 

[island]

Making a tiny bit of progress on this. The following returns editable rows at least:

SELECT *
FROM testuser
WHERE email IN (
SELECT email
FROM testuser
GROUP BY email
HAVING count(email) > 1
)
ORDER BY email

although it will still be a big task to do this one by one that will probably move me towards carparl tunnel , so still hoping for any tips on a way to automate it and update all duplicate emails through an sql query to emailaddress.theirdomain.com@mydomain.com

 

[AndyB]

This works on vBulletin forums, adapt to your forum as needed.

Run SQL in phpMyAdmin.

SELECT username, email
FROM user
WHERE email IN (SELECT email FROM user GROUP BY email HAVING ( COUNT( email ) > 1 ))
ORDER BY username ASC

 

[island]

Thanks Andy -- greatly appreciated. At least if I can go line by line and edit it saves me some clicks. (the importer is slick, but I have too many for one page I'm afraid, plus since it takes a couple hours, it would mean having the live site offline for that much more time, so I'd rather address it on the vbulletin database before beginning the import.)

I tried the following mysql query which appears to actually accomplish the task, BUT it seems to get stuck and pushes mysql to 100% and never stops.

Any ideas:

(I duplicated user table to testuser and testuser2 tables to try this, applying changes to testuser2 which did change the duplicate email addresses to 2@mydomain.com 3@mydomain.com for userids 2 and 3 which had duplicate emails (and so on) Major drawback is that it doesn't terminate so it seems to hang. Minor drawback is that it changes every occurrence of a duplicate including the first one.)

update testuser2 set email = concat(userid,'@mydomain.com')
WHERE email IN (
    SELECT email
    FROM testuser
    GROUP BY email
    HAVING count(email) > 1
    );

 

[AndyB]

At least if I can go line by line and edit it saves me some clicks.

Do this until you remove all duplicate email.

 

[island]

I was afraid you were going to say that!

The above query makes a few hours of work into a few seconds, and seems to actually accomplish the goal, BUT I can't be sure what is happening when it hangs so I'm afraid to try it to do the actual task instead of just on the test database. I'm sure my attempt is not quite right although it's frustrating as it appears to actually work, albeit in a very ugly fashion as it hangs.

 

[island]

If I can't figure out a clean way to do the same as post#5, an ugly 5-minute solution could be to:

• copy the user table to a local mysql database, copy to testuser table (so I don't hang the production server.)
• Create a temp emailnoat column to remove @ sign in email for next step. Copy email column to emailnoat, then a simple replace

UPDATE testuser SET emailnoat = replace(emailnoat, '@', '-');

• Run the query in post#5 to update testuser (which seems to complete the task in my limited testing on a database with 17,000 users replacing 7 test duplicates. It runs at 100% CPU which isn't great. PHPMyAdmin gives an error I believe timing out, but mysql continues at 100% cpu usage for around 10 minutes , and then goes back to normal)

UPDATE testuser SET email = concat(userid,'-',emailnoat,'@mydomain.com')
WHERE email IN (
    SELECT email
    FROM user
    GROUP BY email
    HAVING count(email) > 1
    );

• Then import testuser table into the production mysql database
• Overwrite the email column in user table with the non-duplicate email column in testuser table

UPDATE user
SET email = (
  SELECT email
  FROM testuser
  WHERE userid = user.userid
)

Irks me that I'm not smart enough to figure out a clean way to do it in one go but I've spent the afternoon trying to figure out a cleaner way to do post#5 and so I think I'll go with this for now.