Posted by Warith Al Maawali on Mar 27, 2016 in Blog, Source-Codes | Comments Off on Migrating from Vbulletin to Burning board

I established my first online community forum oman0.net in August 2001. The forum was initially based on 2 software later on I shifted to Vbulletin 3 then finally I settled with Vbulletin 4. During that period I was quite happy with the Vbulletin forums performance and features I could not wait to test Vbulletin 5. However I normally wait for users reviews before I shift into a major update which in my case was to shift from Vbulletin 4 to Vbulletin 5. None of the reviews were promising in terms of features or security for almost 3 years I felt that I was left behind by the technology Vbulletin 5 does not fulfill my needs and I have to look for an alternative. So my first approach was to try developed by Kier Darby that originally served as a lead developer for the community platform vBulletin. I installed it I was happy with it and it was time to check the reviews I noticed that many users compared it with so I decided to give Burning board a shot. Both were excellent software and way better than Vbulletin but Burning board was always a head with features and had matured coding so after two months of continues testing on my simulated board I decided to move my 14 years old forum with its 160k+ members to Burning board. Moving was not easy I have gone through many tough decisions and bumps to achieve this goal. Therefore I am writing this blog as a reference for those who intends to move to Burning board and look for a decent written guide to follow. I have been officially using WBB since 20 October 2015 you can see it live on my other site om77.net, I am so happy with the choice I made not to forget behind WBB you will find a cooperative friendly support team that will ensure your problems are solved on time.
 
 
 

Blog Index:

• Migration steps
• Tweaks and Fixes
• Custom scripts
• Resources

 

Migration steps:

 

First take a backup of your database in my case Vbulletin stored data in Swidish_latin_ci so I had to use same character set on export:

 mysqldump --opt -u DB_USER --password=DB_Password --default-character-set=latin1 --skip-extended-insert DB_NAME -r Exported_DB_NAME.sql

You can verify file encoding type it may be Asci or Latin:

 file -ib Exported_DB_NAME.sql

WBB stores data in utf8 format so before running the WBB Vbulletin importer you need to convert the DB copy into utf8 format which is used in WBB to do so we need to replace all words of latin1 to utf8:

 sed -e's/latin1/utf8/g' Exported_DB_NAME.sql > Exported_DB_NAME_sed.sql

Now install the following packages that are needed for the conversion process:

 yum install dejavu-sans-mono-fonts
 yum install recode

Then Prepare your OS for UTF8 you may need to do this every time before you run the converting tool:

 export LANG=en_US.utf8

WBB support team suggested the following command but it did not work in my case as the data stored was in Arabic so don’t use it! I am just adding it for reference:

 iconv -f ISO-8859-1 -t UTF-8 DB_NAME.sql  > UTF8_DB.sql    # Don't use this command if your data is stored in Arabic

So instead I used this tool to convert the Database from Swidish_latin_ci to required utf8 (utf8_general_ci) or (utf8_unicode_ci):

 recode ms-arab Exported_DB_NAME_sed.sql    

Once its ready now you can import the utf8 converted DB make sure you create a test DB first:

mysql -u DB_USER --password=DB_Password --default-character-set=utf8 Test_DB_NAME < Exported_DB_NAME_sed.sql  

Note that the web based importer did not work with me if you have a large board it is recommended to use SSH combined with CLI as well screen command to keep the session even if you get disconnected. I also had to clear private message and visitors message tables because there were huge and mostly loaded with spam so if you intend to do the same then empty the following tables on vBulletin DB:
visitormessage, visitormessage_hash, pm, pmreceipt, pmtext and pmthrottle

Then I had to run the following query:

UPDATE user 
SET vmunreadcount = 0, 
vmmoderatedcount = 0,
pmtotal = 0,
pmunread= 0;

Run screen as root:

screen

Run to avoid quieting the session if you press the wrong keys such as CTRL D use the following command:

IGNOREEOF=4 

Then you can start the import process by entering the following command into the screen session:

php cli.php --packageID=4    
# I used ID=4 becuase to import from Vbulletin 4 make sure you use the correct ID
# When you get promoted to enter the path make sure you enter the full path to Vbulletin like: /home/oman0/public_html/ 

# To see file document root via php
<?php
	echo $_SERVER['DOCUMENT_ROOT'];
?> 

After a successful login, enter:

import

Because you had to run the above commands as root you need to change file owner info once each phase is completed to avoid errors:

chown om77.om77 * -R

Once the importer is done, you should update the counters with the following consideration:

• Run the counter commands with domain user not root !:

  su -l om77

