OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



How to delete thousands of users?

Are you responsible for making OJS work -- installing, upgrading, migrating or troubleshooting? Do you think you've found a bug? Post in this forum.

Moderators: jmacgreg, btbell, michael, bdgregg, barbarah, asmecher

Forum rules
What to do if you have a technical problem with OJS:

1. Search the forum. You can do this from the Advanced Search Page or from our Google Custom Search, which will search the entire PKP site. If you are encountering an error, we especially recommend searching the forum for said error.

2. Check the FAQ to see if your question or error has already been resolved.

3. Post a question, but please, only after trying the above two solutions. If it's a workflow or usability question you should probably post to the OJS Editorial Support and Discussion subforum; if you have a development question, try the OJS Development subforum.

How to delete thousands of users?

Postby sebsch » Mon Dec 10, 2012 3:10 pm

Hi there,

Wondering how the number of registered readers in one of our journals could increase to 15000 I have had a look at the entries - there are lots of fake users. There common characteristic is often that first, middle and last name are the same. Working on the DB directly, I think it should be easy to filter them like this and to delete them. But it seems that the users table is linked to other tables and that deleting the user names would leave thousands of entries in other tables.

Does anyone have an idea how to tackle this problem?

Best regards,
Sebastian
sebsch
 
Posts: 10
Joined: Sat Apr 30, 2011 7:47 am

Re: How to delete thousands of users?

Postby DAnderson » Tue Dec 11, 2012 9:29 am

Hello Sebastian,

The way I deal with invalid users here is to determine a common thread throughout the fake accounts. Usually phone number e.g. "123456" or like you have seen first, middle or last name. One thing to watch for is matching first and last name since some of these have been valid. I have found it not necessary to remove user info from other tables like "user_interest: etc. since they take no real needed space in the database.

I usually start out like the following:

select * from users where phone like "123456"; or another item I want to key off of. first_name, last_name, email etc.

once I determine a block of fake users I delete them as follows:

delete from users where phone = '123456'; or any other field fond to be invalid.

Also if you feel the need to remove the user id's from other tables I would go with the following:

select user_id from users where phone like "xxxxx" or first_name like "xxxxxx" or ---- into outfile...........

then create a pl-sql script that will read the out file so you can use it to delete the user id's from your other tables.

Hope this helps.

All best,
Delvon

Delvon Anderson
Systems Analyst
ULS - Information Technology
University of Pittsburgh
Room 129, 7500 Thomas Blvd.
Pittsburgh, Pa. 15208
Last edited by DAnderson on Tue Dec 11, 2012 11:45 am, edited 1 time in total.
DAnderson
 
Posts: 1
Joined: Tue Dec 11, 2012 9:11 am

Re: How to delete thousands of users?

Postby asmecher » Tue Dec 11, 2012 10:28 am

Hi all,

Note that while we don't recommend going directly to the database when deleting active users (since you may leave orphaned submissions, review assignments, etc.), spam accounts generally affect only the users and potentially user_settings tables. Delvon's advice is fine, but you may also find you leave records in user_settings; you can check for these by running:
Code: Select all
SELECT us.user_id FROM user_settings us LEFT JOIN users u ON (u.user_id = us.user_id) WHERE u.user_id IS NULL;
Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 8673
Joined: Wed Aug 10, 2005 12:56 pm

Re: How to delete thousands of users?

Postby sebsch » Fri May 17, 2013 2:02 am

Hi Alec, hi Delvon,

thank you for your advices. I hadn't tried them because we have lots of small annoying problems with our journals. We decided to upgrade to continue getting help here. That's what we have done now – we are running 2.4.2.0. Do your hints and SQL commands remain the same for the new version?

Thank you,
Sebastian
sebsch
 
Posts: 10
Joined: Sat Apr 30, 2011 7:47 am

Re: How to delete thousands of users?

Postby asmecher » Fri May 17, 2013 9:36 am

Hi Sebastian,

Yes, the same queries apply to OJS 2.4.x.

Regards,
Alec Smecher
Public Knowledge Project Team
asmecher
 
Posts: 8673
Joined: Wed Aug 10, 2005 12:56 pm


Return to OJS Technical Support

Who is online

Users browsing this forum: Google [Bot] and 3 guests