Is there a way to delete duplicate users?

[capewellmj]

Hello

When I migrated my site from VM1.1 to VM2 6 months ago it looks like for some reason the users were duplicated which has lead to a huge database and also login problems for users.

Is there a component, script or some kind of procedure that will check for duplicate entries for example usernames and emails and remove the duplicate entries. Despite all the information being duplicated the ID's are all unique.

Martin

Using VM2.0.22c on Joomla 2.5.14

[Maxim Pishnyak]

Did you look in JED available extensions for import-export userbase?

[capewellmj]

I see that there are user export extensions such as userport. How will that help me to clean up duplicated customer information? I need a way to automatically recognize a duplicated entry in the database and to delete one of them. My migration was done 6 months ago. I'm only just deciding to see if I can tackle the issue.

Thanks.

[Maxim Pishnyak]

Did you see how imported data looks usually? CSV files could be reconstructed in Open Office in various ways.

[capewellmj]

Okay I managed to fix it using a database query.

What I did is to go to the database table prefix_users. I went to operations <  Copy Table to, then rename the new table prefix_users_NEW and click structure only then hit go. This created a new table without any content.

Then I clicked the SQL tab and ran the following query:

INSERT INTO `prefix_users_NEW` select * FROM `prefix_users` WHERE 1 GROUP BY `username`;

This pulled all the data from the original table and copied it into the new table but without records with duplicate usernames thus filtering out all the duplicates which were causing log in issues and password reset issues for my older customers.

I then renamed the tables, by clicking into the tables > operations > rename table to: and made the prefix_user into prefix_user_OLD and then went to the prefix_user_NEW table and renamed it into prefix_user.

This method is good because if it doesn't work you can easily rename the tables back to the way they were and even keep them on the server for a while before you delete them to thoroughly test you didn't delete the wrong data.

Originally I ran the query: CREATE TABLE `prefix_users_NEW` select * FROM `prefix_users` WHERE 1 GROUP BY `username`; and the query created the table also. However it did not copy over the structure and table options quite right and it caused errors when I logged into the backend even after I thought I configured the table the way the original was (setting primary field, setting auto increment etc).

Please let me know if there was a more elegant way of doing this. It would have been nice to specify which duplicates to delete e.g. the lower id #'s which I am sure is possible but I am not an expert at SQL.

Finally I went to the Operations Tab in the new table> Table Maintenance > Optimize Table. This deleted the overhead and reduced the table size. I was able to delete about 9000 duplicated user entries.


Note this did not delete the duplicate prefix_virtuemart_userinfos records (obviously) which was not a problem except the massive database size it caused. I'll post later how I fixed that.

Martin

[Maxim Pishnyak]

Nice job with phpmyadmin.