• Execute to the imported directory before running the counter commands:

  chown om77.om77 * -R 

• For long time execution commands you have to quite and enter again the CLI I will add bellow a comment on each long process command.
• Many of your problems can be fixed by running all counter workers once in the correct order.
• If you had to rerun the counter you can skip the last 3, if they were already run.
• After switching to user use the command export

  IGNOREEOF=4 

  to avoid ctrl D from logging off the user.

• Lock old Vbulletin forum directory with htaceess to avoid session table locks in mysql.
• Keep watching phpmyadmin for status and what job is lbeing locked.
• Never have mysql log_bin enabled in mysql.cnf while doing this!.

worker wcf\\system\\worker\\LikeRebuildDataWorker      
worker wcf\\system\\worker\\LikeUserRebuildDataWorker 
worker wbb\\system\\worker\\PostRebuildDataWorker                              # Long
worker wbb\\system\\worker\\ThreadRebuildDataWorker     
worker wbb\\system\\worker\\BoardRebuildDataWorker  
worker wcf\\system\\worker\\ConversationMessageRebuildDataWorker       # Long only if private messages and visitor message are not cleared
worker gallery\\system\\worker\\AlbumRebuildDataWorker   
worker gallery\\system\\worker\\ImageRebuildDataWorker   
worker gallery\\system\\worker\\ThumbnailRebuildDataWorker                  # You will ge a warning after runing this just ignore it
worker blog\\system\\worker\\BlogRebuildDataWorker           
worker calendar\\system\\worker\\EventDateRebuildDataWorker  
worker wcf\\system\\worker\\ConversationRebuildDataWorker  
worker calendar\\system\\worker\\EventRebuildDataWorker   
worker filebase\\system\\worker\\FilebaseRebuildDataWorker  
worker wcf\\system\\worker\\UserRebuildDataWorker    
worker wcf\\system\\worker\\AttachmentRebuildDataWorker   
worker wcf\\system\\worker\\StatDailyRebuildDataWorker    
worker wbb\\system\\worker\\PostSearchIndexRebuildDataWorker

Now you should be done with the import process you can try to login to the forum.
 

---

 

Tweaks and Fixes:

To change the country flag on the language file edit the second line in the exported language XML as follow:

<language xmlns="http://www.woltlab.com"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.woltlab.com http://www.woltlab.com/XSD/maelstrom/language.xsd" 
languagecode="ar-om" languagename="Oman" countrycode="om">

To translate the language file export the English language in the ACP. Translate the XML-file and reimport it.

To trouble shoot thread and posts tables run those queries and if they return 0 rows then everything is OK:

SELECT * FROM wbb1_post WHERE threadID NOT IN (SELECT threadID FROM wbb1_thread)
SELECT * FROM wbb1_thread WHERE boardID NOT IN (SELECT boardID FROM wbb1_board)

To remove BB codes in wall comments:

UPDATE wcf1_comment SET `message`=REPLACE(`message`,'[url]','');
UPDATE wcf1_comment SET `message`=REPLACE(`message`,'[/url]','');
UPDATE wcf1_comment SET `message`=REPLACE(`message`,'[img]','');
UPDATE wcf1_comment SET `message`=REPLACE(`message`,'[/img]','');
UPDATE wcf1_comment SET `message`=REPLACE(`message`,'[b]','');
UPDATE wcf1_comment SET `message`=REPLACE(`message`,'[/b]','');
UPDATE wcf1_comment SET `message`=REPLACE(`message`,'[color="Teal"]','');
UPDATE wcf1_comment SET `message`=REPLACE(`message`,'[/color]','');

To solve indent issue on posts:

UPDATE wbb1_post SET `message`=REPLACE(`message`,'[INDENT]','');
UPDATE wbb1_post SET `message`=REPLACE(`message`,'[/INDENT]','');

To solve issues with images do not have thumbnail after import:

update gallery1_image set tinyThumbnailSize='6523' where tinyThumbnailSize='0';
update gallery1_image set smallThumbnailSize='13830' where smallThumbnailSize='0';
# Below 200 in hight we have a problem so run:
update gallery1_image set smallThumbnailSize='0' where height < 300;

To solve displaying wrong BBcode on signatures:

UPDATE wcf1_user SET `signature`=REPLACE(`signature`,'[/size]','');
UPDATE wcf1_user SET `signature`=REPLACE(`signature`,'[SIZE=5]','');
UPDATE wcf1_user SET `signature`=REPLACE(`signature`,'[h=1]','');
UPDATE wcf1_user SET `signature`=REPLACE(`signature`,'[/h]','');
UPDATE wcf1_user SET `signature`=REPLACE(`signature`,'[size=2]','');
UPDATE wcf1_user SET `signature`=REPLACE(`signature`,'[COLOR="SlateGray"]','');

