Warith AL Maawali

0 %
Warith AL Maawali
Driving cybersecurity excellence
Innovator behind Linux Kodachi
  • Residence:
    127.0.0.1
  • Uptime Binary:
    101110
  • Mantra:
    Innovate, Secure, Repeat
ONS
EEDS
NSSG
Visual basic
Gambas
PHP
Delphi
Bash
  • Cybersecurity
  • Generative AI
  • Cloud & Automation
  • Cryptocurrency

Migrating from Vbulletin to Burning board

27/03/2016

Transitioning to Woltlab Burning Board: My Journey from Vbulletin

I established my first online community forum, oman0.net, in August 2001. Initially, the forum was based on Vbulletin 2 software, which I later upgraded to Vbulletin 3, and eventually settled with Vbulletin 4. During this period, I was quite satisfied with the performance and features of Vbulletin forums. Naturally, I was eager to test Vbulletin 5. However, I typically wait for user reviews before making a significant update. In this case, I held off on upgrading from Vbulletin 4 to Vbulletin 5 due to consistently unfavorable reviews regarding features and security over almost three years. It became clear that Vbulletin 5 did not meet my needs, prompting me to seek an alternative.

My first approach was to try XenForo, a commercial Internet forum software package written in PHP using the Zend Framework, developed by former Vbulletin lead developers Kier Darby and Mike Sullivan. After installing XenForo, I was pleased with its performance and decided to check user reviews. Many users compared it with Woltlab Burning Board (WBB), another commercial Internet forum software package written in PHP by the Germany-based company WoltLab.

Intrigued by the comparisons, I decided to give Burning Board a shot. Both XenForo and Burning Board were excellent, significantly outperforming Vbulletin. However, Burning Board stood out with its advanced features and mature coding. After two months of continuous testing on a simulated board, I decided to migrate my 14-year-old forum, with its 160k+ members, to Burning Board.

The migration process was challenging, involving numerous tough decisions and obstacles. I am writing this blog as a reference for anyone looking to move to Burning Board and in need of a comprehensive guide. I have been officially using WBB since October 20, 2015, and you can see it live on my other site, om77.net. I am extremely happy with my choice. Additionally, the cooperative and friendly support team behind WBB ensures that any issues are promptly resolved.

Blog Index:

Migration steps:  

ShellScript
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 for me due to the large size of my forum. If you have a large board, it is recommended to use SSH combined with CLI, along with the screen command to maintain the session even if you get disconnected. Additionally, I had to clear the private message and visitor message tables because they were huge and mostly filled with spam. If you intend to do the same, you should empty the following tables in your vBulletin database: 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
 

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
ShellScript

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

vintage-racing-car
vintage-racing-car

Tweaks and Fixes:


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

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

To troubleshoot thread and post 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 that do not have thumbnails 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 the 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 the 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 given that you have primary and foreign keys 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 problems 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`,'ي','');
UPDATE wbb1_post SET `subject`=REPLACE(`subject`,'ا','');

Here are some useful codes to be used in templates that 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)}
  

{/if}
{/if} 

To use the login authentication function on another script:

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
ShellScript

Custom scripts:

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

PHP
<?php
/* PHP script written by W. Al Maawali  
# (c) 2016 Founder of Eagle Eye Digital Solutions
# https://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);

?>
PHP
Download script

# Script 2

PHP
<?php
/* PHP script written by W. Al Maawali  
# (c) 2016 Founder of Eagle Eye Digital Solutions
# https://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);

?>
PHP
Download script

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

PHP
<?php
/* PHP script written by W. Al Maawali  
# (c) 2016 Founder of Eagle Eye Digital Solutions
# https://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);

?>
PHP
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 123.

 


 

Posted in Tech Blog
© 2024 Warith AL Maawali. All Rights Reserved.
Stay Secure, Stay Assured.