Converting a large vBulletin site to XenForo

Zeras

Active member
I am in the test phase of converting a 5+ million posts vb3 site to XenForo. I have followed XenForo since before its release. I lurked regularly throughout the IB legal fiasco and was very happy when the settlement was announced. Now I have finally decided to make the switch to XenForo.

I stayed with vb3 for the same reason many sites have. It was fast and just works. However, it is also old and dated. I watched friends go through the vb4 (or vb3.9 as I refer to it) nightmare and wanted no part of it. I bought an IPB license because it was not an IB product and it looked clean and solid, only to dive into the code and learn how bloated and un-optimised it is. 90+ database queries on a single page? Who does that? I cannot imagine running a high traffic site on IPB without a cluster of servers.

I used the built-in VB3-to-XenForo importer to import all of the data except threads and posts. Based on a sample test conversion for posts and threads, I estimated it was going to take 24+ hours to convert with the built-in importer. So I wrote a custom cli-based program to convert the posts and threads, but it was only slightly faster than the built-in system and was still going to require way too much downtime.

So instead I decided to try converting the tables inside mysql. The mysql conversion method took about 30 minutes to convert the 5+ million posts on my development server and I expect it will be even faster on the live servers. So far, everything seems to have converted fine though I still need to spend some time looking more closely at the post/thread tables to make sure everything converted correctly.

Well that's my story so far. I look forward to becoming an active member here and if anyone has any additional conversion tips they can pass on, that would be great! I hope to do the live conversion in a week or so once I have had time to create a custom theme.

-Zeras
 
Have you checked out Slaviks really fast importer? Its good for vb3 now.
Slavik's Amazingly Fast And Responsive Importer
Enjoy...

I was going to buy it but he took it down for some reason. I don't really need it now since I was able to write my own conversion script that converted the 5+ million posts in around 30 minutes. As I said though, I still have to verify the data in the tables, but from the front-end side, everything looks good.

-Zeras
 
Did you convert all data or did you choose to loose data?

I still have to check over the data more thoroughly to be sure with regards to the posts and threads, but I used the built-in importer for everything else. It helped that I didn't have to worry with attachments.
 
I still have to check over the data more thoroughly to be sure with regards to the posts and threads, but I used the built-in importer for everything else. It helped that I didn't have to worry with attachments.

The built in importer misses a lot of data. Check out the following log, as it has many things that the standard importer does not have:
Code:
#################################################
### Digital Point A.R.F.I. vB4 -> XF Importer ###
#################################################
1 - Posts
2 - Users
3 - Threads
4 - Private Messages
5 - Avatars
6 - Attachments
7 - Forums
8 - Miscellaneous
9 - Everything
Make Selection [1-9]: 9
exporting posts...
          [forking process for posts]
          post IPs (18,007,231 records in 537.21s)...
          likes (405,330 records in 4.53s)...
          post edit history (269,767 records in 40.05s)...
          reported posts (184,715 records in 84.92s)...
          reported post comments (211,405 records in 61.45s)...
          reported profile posts (713 records in 0.17s)...
          reported profile post comments (788 records in 0.36s)...
          reported private messages (5,962 records in 1.52s)...
          reported private message comments (6,825 records in 2.29s)...
Total time for posts: 732.62s
exporting users...
          [forking process for users & user authenticate]
          user external auth (17,694 records in 2.26s)...
          user follow (210,683 records in 0.57s)...
          user ignored (10,736 records in 0.20s)...
          user notes (30,649 records in 3.17s)...
          user options (654,462 records in 5.19s)...
          user profile (654,462 records in 65.50s)...
          user privacy (654,462 records in 5.97s)...
          user warnings (410,608 records in 60.84s)...
          user bans (23,963 records in 7.55s)...
          warning action triggers (23,963 records in 3.39s)...
          user field (187,730 records in 108.30s)...
          user registration IPs (654,462 records in 8.03s)...
          user account confirmation IPs (213,661 records in 5.93s)...
          user change log (464,391 records in 9.22s)...
          user change log IPs (337,774 records in 3.78s)...
          profile posts (54,412 records in 1.32s)...
          profile post IPs (49,840 records in 1.54s)...
          user upgrades active (483 records in 0.06s)...
          user upgrades expired (1,966 records in 0.04s)...
          user upgrades log (7,207 records in 0.59s)...
Total time for users: 293.44s
*** Don't forget to rebuild user cache ***
exporting threads...
          [forking process for threads]
          watched threads (10,838,001 records in 30.97s)...
          thread user post (12,752,077 records in 105.70s)...
          polls (240,405 records in 13.68s)...
          thread read (261,856 records in 1.67s)...
Total time for threads: 152.02s
*** Don't forget to rebuild poll cache ***
exporting private messages...
          [forking process for conversation master]
          conversation message (449,871 records in 264.23s)...
          conversation recipient (679,335 records in 18.66s)...
          conversation user (679,335 records in 13.00s)...
          conversation IPs (439,731 records in 7.65s)...
Total time for private messages: 303.66s
performing avatar diff...
        1 users with new avatars