Replace words on user-names and blog titles:

UPDATE wcf1_user SET `username`=REPLACE(`username`,'Duplicate ','');
UPDATE wcf1_user SET `username`=REPLACE(`username`,'Duplicate2','');
UPDATE blog1_blog SET `title`=REPLACE(`title`,'Blog','');  
UPDATE blog1_blog SET `title`=REPLACE(`title`,'’s','');

To set an avatar from gravatar to all users who have none:

update wcf1_user set enablegravatar=1 where avatarid is null and enablegravatar=0;

Delete users own likes after disabling it under acp -> -> system -> options -> messages -> general -> like system

DELETE l.* FROM wcf1_like l LEFT JOIN wbb1_post p ON p.postID = l.objectID WHERE p.userID = l.userID AND p.postID = l.objectID;

DELETE ae.* FROM wcf1_user_activity_event ae LEFT JOIN wcf1_like l ON ae.objectID = l.objectID WHERE ae.objectTypeID = (SELECT objectTypeID FROM wcf1_object_type WHERE objectType = 'com.woltlab.wbb.likeablePost.recentActivityEvent') AND l.objectID IS NULL;

To enable guests so they see the forum always in Arabic add at the end of the file config.inc.php in the WCF directory the following line:

$ _SERVER ['HTTP_ACCEPT_LANGUAGE'] = 'ar-ar';

To change forum icon colors:

UPDATE `wbb1_board` SET  `iconNewColor` ='rgba(22, 46, 69, 1)' where `iconNewColor` = 'rgba(13, 111, 249, 1)';

To reduce the number of users who are shown on birthday list I had to run the following queries:

To select records from two tables wcf1_user_option_value and wcf1_user where wcf1_user.wbbPosts < 100 and birth date on wcf1_user_option_value not equal to 0000-00-00:

 SELECT *
    FROM wcf1_user_option_value
    JOIN wcf1_user ON wcf1_user_option_value.userid = wcf1_user.userid
    WHERE wcf1_user.wbbPosts < '100' and wcf1_user_option_value.userOption2 !='0000-00-00';

Now once we know what are the expected results we can run the update.

To update a table based on information on another table giving that you have primary and foreign key on those tables:

    UPDATE wcf1_user_option_value AS U1,  wcf1_user AS U2 
    SET U1.userOption2 = '0000-00-00'
    WHERE U1.userid =U2.userid  and U2.wbbPosts < '100';

I had symbol problem with Blogs, Threads and Posts and to replace them I used the following queries:

SELECT  `blogID`,`message` FROM blog1_entry WHERE  `message` REGEXP '.*&#[0-9]+';

SELECT threadid, topic
FROM wbb1_thread
WHERE topic REGEXP '.*&#[0-9]+'

SELECT `postid`, `subject`
FROM wbb1_post
WHERE `subject` REGEXP '.*&#[0-9]+'

UPDATE blog1_entry SET `subject`=REPLACE(`subject`,'я','я');
UPDATE blog1_entry SET `message`=REPLACE(`message`,'👍','♥'); 
UPDATE wbb1_thread SET `topic`=REPLACE(`topic`,'&#1610','');
UPDATE wbb1_post SET `subject`=REPLACE(`subject`,'&#1575','');

Here are some useful codes to be used in templates they were posted in WBB forums:

To show different content to guests and logged in members:

 {if $__wcf->user->userID}Logged In{else}Guest{/if}

To show content to a specific user group:

{if GROUP_ID_HERE|in_array:$__wcf->user->getGroupIDs()}

To hide content from a specific user group:

 {if !GROUP_ID_HERE|in_array:$__wcf->user->getGroupIDs()}

To show content to more than one user group:

{if GROUP_ID_HERE|in_array:$__wcf->user->getGroupIDs() || GROUP_ID_HERE|in_array:$__wcf->user->getGroupIDs() || GROUP_ID_HERE|in_array:$__wcf->user->getGroupIDs()}

To hide content from more than one user group:

{if !(GROUP_ID_HERE|in_array:$__wcf->user->getGroupIDs() || GROUP_ID_HERE|in_array:$__wcf->user->getGroupIDs() || GROUP_ID_HERE|in_array:$__wcf->user->getGroupIDs())}

