By Carlos Miranda Levy on

I have successfully migrated my old communities, which shared one big database and forum site, to Drupal and split them into several separate Drupal sites. Now I would like to clean the users table, since, having splitted the communities into separate databases, each site now has a lot of users who have never posted in that particular community.

Can someone advice if it is safe to use the following SQL statement to select (and afterwards delete) all users who have no node and comment associated to them?

Is there any other table, variable, etc. I should look into or alter or is it safe to proceed with the following in Drupal 5.x?

SELECT * FROM users
WHERE users.uid NOT
IN (
SELECT node.uid
FROM node
)
AND users.uid NOT
IN (
SELECT comments.uid
FROM comments
);

* ps: I would add LIMIT 1000 to avoid mysql timing out ... I did notice it took quite a bit on my local Mysql installation and a user table of 10,000 users

⋅ Categories: Drupal 5.x

Comments

rockyrackoon’s picture

rockyrackoon commented

hi
if you delete them using user_delete then it could be safe, but don't just dropt them out of the table.
see api.drupal.org on user_delete:
http://api.drupal.org/api/function/user_delete/

and why don't you use a join ?

adios
rocky

Carlos Miranda Levy’s picture

Carlos Miranda Levy commented

Will try to build a script that uses the function.
I have been looking and there is no module to delete a user and all its content (needed for spammers and trolls).
To clean my databases I used the inactive user module, which did a nice job of deleting everyone with no existing posts and more than a week not visiting.

About the join, just wanted to keep the code simple and readable :p

------
Con paciencia y calma,
sube un burro a una palma