Migrated >60,000 users from vBulletin to bbPress. Now what?!
Okay, so I am working on a migration from vBulletin to bbPress. Everything moved over fine, but now I have over 60k users. This causes a real problem when I want to delete a user, because the Delete User screen creates a pick list with all 60k names!
Here’s what I’m looking for…
- A way to purge all users with no forum topics or replies (or posts, for that matter).
- A way to “nuke” users who have spammed the forums. This would delete the user as well as all of their topics and replies.
- A way to delete users an reattribute their posts/topics/replies to another user (mainly for a handful of folks who already had accounts on the WordPress side before migrating vBulletin over)
Any sage words of advice?
Sage words? Nope 😉
I had a quick look around https://wordpress.org/plugins/search.php?q=bulk+users and nothing really stood out.
The most users I have played around with is ~4,000 and that was too many also for the WordPress admin dashboard. I ended up doing everything I needed directly in phpMyAdmin with SQL queries.
A way to delete users an reattribute their posts/topics/replies to another user (mainly for a handful of folks who already had accounts on the WordPress side before migrating vBulletin over)
With only a handful of these use WordPress’ built in user delete tool, when you select to delete a user you have the option to delete all posts (including topics & replies) or keep them an attribute them to another user.
A way to “nuke” users who have spammed the forums. This would delete the user as well as all of their topics and replies.
First you are going to have to find the spam and record each user id associated and this is painful indeed! I am not to sure on the specifics but there probably is a way to get Akismet to check all of this for you.
A way to purge all users with no forum topics or replies (or posts, for that matter).
I think the quickest way for this would be in phpMyAdmin directly using an SQL query.
The query (Good to verify this stuff before you delete them)
SELECT ID FROM wp_users WHERE ID NOT IN (SELECT DISTINCT post_author FROM wp_posts);
The delete queries (Any user without a post gets deleted)
DELETE FROM wp_usermeta WHERE user_id NOT IN (SELECT DISTINCT post_author FROM wp_posts);
DELETE FROM wp_users WHERE ID NOT IN (SELECT DISTINCT post_author FROM wp_posts);
Thanks, Stephen. This was very helpful. I ran the queries to purge the accounts with no posts, and the membership dropped to about 17,000 — still a lot, but much more manageable.
As for the delete user function, the problem with the current design is that it creates a pick list to attribute the original users’ posts to someone else. For me, that’s a pick list with over 17,000 names. It takes a while to load to say the least!
Glad it helped 🙂
With the dropdown list, even though it is slow you can type the username to get you there quicker without relying on the mouse for 100%, once the dropdown is showing start typing the name, you have to type fast to get more than two or three characters but it is helpful.
The other option you have is to modify the entries directly in phpMyAdmin
UPDATE wp_posts SET post_author = 123 WHERE post_author = 789;
Where 123 is the new user id you want to attribute to the old user id 789
@bigblueball Can I ask why you left vBulletin for bbPress?
I’m looking for an alternative to bbPress because I can’t fix it, it doesn’t work well and has poor support documentation.
I don’t want to make the same mistake again and so I’d appreciate a little feedback on vBulletin.
Stephen, I know about the typeahead feature for select lists, but the list is so long (with thousands of names) that the widget page takes several minutes to load. I have to wait… and wait… and wait before I can even scroll on the page or do anything with any widget. Very unusable.
I understand it is quite unusable but as I stated previously if you only have a handful of users this is required for will it suffice, how many is a ‘handful’ in this case?
In regard to removing spam posts I found this plugin: https://wordpress.org/plugins/clobber-spam-users/
This looks like it could be easily forked and adjusting the search criteria to return the results that meet your criteria to then have it delete the topics & replies for you, then afterwards you will again have users with no posts/topics/replies etc and again masss delete those users.
Thank you very much this helped for me to as i have to a big users list.
Stephen, I don’t think I ever said I had a handful of users. The actual number is over 17,000.
There are several pages in the WordPress admin where a pick list with ALL users is displayed. These pages will tend to several minutes to load and allow me to use them. I mention this because for bbPress to be useful for a volume of users (> 10000) there needs to be some adjustment to the way the user lists are created.
Here are two problem pages:
- Delete Users – the list appears under “Attribute all posts to”
- Widgets – the list of users is loaded under one or more widgets
For the same reason that /wp-admin/users.php only displays 20 users per page, these lists should do something similar. Or rather than a list, use a search.
I figure this isn’t something quickly or easily fixed, but suggest that it be considered in the future. And certainly bbpress.org has the same issue… how do you/they deal with it?
Stephen, I don’t think I ever said I had a handful of users. The actual number is over 17,000
This snippet from your original post was the only context I was referring to for the ‘handful’ term so that even though the experience is painful it will get THIS part of your job done as long as you are not trying to do this for 17,000 users.
Onto the ‘other’ bits of your last post.
For the most part the standard
/wp-admin/users.phpuser admin dashboard panel is ‘enough’ to get day to day tasks done for ‘most’ situations as it does have search (top right) to make this usable.
The delete users
wp-admin/users.php?action=delete&user=123is all WordPress and bbPress does not alter this behaviour though as you state it would be nice if this could be improved for sites with large user bases. To resolve this we will have to wait until WordPress implements some changes here.
None of the bbPress widgets do this
Widgets – the list of users is loaded under one or more widgets
So yes, these things are not just under consideration they are actively thought about constantly and we have tried to keep from implementing this pain in bbPress, but for now until we can get some changes upstream in WordPress core some of this pain will remain.
As I mentioned above on the ‘clobber-spam-users’ plugin I mentioned this plugin as it is not very complex and could be modified to ‘help’ with your cleaning up of 17,000 users topics & replies.
- You must be logged in to reply to this topic.