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.
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
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.
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.
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.
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
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.
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?
$ 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
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.
