Migrating from vBulletin to WoltLab Burning Board

# First take a backup of your database in my case Vbulletin stored data in latin1_swedish_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 latin1_swedish_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 quitting 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 because 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 quit and enter again the CLI I will add below 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 htaccess to avoid session table locks in mysql.

# Keep watching phpmyadmin for status and what job is being 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 get a warning after running 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

# 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 height 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`,'&#1103;','я');
UPDATE blog1_entry SET `message`=REPLACE(`message`,'&#128077;','♥'); 
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 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 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

<?php
/* PHP script written by Warith AL Maawali  
# (c) 2016 Warith AL Maawali
# 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
/* PHP script written by Warith AL Maawali  
# (c) 2016 Warith AL Maawali
# 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);

?>