How to delete thousands of users?

[sebsch]

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

[DAnderson]

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

[asmecher]

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

[sebsch]

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

[asmecher]

Hi Sebastian,

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

Regards,
Alec Smecher
Public Knowledge Project Team