Deleting users from the OJS database

It is an ongoing nuisance not being able to delete users
The following unix code creates a file with the the database schema showing the tables and fields.

#Using psql Create a list of tables
#Use table list to generate field list for each table
#psql -c “SELECT tablename FROM pg_tables WHERE tablename !~* ‘pg_*’” > tablelist
psql -t -c “select table_name from information_schema.tables where table_schema NOT IN (‘pg_catalog’, ‘information_schema’)” > tablelist

#Use the list to generate the list of fields in each table
touch dbSchema
for tname in cat tablelist
do
echo $tname >> dbSchema
psql -c “SELECT a.attnum, a.attname AS field, t.typname AS type, a.attlen AS length, a.atttypmod AS length_var, a.attnotnull AS not_null, a.atthasdef as has_default FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = ‘$tname’ AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum;”>> dbSchema
done

The resulting tables show the user id field in 15 tables although a user_id may or may not exist in these tables and the the SQL to remove a specific user_id is not difficult.
I have tried deleting a user ID in these tables and so far so good. Would anyone like to comment about he saftey of this and how we can make it work if there is a problem.

Hi @Keir_Vaughan_taylor,

You can delete users from the system by using the “merge users” feature, either in the journal manager’s web interface or with tools/mergeUsers.php. There’s no need to go to the database directly, and as you’ve noted, you may leave some entities orphaned if you do it that way.

Regards,
Alec Smecher
Public Knowledge Project Team

The merge user feature leave references to users in the database. This can preclude the use of their email address or correcting errors and many other idiosyncrasies mentioned on this forum. We need a way of removing users from the database

Hi @Keir_Vaughan_taylor,

Merge users shouldn’t leave any references to the old user; the email address should be open for a new registration. If you’re seeing something left behind, can you post further details?

Regards,
Alec Smecher
Public Knowledge Project Team

On this occasion a user was removed by the Journal Manager, but the details sit in the background so we couldn’t can’t reuse the email address. In the database I found the this user remained in a number of tables. Something may have gone wrong in the past because there were several identities with the same name and but different user IDs. I did go through the tables and remove the offending IDs and it seems to have done the trick. There needs to be a way of cleaning this sort of problem up. i find the merge feature is unsatisfactory.

Hi @Keir_Vaughan_taylor,

Is it possible that the JM used the “Remove” feature, not the “Merge” feature? Remove just clears all roles from the user’s account, but does not delete the account. “Merge” will actually delete the user.

Regards,
Alec Smecher
Public Knowledge Project Team

So if I have a journal manager who has removed the account when they should have merged it, what do I do to clean up the problem? Thanks for your help.

If a role has been removed from a user, you can always re-add the role at a later time. User Home → Journal Manager → Enroll a User from this Site in this Journal.

This will allow the account to show in the Merge Users function again.

Perfect! Thanks for your help.

Hi there,
what would be the way to re-create a user in OJS3. There I couldn’t find the Enroll a User
Thanks

Found a way. I accidentally deleted the user. So, I added an entry in user_user_groups. This way the users pane would show the user again and I could merge it to re-create the account.

1 Like

Dear Asmecher,
we have thousands of spam accounts. I cannot merge one by one.
Would you please let me know how can I delete all the unrolled and/or spam accounts from our system?
We have OJS 3.0.0

I’ve read in How do I remove users for good? - #3 by sienp2016 that exists a command to delete/merge a group of authors. It could be useful. Which command is it?

And finally, how can I insert a captcha or recaptcha anti-spam in our register process?

Thank you so much.
Best,

1 Like

Hi @Isabel-M,

The command-line tool to merge users is tools/mergeUsers.php; to enable ReCAPTCHA testing, see your config.inc.php configuration file.

Regards,
Alec Smecher
Public Knowledge Project Team

Instructions on using Merge Please either tool or Site Administration → Merge Users.

DO you find a BAD user? Go to Merge Users click on them find another BAD user and merge,
You are back at screen 1.

Find that second BAD (SPAMMED USER) in Merge Users by search on user name click to merge them and select a 3rd BAD user.

WE have 6,000 or so BAD users. There are 2 Searches per “merge to get rid of the first”

WE actually seem tohave 908 Users with username starts with

What about that tool?

Do you have to use https://JournalURL/base/tools/mergeUsers.php to find it because there is no menus option to run it?

Is it a different .php code to what you get at Site Adminstration → Merge Users?

WE have 908 users with username starts with “carpinteyr” I takes a lot of time to just merge 10 of them out.

There needs to be in Enrolled users or Merge Users a Tick a lot of users and use a Remove or Delete option that gets them out. that would not be difficult for developers who know what tables they are in and can write sql to delete from all tables by username.

Hi @ais681,

The mergeUsers.php tool is intended for command-line use, not via the web server. If you have command-line access, it’s a much faster way to remove accounts.

Regards,
Alec Smecher
Public Knowledge Project Team

What is the syntax?
You would still have to collect together all the BAD userNames or some Idetifier or is it intelligent so it can get all with BAD email addresses or name fields that do not contain names?

IT says it is a tool for merging 2 accounts so how does it help with removing thousands of spammed ones.?

Why is it better than System Administrations → Merge Users?

Anne

Hi @ais681,

You can get the syntax by running the tool:

 $ php tools/mergeUsers.php 
OJS merge users tool
Use this tool to merge two OJS user accounts.

Usage: tools/mergeUsers.php [username1] [username2]
username1      The first user to merge.
username2      The second user to merge. All roles and content associated
               with this user account will be transferred to the user account
               that corresponds to username1. The user account that corresponds
               to username2 will be deleted.

It’s better for mass account removal than the web-based approach because it’s easier to automate.

Regards,
Alec Smecher
Public Knowledge Project Team

3 Likes

Thanks.

I did get it to work and it is the only way to remove people from the DB when Journal Admins have removed all their roles thinking the remove button removes them as users in the journal.

So I said sorry.
You need to add a role back then get rid of them 1 by one by merge.

Problem is they have thousands of spam added users so it would be good if there was a one screen select a lot press one button interface.

If you send me a list I can get lots in a script.

Anne

Excuse me @henkel,
please can you tell me where I can find the

user_user_groups

I’ve got the same problem in OJS 3.0.2.
Thankz.

Thank you! it helped out