vBulletin to WordPress bbpress / buddypress – Part 2

Prep old vBulletin DB

The first step in this process is to transfer the data from the Vbulletin site as cleanly as possible across to the bbpress platform

There is luckily an import platform in bbpress which works with vBulletin which we will use. it does not do everything we need so there are some workarounds to get to a working exact replica of the existing forum

Requirements are

• Move the forums, posts, replies and keep current structure – Done by BBpress importer
• Move the users across – BBpress moves users but does not deal with user roles, has an issue with password need to be reset, does not do signatures, does not do avatars
• Move the image albums across – Not available with any plugin or importer currently.
• Moderation for new users and new posts where required
• Spam solution that deals with prevention of spam registration, prevent spam posts, report spam posts easily if they do get through

Step 1 – Clean up your vBulletin DB.

My vBulletin DB is by no means tidy and hopefully these steps will help others to save the time it cost me because of some issues with bbpress importer and for simplicity.

When BBpress imports your users it grabs everyone in the users table from the vBulletin DB it just grabs everyone and makes them a subscriber in the BBpress DB.

This means all of your usergroups which may have been carefully structured in vBulletin now are in a single subscribers user role. Thats all the banned users, users awaiting email confirmation, moderators, admins, + any other specific user roles you may have for your vBulletin site

So you need to really clean up your users before you import this db. So go to your vBulletin Admin > Users > Prune / Move Users

• Choose usergroups as relevant and totally remove
  • Banned users
  • Users awaiting email confirmation
  • (Coppa) users awaiting moderation
  • Any other non active usergroups you dont really need anymore.
• Prune any users who have never been active
  • Choose all usergroups
  • Has not logged in for X days – I set this to 2 years as i want to email all real users who have not been active but might remember the forum
  • Join date is before again 2 years ago
  • Posts less than 1 or 2 Note do not set this to 0 as that will show all users older than the date you chose. that will normally mean at least a few of your oldest users!
  • Order by left as username

A couple of these options gave me over 10k users, if you try to remove them then its likely that you will get an error in vbulleitn that says “Invalid action specified” which is totally unhelpful but after a bit of investigation is because you need to adjust a php directive in order to allow 10k+ concurrent actions to take place. In your php.ini file you need to set max_input_vars = 10000 (or whatever the biggest value of users is i set mine to 15000 so i could remove my banned users list) and it will then run. If you are unsure how to do this search google. You can find out which php ini file is being used by going to the vbulletin admin area and under maintenance there is a php info menu item. in there you are looking for the “Loaded configuration file” path which is your current php ini file. If your file does not contain max_input_vars = x then you can just add the row. you will probably need to restart php after this so do this via command line. again if you are unsure Google it or if its required ill write a guide on how to do this.

Once this is done, play around with the settings as you see fit to remove any and all users your not worried about keeping, you should have a pretty good user table with just the users you want to keep

For any posts created by old users after they have been deleted they will set as guest posts but still showing the old username that originally posted them. I am unsure what effect this might have on the import so i also assigned all the guest posts to a default user using this mysql query and the userdid of an account that was setup early on the forum.

update post
set userid="23144"
where userid = "0";

from here 

you can run this in the vBulletin admincp in the maintenance > sql query section but as i was exporting the db anyway i did this later on in a new copy of the db to be safe.

I also then ran

update post

set username="default"

where userid="23144"; 

so the usernames were against that same user.

finally make sure you do not have any existing users in WordPress with the same names as the forum you are importing or the forum import will change that username to imported_username instead of just importing.

Would be nice to see the importer assign these across but no such luck.

Tags

This was a bit of a problem for me. in my first test import it took aroudn 4 hours to import all my tags, this was due to a few reasons but mainly because i had several vbulletin plugins which combined make a lot of tags.

Auto thread tagging was installed so every time a thread was made it had a lot of tags

VBenterprise translator is a translation plugin which was creating translated versions of all posts and each of these would then be tagged.

Result = Approx 375k tags most of which were pretty crap or just entirely pointless.

My solution to this was to find an automatic tagging plugin for bbpress which would replace all the tags i wanted on the new platform.

As such i opened phpmyadmin and truncated (dropped everything from) the tags table in vbulletin.

Tags = 0 = no problem importing and no need to go through all the tags figuring out which ones to keep.

I will write an update once the new auto tagger is installed and run to show what the results were like.

Old Threads and rubbish threads

This took a little more time and thought. First off i had a large off topic area which was rarely used by anyone. Be really careful here as pruning threads deletes them permanently. make sure you have a backup in case you get things wrong.

• In the admin area of vbulletin go to Threads and posts > Prune
• Choose the options which suit the threads you want to remove. I chose the following
  • last post date at least 4 years ago
  • offtopic subforum
  • not sticky
  • i could have detailed this further with X amount of replies or views but generally i wanted to get rid of the early forums off topic area as it held no real value and didnt drive any current visitors either
  • Basically at this point do what suits your forum in terms of removing old threads that dont drive any traffic or are not required because the forum has changed over the years.
  • One trick to use if you want to keep certain older threads is check the traffic using google analytics or whatever reporting tool and then reply to the thread that drives traffic so that its last updated date is today then prune threads using the last post date as the filter.

Other ways to get rid of unwanted posts and threads

Two nice lines of code I found here to help find and then permanently delete currently soft deleted posts

http://www.xxxxxx/vb/moderation.php?do=viewposts&type=deleted&pp=50&daysprune=100&sort=dateline&order=desc&page=1

and threads

http://www.xxxxxxx/vb/moderation.php?do=viewthreads&type=deleted&pp=20&daysprune=1000&forumid=0&sort=lastpost&order=desc

Which again drops down the size of the db and removes any unnecessary old data

Once you have cleaned your threads up removed any tag data (if you want to and your tags are a mess like mine) you can start the transfer from old forum to new

wp-admin>Tools>forums

at the top you ahve a tab for import forums. the default is vbulletin which suits me and you fill in your db details choose desired options and click start

I set my options to be

• rows limit 250 ( i have a decent server)
• delay time between rows 1 second
• Convert users ticked

This meant i want to import everything including users, forums, topics and replies and if i had any remaining it would also do thread tags

I had some issues with using the bbpress importer when my other forum database was on a remote server so instead i used mysqldump to export the db and reimport it to a new local database for import.

mysqldump -hlocalhost -u USERNAME -pPASSWORD DBNAME | gzip -9 > /path/to/my/folder/forum.`/bin/date +\%Y\%m\%d`.sql.gz

This command exports the mysql db gzips it and adds the date to the end so its easily identifiable. Move that to my test server where i am building this wordpress based forum and unzip it

gunzip forum20160214.sql.gz

import it into the new db i made there again using ssh so i don’t have to worry about timeouts etc

mysql -u testuser -pMYPASSWORD TESTDATABASE < forum20160214.sql

and we have the db in place ready for conversion to bbpress.

Go to tools > forums > import forums and fill out the details as above. Click go, Make a cup of tea and if it stalls at any point you can click stop and then click start and it should resume. When its done you need to go through the repair process to make sure everything is up to date in terms of post counts etc.

Step 3 will deal with the after import process