Orphaned records in BD

[rensousa]

Hello All,

I am trying to solve some problems in my ojs database (version 2.4.3-5). When checking the roles table, I noticed that there are some user_id that don’t exist in the users table.

I used the SQL clause below to verify:

SELECT DISTINCT user_id from roles where user_id not in (select user_id from users)

How to fix it?
I thought of adding a record with the nonexistent user_id and using mergeusers to remove it correctly. Does anyone have any other ideas?

Regards,

Renato

[murilodbva]

Hello @rensousa , do you still need what you asked for?
I’m doing for me something similar you asked.
So I did this searches for try to help you.
I’m not sure, but I guess if you find something with these searches, you can DELETE it.
(use on your own)

## Searching orphan users:

SELECT count(*)
FROM access_keys x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM email_log_users x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM event_log x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM group_memberships x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM notes x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM notification_subscription_settings x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM query_participants x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM roles x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM section_editors x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM stage_assignments x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM subscriptions x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM temporary_files x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM user_interests x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM user_settings x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;


SELECT count(*)
FROM user_user_groups x
    LEFT JOIN users u ON (x.user_id = u.user_id)
WHERE u.user_id IS NULL;