Be sure do this on your Dev and as a test before Prod.
in OJS the users table is site-wide, so after clone-and-prune youâll still see accounts that donât belong to the abc journal. The safest approach is:
first test run on your DEV/clone. It at least works for OJS 3.4 (uses the modern user_groups/user_user_groups mapping) and also preserves site admins.
0) Safety snapshot
-- One quick backup table (optional but recommended)
CREATE TABLE users_backup_$(date +%Y%m%d) LIKE users;
INSERT INTO users_backup_$(date +%Y%m%d) SELECT * FROM users;
1) Resolve abc â journal_id
SET @JPATHJPATH := âabcâ;
SE@JIDECT @JID := journal_id FROM journals WHERE@JPATHpath = @J@JIDATH;
SELECT @JID AS abc_journal_id;
2) Build the âkeepâ set
-- temp keep set
CREATE TEMPORARY TABLE keep_users (user_id INT PRIMARY KEY);
â (A) Users enrolled in ABC user groups
INSERT IGNORE INTO keep_users (user_id)
SELECT DISTINCT uug.user_id
FROM user_user_groups uug
JOIN user_groups ug ON ug.user_group_id = uug.user_group_id
WHERE @JIDg.context_id = @JID;
â (B) Site admins (role_id = 1) â context_id may be NULL or 0 depending on install
â If your install has no ârolesâ table, this INSERT will simply fail; ignore and proceed.
INSERT IGNORE INTO keep_users (user_id)
SELECT DISTINCT r.user_id
FROM roles r
WHERE r.role_id = 1 AND (r.context_id IS NULL OR r.context_id = 0);
Preview what will be removed:
SELECT COUNT(*) AS total_users FROM users;
SELECT COUNT(*) AS keep_users FROM keep_users;
SELECT COUNT(*) AS to_remove
FROM users u
LEFT JOIN keep_users k USING (user_id)
WHERE k.user_id IS NULL;
make sure you donât nuke yourself
-- keep your admin user explicitly (replace âyour_admin_usernameâ)
INSERT IGNORE INTO keep_users (user_id)
SELECT user_id FROM users WHERE username = âyour_admin_usernameâ;
Run your preview again if you want to double-check:
SELECT COUNT(*) AS total_users FROM users;
SELECT COUNT(*) AS keep_users FROM keep_users;
SELECT COUNT(*) AS to_remove
FROM users u LEFT JOIN keep_users k USING (user_id)
WHERE k.user_id IS NULL;
3) Option A (safer): Disable non-ABC users first
This hides them from logins/UI but preserves history. You can leave it here or proceed to hard delete after you verify.
UPDATE users u
LEFT JOIN keep_users k USING (user_id)
SET u.disabled = 1
WHERE k.user_id IS NULL;
Check Users & Roles now. If you still want them gone entirely, do Option B.
4) Option B (hard delete): remove non-ABC users everywhere
Run only after confirming you kept site admins and all ABC staff/editors.
START TRANSACTION;
â Dependent/many-to-one tables commonly referencing user_id:
DELETE n
FROM notifications n
LEFT JOIN keep_users k ON k.user_id = n.user_id
WHERE k.user_id IS NULL;
DELETE i
FROM user_interests i
LEFT JOIN keep_users k ON k.user_id = i.user_id
WHERE k.user_id IS NULL;
DELETE s
FROM sessions s
LEFT JOIN keep_users k ON k.user_id = s.user_id
WHERE k.user_id IS NULL;
DELETE us
FROM user_settings us
LEFT JOIN keep_users k ON k.user_id = us.user_id
WHERE k.user_id IS NULL;
DELETE uug
FROM user_user_groups uug
LEFT JOIN keep_users k ON k.user_id = uug.user_id
WHERE k.user_id IS NULL;
â If your install still uses the roles table, prune any leftovers (harmless if empty)
DELETE r
FROM roles r
LEFT JOIN keep_users k ON k.user_id = r.user_id
WHERE k.user_id IS NULL;
â Optional: API keys / access tokens tables (present on some installs)
DELETE ak FROM api_keys ak LEFT JOIN keep_users k ON k.user_id = ak.user_id WHERE k.user_id IS NULL;
DELETE uak FROM user_access_keys uak LEFT JOIN keep_users k ON k.user_id = uak.user_id WHERE k.user_id IS NULL;
â Finally, delete the user accounts themselves
DELETE u
FROM users u
LEFT JOIN keep_users k USING (user_id)
WHERE k.user_id IS NULL;
COMMIT;
If any of those tables donât exist in your schema, skip that DELETE line.
If MySQL complains about foreign keys (rare in 3.3), run the block with SET FOREIGN_KEY_CHECKS=0; at the top and =1; at the end.
5) Verify
-- Only ABC users (plus site admins) should remain
SELECT u.user_id, u.username, u.email
FROM users u
LEFT JOIN keep_users k USING (user_id)
ORDER BY (k.user_id IS NOT NULL) DESC, u.username
LIMIT 50;
â How many total left
SELECT COUNT(*) AS remaining_users FROM users;
Notes & gotchas
-
Authors vs. Users: Article authors in OJS are not necessarily âusersâ; deleting users wonât erase authors records linked to publications.
-
Your own account: If your admin is site-level, itâs preserved by the site-admin rule. If youâre enrolled in ABC, itâs preserved by the ABC group rule.
-
UI caching: The âUsers & Rolesâ grid sometimes caches filters. Reload the page after changes.
Done.
â but something to be aware of. If you were e.g. handing this installationâs data off to a 3rd party you might need to be aware of the privacy implications.