To show content to more than one member:

 {if $__wcf->user->userID == USER_ID_HERE || $__wcf->user->userID == USER_ID_HERE || $__wcf->user->userID == USER_ID_HERE || $__wcf->user->userID == USER_ID_HERE}

To show content if the user is banned:

 {if $__wcf->user->banned}

To show content to members with 0 posts:

 {if $__wcf->user->wbbPosts == 0}

To show content to members with more than x posts:

 {if $__wcf->user->wbbPosts > X}

To show content to members with less than x posts:

 {if $__wcf->user->wbbPosts < X}

To show show content to members who have an avatar:

 {if $__wcf->user->avatarID || $__wcf->user->enableGravatar}

To show content to members who do not have an avatar:

 {if !($__wcf->user->avatarID || $__wcf->user->enableGravatar)}

To show content to members who have completed a custom user field:

 {if $__wcf->user->userOptionXXX} 

where XXX is the ID of the userOption
To show content to members who have not confirmed their email address:

 {if $__wcf->user->activationCode}

To allow certain user group like registered,approved ,and not banned so they see a page or a script:

{if 18|in_array:$__wcf->user->getGroupIDs() || 19|in_array:$__wcf->user->getGroupIDs()|| 20|in_array:$__wcf->user->getGroupIDs()|| 21|in_array:$__wcf->user->getGroupIDs()|| 22|in_array:$__wcf->user->getGroupIDs()|| 23|in_array:$__wcf->user->getGroupIDs()|| 24|in_array:$__wcf->user->getGroupIDs()|| 25|in_array:$__wcf->user->getGroupIDs()|| 3|in_array:$__wcf->user->getGroupIDs()|| 4|in_array:$__wcf->user->getGroupIDs()}||{if !($__wcf->getUser()->banned)}
  
<script type="text/javascript" src="sx.php" charset="utf-8"></script>
{/if}
{/if} 

To use the login authentication function on another script:

<?php
require('/path/to/wcf/lib/util/PasswordUtil.class.php');
if (\wcf\util\PasswordUtil::checkPassword($username, $password, $dbHash)) {
        // okay
}
else {
        // $password falsch
}
?>

To check how many records have been stored to elasticsearch’s search index:

curl -XGET 'http://localhost:9200/wcf/com_woltlab_wbb_post/_count?q=*'

To install Elasticsearch on CentOS:

    cd ~
    sudo yum update
    su -c "yum install java-1.8.0-openjdk"
     
    wget https://download.elastic.co/elasticsearch/elasticsearch/elasticsearch-1.7.2.zip  
    unzip elasticsearch-1.7.2.zip 
    rm -f elasticsearch-1.7.2.zip
    mv elasticsearch-* elasticsearch 
    sudo mv elasticsearch /usr/local/share
     
    curl -L http://github.com/elasticsearch/elasticsearch-servicewrapper/tarball/master | tar -xz
    mv *servicewrapper*/service /usr/local/share/elasticsearch/bin/
    rm -Rf *servicewrapper*
    sudo /usr/local/share/elasticsearch/bin/service/elasticsearch install
    sudo /etc/init.d/elasticsearch start
     
    # To test
    curl http://localhost:9200 

---

 

Custom scripts:

With the following 2 scripts I solved birthday format issue after the conversion from VB to WBB:
# Script 1

<?php
/* PHP script written by W. Al Maawali  
# (c) 2016 Founder of Eagle Eye Digital Solutions
# http://www.digi77.com
# http://www.om77.net
# script starts here:*/

/* Attempt MySQL server connection. Assuming you are running MySQL */

  
if (!$link = mysql_connect('localhost', 'MYSQL_DB_User', 'DB_Password')) {
    echo 'Could not connect to mysql';
    exit;
}

if (!mysql_select_db('om77_wbb', $link)) {
    echo 'Could not select database';
    exit;
}

// Display in utf8
mysql_query("SET NAMES 'utf8'", $link);

// Find all html codes for symbols
$sql    = 'SELECT userid,email FROM wcf1_user';

$result = mysql_query($sql, $link);

