OJS OCS OMP OHS

You are viewing the PKP Support Forum | PKP Home Wiki



Delete the spam reviewer interests

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.

Delete the spam reviewer interests

Postby linghe » Wed Apr 24, 2013 8:31 am

Hi All,

I just want to share the experience of deleting the spam reviewer interests from OJS. A while ago, we had found many spam users in our system, so we had used the merge users function as suggested in this forum to get them removed. But shortly after a user found there are many spam reviewer interests existing in our system. After some investigations, we think those spam reviewer interests were probably created by those deleted spam users. So we constructed some SQLs, select and delete those spam interests. Here is the select sql:
Code: Select all
SELECT controlled_vocab_entry_id FROM controlled_vocab_entry_settings WHERE setting_name='interest' AND controlled_vocab_entry_id NOT IN (SELECT DISTINCT a.controlled_vocab_entry_id FROM controlled_vocab_entry_settings a, user_interests b, users c WHERE a.setting_name='interest' AND b.controlled_vocab_entry_id=a.controlled_vocab_entry_id AND b.user_id=c.user_id)

The controlled_vocab_entry_id selected here are those spam interests. Because the field exists in a few tables, we ran 3 delete queries to delete them from controlled_vocab_entries, controlled_vocab_entry_settings, and user_interests. The first 2 are identical. For deleting from users_interests, we just deleted those user_ids not in users table.

Thanks,
Ling
linghe
 
Posts: 3
Joined: Mon Nov 05, 2012 2:02 pm

Re: Delete the spam reviewer interests

Postby asmecher » Mon Jul 15, 2013 10:44 am

Hi all,

Here's some alternative SQL to view and clean user interests that are no longer used (e.g. because they were introduced by spam user accounts which have already been deleted). The relevant entries are in the controlled_vocab_entry, controlled_vocab_entry_settings, and user_interests tables. To view them:
Code: Select all
SELECT cves.setting_value, u.user_id
FROM controlled_vocabs cv
    JOIN controlled_vocab_entries cve ON (cv.controlled_vocab_id = cve.controlled_vocab_id)
    LEFT JOIN controlled_vocab_entry_settings cves ON (cve.controlled_vocab_entry_id = cves.controlled_vocab_entry_Id)
    LEFT JOIN user_interests ui ON (cve.controlled_vocab_entry_id = ui.user_id)
    LEFT JOIN users u ON (ui.user_id = u.user_id)
WHERE cv.symbolic = 'interest' AND u.user_id IS NULL;
If all the contents returned are ready for deletion, you can delete them using the following (works in MySQL, not tested in PostgreSQL):
Code: Select all
DELETE cves, cve, ui
FROM controlled_vocabs cv
    JOIN controlled_vocab_entries cve ON (cv.controlled_vocab_id = cve.controlled_vocab_id)
    LEFT JOIN controlled_vocab_entry_settings cves ON (cve.controlled_vocab_entry_id = cves.controlled_vocab_entry_Id)
    LEFT JOIN user_interests ui ON (cve.controlled_vocab_entry_id = ui.user_id)
    LEFT JOIN users u ON (ui.user_id = u.user_id)
WHERE cv.symbolic = 'interest' AND u.user_id IS NULL;

As always, make a full backup of your database before modifying it manually.

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

Re: Delete the spam reviewer interests

Postby keir » Mon Mar 31, 2014 7:50 pm

The delete syntax produces errors. it would be cool if that syntax were possible but this deletes a record that refers to a line in another record that also needs to be deleted but after the deletion the reference is gone. Whether that interp is right or not the psql interpreter rejects the supplied deletion syntax and it needs to be done as a independent deletions.
keir
 
Posts: 19
Joined: Sun Aug 12, 2012 9:43 pm

Re: Delete the spam reviewer interests

Postby asmecher » Tue Apr 01, 2014 7:57 am

Hi Keir,

As noted above, the query works in MySQL, but was not tested in PostgreSQL. The multi-table delete syntax may need some adaptation for PostgreSQL.

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


Return to OJS Technical Support

Who is online

Users browsing this forum: Google [Bot], Yahoo [Bot] and 4 guests