733016
Total time for avatars: 14.07s
performing attachment diff...
        1 new attachments
98836
        updating attachment view count
Total time for attachments: 1.52s
exporting forums...
          nodes (147 records in 0.23s)...
          forums (140 records in 0.15s)...
          link forums (10 records in 0.15s)...
          forum read (10,713 records in 0.23s)...
          watched forums (3,164 records in 0.15s)...
Total time for forums: 0.90s
exporting micellaneous stuff...
          deletion log (996,651 records in 11.44s)...
          moderator log (894,108 records in 63.27s)...
          daily stats (23,263 records in 99.99s)...
          directory categories (11 records in 0.15s)...
          directory entries (119 records in 0.13s)...
          marketplace (224,965 records in 4.46s)...
          marketplace bids (84,538 records in 1.47s)...
          marketplace bid IPs (71,961 records in 6.79s)...
          marketplace transactions (6,044 records in 0.81s)...
          advertising transactions (4,577 records in 0.53s)...
          ebook transactions (478 records in 1.33s)...
          ebooks (804 records in 0.56s)...
          watched items (478 records in 0.14s)...
          marketplace licenses (447 records in 0.15s)...
          marketplace ratings (68 records in 0.13s)...
          marketplace files (804 records in 0.15s)...
          marketplace item attachments (804 records in 18.09s)...
          marketplace item attachments data (804 records in 0.76s)...
          marketplace item file data (804 records in 67.80s)...
Total time for micellaneous stuff: 278.16s
          [waiting on process for users & user authenticate] DONE.
          [waiting on process for conversation master] DONE.
          [waiting on process for threads] DONE.
          [waiting on process for posts] DONE.
    Grand Total For Everything: 33 minutes, 3 seconds (74,587,823 records)
 
That looks very thorough! Fortunately, in my case, most of the extra things it converts were not available in the vb3 release I run and many of the others that were available were disabled. The built-in importer converted everything we needed and the custom thread/post converter I wrote seems to have worked quite well.

I can see where most vbulletin sites would need something more thorough as they likely have a lot of features enabled, plugins installed, etc. In this case, the only data that will likely be lost in the conversion will be logs which I really don't want converted.

I appreciate you posting the output logs of the converter as it will help me make sure I'm not missing anything or forgetting something!

Thanks!

-Zeras
 
I final my 12 million of posts importation today, im reindexing right now the search in elastic.
I use the xenforo importer to import forums, users, prefixes, user groups, etc and import the posts and thread from vB3.8 database with two queries without problems. Later to rebuild threads from CP, all looks ok and i have the 12million of posts in his respective forum

The queries, where forum3_6 is the name of your vbulletin database. Use at your own risk.

Code:
INSERT IGNORE INTO  xf_post (post_id, thread_id, user_id, username, post_date, message) SELECT postid, threadid, userid, username, dateline, pagetext FROM forum3_6.post;

Code:
INSERT IGNORE INTO xf_thread (nodeid, thread_id, title, reply_count, view_count, user_id, username, post_date, sticky,
first_post_id, last_post_date, last_post_id, last_post_user_id, last_post_username) SELECT
forumid, threadid, title, replycount, views, postuserid, postusername, dateline, sticky, firstpostid, lastpost,
lastpostid,postuserid, lastposter FROM forum3_6.thread;
 
The queries, where forum3_6 is the name of your vbulletin database. Use at your own risk.
Code:
INSERT IGNORE INTO xf_thread (nodeid, thread_id, title, reply_count, view_count, user_id, username, post_date, sticky,


first_post_id, last_post_date, last_post_id, last_post_user_id, last_post_username) SELECT
forumid, threadid, title, replycount, views, postuserid, postusername, dateline, sticky, firstpostid, lastpost,
lastpostid,postuserid, lastposter FROM forum3_6.thread;

Nice work!

Shouldn't the 'nodeid' in that statement be 'node_id'? Also, are you not worried about deleted posts and threads being undeleted?

-Zeras
 
Yes, as you say replace nodeid by node_id, sorry i replace in the console but not in the text file where save the query. The insert ignore into allow dont insert the duplicate data if they exist in the table previously. I run the two queries for only 10.000 records to test that work before import the 12 million of posts, later when i run the query, they insert all records except the initial 10.000.
 
Yes, as you say replace nodeid by node_id, sorry i replace in the console but not in the text file where save the query. The insert ignore into allow dont insert the duplicate data if they exist in the table previously. I run the two queries for only 10.000 records to test that work before import the 12 million of posts, later when i run the query, they insert all records except the initial 10.000.

I didn't have to worry about duplicate content because it was a fresh install, but it's a good idea to use IGNORE anyway just in case. I am not near my server right now, but I can remember I used two queries for the post conversion and two for the thread conversion. In each query, I limited one run to deleted posts and one to non-deleted posts
Sorry i forget to say that i delete in the source (bulletin) all soft deleted posts.
That makes sense. In my case I wanted to keep the soft-deleted posts just in case, but it is unlikely that any of them would be undeleted in the future.
 
Top Bottom