if (!$result) {
    echo "DB Error, could not query the database\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

// Go through the results
while ($row = mysql_fetch_assoc($result)) {
    echo $row[userid] . ' ' . $row['email'] .'<br>';
 
	
	 // Replace html codes	
 
	$string=mysql_real_escape_string($row[email]);
	 
	// Build the query used to update the record
	$sql = "UPDATE `user` SET `userid` = '$row[userid]' WHERE email = '$string';";
    $retval = mysql_query( $sql, $link );

	if(! $retval )
	{
	  die('Could not update data: ' . mysql_error());
	}
	echo "Updated data successfully\n for " . $row[userid] . "<br>";
	    
}

mysql_free_result($result);
	 
    // Close connection

    mysqli_close($link);

?>

 

Download script

---

# Script 2

<?php
/* PHP script written by W. Al Maawali  
# (c) 2016 Founder of Eagle Eye Digital Solutions
# http://www.digi77.com
# http://www.om77.net
# script starts here:*/

/* Attempt MySQL server connection. Assuming you are running MySQL */
    
if (!$link = mysql_connect('localhost', 'MYSQL_DB_User', 'DB_Password')) {
    echo 'Could not connect to mysql';
    exit;
}

if (!mysql_select_db('om77_wbb', $link)) {
    echo 'Could not select database';
    exit;
}

// Display in utf8
mysql_query("SET NAMES 'utf8'", $link);

// Find all html codes for symbols
$sql    = 'SELECT userid,userOption2 FROM wcf1_user_option_value';

$result = mysql_query($sql, $link);

if (!$result) {
    echo "DB Error, could not query the database\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

// Go through the results
while ($row = mysql_fetch_assoc($result)) 
{
    echo 'Old date format ' . $row[userOption2] .  '<br>';
	
 
	$pieces = explode("-", $row[userOption2] );
	if ( $row[userOption2]!="0000-00-00") 
	{
		$new_date =$pieces[2]. '-' . $pieces[0] . '-' . $pieces[1] ;  
	}
	else
	{
		$new_date =$row[userOption2];
	}	

	echo 'New date format ' . $new_date .  '<br>';
	
	$string=mysql_real_escape_string($row[email]);
	 
	// Build the query used to update the record
	$sql = "UPDATE `wcf1_user_option_value` SET `userOption2` = '$new_date' WHERE userid = '$row[userid]';";
    $retval = mysql_query( $sql, $link );

	if(! $retval )
	{
	  die('Could not update data: ' . mysql_error());
	}
	//echo "Updated data successfully\n for " . $row[userid] . "<br>";
	//echo "Updated data successfully\n for " . $row[postid] . "<br>";
    // To test 1 row only activate uncomment exit	
	//exit;
        
}

mysql_free_result($result);
	 
    // Close connection
    mysqli_close($link);

?>

 

Download script

---

Many Blog and Posts titles had symbols which were not decoded properly with this script I solve the issue:

<?php
/* PHP script written by W. Al Maawali  
# (c) 2016 Founder of Eagle Eye Digital Solutions
# http://www.digi77.com
# http://www.om77.net
# script starts here:*/

/* Attempt MySQL server connection. Assuming you are running MySQL */
    
if (!$link = mysql_connect('localhost', 'MYSQL_DB_User', 'DB_Password')) {
    echo 'Could not connect to mysql';
    exit;
}

if (!mysql_select_db('om77_wbb_forum', $link)) {
    echo 'Could not select database';
    exit;
}

// Display in utf8
mysql_query("SET NAMES 'utf8'", $link);

// Find all html codes for symbols
$sql    = 'SELECT postid,subject FROM wbb1_post WHERE subject REGEXP \'.*&#[0-9]+\'';

$result = mysql_query($sql, $link);

if (!$result) {
    echo "DB Error, could not query the database\n";
    echo 'MySQL Error: ' . mysql_error();
    exit;
}

// Go through the results
while ($row = mysql_fetch_assoc($result)) {
    
 
	
	 // Replace html codes	
	$string = html_entity_decode($row[subject], ENT_COMPAT, 'UTF-8');
	$string=mysql_real_escape_string($string);
	 
	// Build the query used to update the record
	$sql = "UPDATE `wbb1_post` SET `subject` = "$string" WHERE `postid` = '$row[postid]';";
    $retval = mysql_query( $sql, $link );

	if(! $retval )
	{
	  die('Could not update data: ' . mysql_error());
	}
	echo "Updated data successfully\n for " . $string . "<br>";
	   
}

mysql_free_result($result);
	 
    // Close connection

    mysqli_close($link);

?>

 

Download script

---

 

Resources:

• WBB 4.0 user Manual
• WoltLab community framework technical documentation.
• WBB plugins.
• Extra paid styles and plugins from CLS.
• Extra WBB user tips here.
• Extra WBB developer tips here.
• WBB source on Github.
• WBB plugins development forum here.
• WBB plugins development resources 1 – 2 – 3.

 

---

 

Copyright protected by Digiprove © 2016-2022 Eagle Eye Digital Solutions