vBulletin Big Board Importer [vBulletin 3 + vBulletin 4] [Paid] 1.5.0

fotografik · Apr 26, 2018

Ludachris said:
Somewhere along the line my vb3 thread rewrites stopped working - when you click on them they result in Server 500 errors. Would I be able to get someone to take a look at a post on my site and recommend the htaccess rewrite code and where this code should reside in the file and in the directories? I'm running XF 1.5 if that matters. Here's an example post:

http://www.dsmtuners.com/threads/wire-tuck-how-tos.414500/#post-152729896

Would really appreciate it.

It looks like you had VBSEO installed at some point in time (or some SEO plugin). We also used to have VBSEO and had old URLs in the same format.

Are you using Nginx or Apache? I can try to help with Nginx rewrite rules. Here is a version that is running on our forums. This rewrite accommodates the forum description in the middle. There are a couple of "lazy" captures in there but the essential capture is the numerical bit - ([0-9]+)

Code:

location ~* /forums/(.*)/([0-9]+)-(.*)\.html$ {
        return 301 /index.php?threads/$2/;
}

We have a redirect mapping file with 200+ rules because we had VBSEO, VBulletin fancy URLs and then finally Vbulletin standard URLs on our site.

Ludachris · Apr 26, 2018

Yes, we had VBSEO running before we migrated. All the rewrites were working for years. It seems that just recently they stopped but I have no way of figuring out what change caused it. Maybe it was due to an add-on or an upgrade... we're using Apache.

Thanks for that code I'll give it a try.

Ludachris · Apr 26, 2018

Sorry, I realized this is the wrong thread for redirect discussion. I'll post it in the correct thread.

fotografik · Apr 27, 2018

Ludachris said:
Yes, we had VBSEO running before we migrated. All the rewrites were working for years. It seems that just recently they stopped but I have no way of figuring out what change caused it. Maybe it was due to an add-on or an upgrade... we're using Apache.

Thanks for that code I'll give it a try.

Ah, do note that the code snippet I gave is for Nginx and is not going to work in Apache.

Ludachris · Apr 27, 2018

fotografik said:
Ah, do note that the code snippet I gave is for Nginx and is not going to work in Apache.

Yeah, I figured as much. I think there's something else causing this, as I still have the rewrite code in there.

mazdas247 · May 2, 2018

Hey guys, I don't mean to complain, but I feel I need to point out the Export script has several big issues and makes glaring assumptions as to the environment. I am patching these things, progressively as I go though this process, the more I use it the more problems I see. If there is any interest I could contribute all the fixes at the end of the process.

mazdas247 · May 2, 2018

Do I need to worry about this message I see when I run Export.php?

!!!WARNING!!! - set charset to 'binary' in XenForo_Application:loadDb()

Slavik · May 2, 2018

mazdas247 said:
Do I need to worry about this message I see when I run Export.php?

!!!WARNING!!! - set charset to 'binary' in XenForo_Application:loadDb()

Nope

mazdas247 · May 4, 2018

@Slavik here is what I found so far...

Export uses $this->db, which is the XF database, to try to access VB4 tables. This assumes that the XF DB server is the same as the VB, and that it is the same user. Basically, it ignores $this->slaveDbHost, and slaveDbUser, etc... I patched the code to use mysqli to create connection for the VB4 DB, and re-use it throughout the code. Also patched to use the mysqli version of the APIs, not whatever XF uses. I had to repeat this for users, polls, and attachments.
The VB4 attachment import script assumes that userid in attachment table is the same as that of the matching record in filedata table. Most of the time it is, but in my case, it does not always match and the script was thus skipping ALOT of attachments (MISSING DATA). Now all failed attachment import are legit (0 size file on disk)..

ivp · Oct 20, 2018

1) @Slavik vBulletin 4.2.5 started using table 'ipdata' containing IP addresses referenced from some tables, such as: 'userchangelog', 'blog_text', 'groupmessage', 'picturecomment', 'visitormessage'.

Here is the fix:

Code:

        echo "          user account confirmation IPs";
        //exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SET @ip_id = ' . $existingIpCount . ';SELECT SQL_NO_CACHE @ip_id:=@ip_id+1 AS ip_id, userchangelog.userid AS user_id, \'user\' AS content_type, userchangelog.userid AS content_id, \'account-confirmation\' AS action, ipaddress AS ip, userchangelog.change_time AS log_date FROM ' . self::$tablePrefix . 'userchangelog as userchangelog WHERE fieldname = \'usergroupid\' AND oldvalue = \'3\' AND ipaddress > 0 AND userid IN(0, adminid) GROUP BY userchangelog.userid ORDER BY userchangelog.userid" ' . self::$extraOutCommand . ' >> ' . self::$dataDir . 'xf_ip.txt');
        //ipdata fix
        exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SET @ip_id = ' . $existingIpCount . ';SELECT SQL_NO_CACHE @ip_id:=@ip_id+1 AS ip_id, userchangelog.userid AS user_id, \'user\' AS content_type, userchangelog.userid AS content_id, \'account-confirmation\' AS action, IF(ISNULL(ipdata.ip), userchangelog.ipaddress, INET_ATON(ipdata.ip)) AS ip, userchangelog.change_time AS log_date FROM ' . self::$tablePrefix . 'userchangelog as userchangelog LEFT JOIN ' . self::$tablePrefix . 'ipdata AS ipdata ON userchangelog.ipaddress=ipdata.ipid WHERE fieldname = \'usergroupid\' AND oldvalue = \'3\' AND ipaddress > 0 AND userchangelog.userid IN(0, adminid) GROUP BY userchangelog.userid ORDER BY userchangelog.userid" ' . self::$extraOutCommand . ' >> ' . self::$dataDir . 'xf_ip.txt');
        echo " (" . number_format(intval(shell_exec('wc -l ' . self::$dataDir . 'xf_ip.txt')) - $existingIpCount) . ' records in ' .  number_format(microtime(true) - $start, 2) . "s)...\r\n";

Code:

        echo "          user change log IPs";
        //exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SET @ip_id = ' . $existingIpCount . ';SELECT SQL_NO_CACHE @ip_id:=@ip_id+1 AS ip_id, userchangelog.adminid AS user_id, \'user\' AS content_type, userchangelog.userid AS content_id, \'change\' AS action, ipaddress AS ip, userchangelog.change_time AS log_date FROM ' . self::$tablePrefix . 'userchangelog as userchangelog WHERE userchangelog.ipaddress > 0 ORDER BY userchangelog.changeid" ' . self::$extraOutCommand . ' >> ' . self::$dataDir . 'xf_ip.txt');
        //ipdata fix
        exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SET @ip_id = ' . $existingIpCount . ';SELECT SQL_NO_CACHE @ip_id:=@ip_id+1 AS ip_id, userchangelog.adminid AS user_id, \'user\' AS content_type, userchangelog.userid AS content_id, \'change\' AS action, IF(ISNULL(ipdata.ip), userchangelog.ipaddress, INET_ATON(ipdata.ip)) AS ip, userchangelog.change_time AS log_date FROM ' . self::$tablePrefix . 'userchangelog as userchangelog LEFT JOIN ' . self::$tablePrefix . 'ipdata AS ipdata ON userchangelog.ipaddress=ipdata.ipid WHERE userchangelog.ipaddress > 0 ORDER BY userchangelog.changeid" ' . self::$extraOutCommand . ' >> ' . self::$dataDir . 'xf_ip.txt');
        echo " (" . number_format(intval(shell_exec('wc -l ' . self::$dataDir . 'xf_ip.txt')) - $existingIpCount) . ' records in ' .  number_format(microtime(true) - $start, 2) . "s)...\r\n";

Code:

        echo "          profile post IPs";
        //exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SET @ip_id = ' . $existingIpCount . ';SELECT SQL_NO_CACHE @ip_id:=@ip_id+1 AS ip_id, visitormessage.postuserid AS user_id, \'profile_post\' AS content_type, visitormessage.vmid AS content_id, \'insert\' AS action, ipaddress AS ip, visitormessage.dateline AS log_date FROM ' . self::$tablePrefix . 'visitormessage as visitormessage ORDER BY visitormessage.vmid" ' . self::$extraOutCommand . ' >> ' . self::$dataDir . 'xf_ip.txt');
        //ipdata fix
        exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SET @ip_id = ' . $existingIpCount . ';SELECT SQL_NO_CACHE @ip_id:=@ip_id+1 AS ip_id, visitormessage.postuserid AS user_id, \'profile_post\' AS content_type, visitormessage.vmid AS content_id, \'insert\' AS action, IF(ISNULL(ipdata.ip), visitormessage.ipaddress, INET_ATON(ipdata.ip)) AS ip, visitormessage.dateline AS log_date FROM ' . self::$tablePrefix . 'visitormessage as visitormessage LEFT JOIN ' . self::$tablePrefix . 'ipdata AS ipdata ON visitormessage.ipaddress=ipdata.ipid ORDER BY visitormessage.vmid" ' . self::$extraOutCommand . ' >> ' . self::$dataDir . 'xf_ip.txt');      
        echo " (" . number_format(intval(shell_exec('wc -l ' . self::$dataDir . 'xf_ip.txt')) - $existingIpCount) . ' records in ' .  number_format(microtime(true) - $start, 2) . "s)...\r\n";

2) Got hundreds of "Warning 1062 Duplicate entry '398588' for key 'PRIMARY'" when importing xf_ip. Any hint what to do about this?

3) Got dozens of "Warning 1265 Data truncated for column 'username' at row 21284" while processing xf_profile_post. It seems to be related to HTML entities in usernames. What to do about this? If we make changes to vBulletin user table, such users wouldn't be able to login?

4) Admin home/Member Statistics show much smaller number of Total Members than vBulletin, even after user cache is rebuilt?

5) Noticed commented out export to files 'dp_user_change_log.txt', 'dp_edit_history.txt', 'dp_node_watch.txt'. Any way to import that stuff, too?

ivp · Oct 21, 2018

Patch to import user change logs:

Add the following to Export.php, below commented code for dp_user_change_log.txt:

Code:

// userchangelog
$start = microtime(true);
echo "          user change log";
$fp_user_change = fopen('' . self::$dataDir . 'xf_user_change_log.txt', 'w'); 
$user_changes = $this->db->fetchAll('
        SELECT SQL_NO_CACHE userchangelog.changeid AS log_id, userchangelog.userid AS user_id, userchangelog.adminid AS edit_user_id,
        userchangelog.change_time AS edit_date,
        REPLACE(REPLACE(userchangelog.fieldname, \'membergroupids\', \'secondary_group_ids\'), \'usergroupid\', \'user_group_id\') AS field,
        userchangelog.oldvalue AS old_value, userchangelog.newvalue AS new_value, \'0\' as protected FROM ' . $this->sourceDb . '.' . self::$tablePrefix . 'userchangelog as userchangelog
        ORDER BY userchangelog.changeid');
foreach ($user_changes as &$change) {
        if($change['field']=='user_group_id' || $change['field']=='secondary_group_ids') {
                $change['old_value'] = self::$groupMap[$change['old_value']];
                $change['new_value'] = self::$groupMap[$change['new_value']];
        }
        fwrite($fp_user_change, "$change[log_id]\t$change[user_id]\t$change[edit_user_id]\t$change[edit_date]\t$change[field]\t$change[old_value]\t$change[new_value]\t$change[protected]\n");
}
fclose($fp_user_change);
unset ($user_changes);   
echo " (" . number_format(intval(shell_exec('wc -l ' . self::$dataDir . 'xf_user_change_log.txt'))) . ' records in ' .  number_format(microtime(true) - $start, 2) . "s)...\r\n";

Add the following to Import.php, below importTable('xf_ip'):

Code:

importTable('xf_user_change_log');

ivp · Oct 22, 2018

mazdas247 said:
The VB4 attachment import script assumes that userid in attachment table is the same as that of the matching record in filedata table. Most of the time it is, but in my case, it does not always match and the script was thus skipping ALOT of attachments (MISSING DATA). Now all failed attachment import are legit (0 size file on disk)..

Here is the patch for bunch of MISSING DATA when importing attachments:

Instead of:

Code:

$attachments = $this->db->fetchAll('
    SELECT attachment.attachmentid AS attachment_id, attachment.userid AS user_id, ' . (self::$isVB4 ? 'contentid' : 'postid') . ' AS content_id, attachment.dateline AS attachment_date, ' . (self::$isVB4 ? 'filedata.filedata' : 'attachment.filedata') . ' AS file, ' . (self::$isVB4 ? 'filedata.thumbnail_filesize' : 'attachment.thumbnail_filesize') . ', attachment.filename, ' . (self::$isVB4 ? 'filedata.extension' : 'attachment.extension') . ' AS extension, ' . (self::$isVB4 ? 'filedata.filedataid' : 'attachment.attachmentid') . ' AS filesysid
    FROM ' . $this->sourceDb . '.' . self::$tablePrefix . 'attachment as attachment
        ' . (self::$isVB4 ? 'LEFT JOIN ' . $this->sourceDb . '.' . self::$tablePrefix . 'filedata AS filedata ON (filedata.filedataid = attachment.filedataid)' : '') . '
    WHERE attachment.attachmentid BETWEEN ' . intval($start) . ' AND ' . intval($start + 999) . (self::$isVB4 ? ' AND attachment.contenttypeid = 1' : '') . '
    ' . (self::$isVB4 ? 'GROUP BY attachment.filedataid' : '') . '
    ORDER BY attachment.attachmentid
')

use this:

Code:

$attachments = $this->db->fetchAll('
    SELECT attachment.attachmentid AS attachment_id, attachment.userid AS user_id, ' . (self::$isVB4 ? 'contentid' : 'postid') . ' AS content_id, attachment.dateline AS attachment_date, ' . (self::$isVB4 ? 'filedata.filedata' : 'attachment.filedata') . ' AS file, ' . (self::$isVB4 ? 'filedata.thumbnail_filesize' : 'attachment.thumbnail_filesize') . ', attachment.filename, ' . (self::$isVB4 ? 'filedata.extension' : 'attachment.extension') . ' AS extension, ' . (self::$isVB4 ? 'filedata.filedataid' : 'attachment.attachmentid') . ' AS filesysid, filedata.userid AS filedata_user_id
    FROM ' . $this->sourceDb . '.' . self::$tablePrefix . 'attachment as attachment
        ' . (self::$isVB4 ? 'LEFT JOIN ' . $this->sourceDb . '.' . self::$tablePrefix . 'filedata AS filedata ON (filedata.filedataid = attachment.filedataid)' : '') . '
    WHERE attachment.attachmentid BETWEEN ' . intval($start) . ' AND ' . intval($start + 999) . (self::$isVB4 ? ' AND attachment.contenttypeid = 1' : '') . '
    ' . (self::$isVB4 ? 'GROUP BY attachment.filedataid' : '') . '
    ORDER BY attachment.attachmentid
')

Instead of:

Code:

if (self::$attachFile AND file_exists(self::$attachFile . implode('/', preg_split('//', $attachment['user_id'],  -1, PREG_SPLIT_NO_EMPTY)) . '/' . strval($attachment['filesysid']) . '.attach'))
{
    $attachment['file'] = file_get_contents(self::$attachFile . implode('/', preg_split('//', $attachment['user_id'],  -1, PREG_SPLIT_NO_EMPTY)) . '/' . strval($attachment['filesysid']) . '.attach');
}
else
{
    $attachment['file'] = $attachment['file'];
}

use this:

Code:

if (self::$attachFile AND file_exists(self::$attachFile . implode('/', preg_split('//', $attachment['user_id'],  -1, PREG_SPLIT_NO_EMPTY)) . '/' . strval($attachment['filesysid']) . '.attach'))
{
    $attachment['file'] = file_get_contents(self::$attachFile . implode('/', preg_split('//', $attachment['user_id'],  -1, PREG_SPLIT_NO_EMPTY)) . '/' . strval($attachment['filesysid']) . '.attach');
}
else
// If doesn't exist try filedata user id 
if (self::$attachFile AND file_exists(self::$attachFile . implode('/', preg_split('//', $attachment['filedata_user_id'],  -1, PREG_SPLIT_NO_EMPTY)) . '/' . strval($attachment['filesysid']) . '.attach'))                
{
    $attachment['file'] = file_get_contents(self::$attachFile . implode('/', preg_split('//', $attachment['filedata_user_id'],  -1, PREG_SPLIT_NO_EMPTY)) . '/' . strval($attachment['filesysid']) . '.attach');                
}
else
{
    $attachment['file'] = $attachment['file'];
}

ivp · Oct 23, 2018

Patch for proper import of polls, since table xf_poll order of columns is changed in XenForo 1.5.22

Instead of:

Code:

fwrite($fp, "$poll[poll_id]    $poll[content_type]    $poll[content_id]    $poll[question]    $poll[responses]    $poll[voter_count]    $poll[public_votes]    $poll[close_date]\n");

use:

Code:

fwrite($fp, "$poll[poll_id]\t$poll[content_type]\t$poll[content_id]\t$poll[question]\t$poll[responses]\t$poll[voter_count]\t$poll[public_votes]\t0\t$poll[close_date]\t0\t0\n");

ivp · Oct 25, 2018

Fix "Row xxx doesn't contain data for all columns" message when importing xf_conversation_message, xf_conversation_user and xf_thread, xf_user, xf_user_ban, xf_user_option in XenForo 1.5.22

Instead of:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SET @ip_id = ' . $existingIpCount . ';SELECT SQL_NO_CACHE pmtext.pmtextid AS message_id, pmtext.pmtextid AS conversation_id, pmtext.dateline AS message_date, pmtext.fromuserid AS user_id, pmtext.fromusername AS username, pmtext.message, 0 AS attach_count FROM ' . self::$tablePrefix . 'pmtext as pmtext ORDER BY pmtext.pmtextid"' . $this->_getReplaceBBCodeCommand() . ' ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_conversation_message.txt');

use:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SET @ip_id = ' . $existingIpCount . ';SELECT SQL_NO_CACHE pmtext.pmtextid AS message_id, pmtext.pmtextid AS conversation_id, pmtext.dateline AS message_date, pmtext.fromuserid AS user_id, pmtext.fromusername AS username, pmtext.message, 0 AS attach_count, 0 AS ip_id FROM ' . self::$tablePrefix . 'pmtext as pmtext ORDER BY pmtext.pmtextid"' . $this->_getReplaceBBCodeCommand() . ' ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_conversation_message.txt');

Instead of:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE pm.pmtextid AS conversation_id, userid AS owner_user_id, IF(pm.messageread > 0, 0, 1) AS is_unread, 0 AS reply_count, pmtext.dateline AS last_message_date, pm.pmtextid AS last_message_id, pmtext.fromuserid AS last_message_user_id, pmtext.fromusername AS last_message_username FROM ' . self::$tablePrefix . 'pm as pm LEFT JOIN ' . self::$tablePrefix . 'pmtext as pmtext ON (pmtext.pmtextid = pm.pmtextid) ORDER BY pm.pmtextid" ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_conversation_user.txt');

use:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE pm.pmtextid AS conversation_id, userid AS owner_user_id, IF(pm.messageread > 0, 0, 1) AS is_unread, 0 AS reply_count, pmtext.dateline AS last_message_date, pm.pmtextid AS last_message_id, pmtext.fromuserid AS last_message_user_id, pmtext.fromusername AS last_message_username, 0 AS is_starred FROM ' . self::$tablePrefix . 'pm as pm LEFT JOIN ' . self::$tablePrefix . 'pmtext as pmtext ON (pmtext.pmtextid = pm.pmtextid) ORDER BY pm.pmtextid" ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_conversation_user.txt');

Instead of:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE pmtext.pmtextid AS conversation_id, pmtext.fromuserid AS owner_user_id, 0 AS is_unread, 0 AS reply_count, pmtext.dateline AS last_message_date, pmtext.pmtextid AS last_message_id, pmtext.fromuserid AS last_message_user_id, pmtext.fromusername AS last_message_username FROM ' . self::$tablePrefix . 'pmtext as pmtext LEFT JOIN ' . self::$tablePrefix . 'pm as pm ON (pm.pmtextid = pmtext.pmtextid AND pm.userid = pmtext.fromuserid) WHERE pm.userid IS NULL" ' . self::$extraOutCommand . ' >> ' . self::$dataDir . 'xf_conversation_user.txt');

use:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE pmtext.pmtextid AS conversation_id, pmtext.fromuserid AS owner_user_id, 0 AS is_unread, 0 AS reply_count, pmtext.dateline AS last_message_date, pmtext.pmtextid AS last_message_id, pmtext.fromuserid AS last_message_user_id, pmtext.fromusername AS last_message_username, 0 AS is_starred FROM ' . self::$tablePrefix . 'pmtext as pmtext LEFT JOIN ' . self::$tablePrefix . 'pm as pm ON (pm.pmtextid = pmtext.pmtextid AND pm.userid = pmtext.fromuserid) WHERE pm.userid IS NULL" ' . self::$extraOutCommand . ' >> ' . self::$dataDir . 'xf_conversation_user.txt');

Instead of:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT thread.threadid AS thread_id, thread.forumid AS node_id, REPLACE(REPLACE(REPLACE(REPLACE(thread.title, \'&amp;\', \'&\'), \'&lt;\', \'<\'), \'&gt;\', \'>\'), \'&quot;\', \'\\"\') AS title, thread.replycount AS reply_count, thread.views AS view_count, thread.postuserid AS user_id, thread.postusername AS username, thread.dateline AS post_date, thread.sticky, IF(thread.visible = 0, \'moderated\', IF(thread.visible = 2, \'deleted\', \'visible\')) AS discussion_state, thread.open AS disucssion_open, IF(thread.pollid > 0, \'poll\', \'\') AS discussion_type, thread.firstpostid AS first_post_id, (SELECT COUNT(*) FROM ' . self::$tablePrefix . 'reputation as reputation WHERE reputation.postid = thread.firstpostid AND (reputation > 0 OR (reason = \'\' AND reputation = 0))) AS first_post_likes, thread.lastpost AS last_post_date, thread.lastpostid AS last_post_id, ' . (self::$isVB4 ? 'thread.lastposterid' : "'0'") . ' AS last_post_user_id, thread.lastposter AS last_post_username, \'0\' AS prefix_id FROM ' . self::$tablePrefix . 'thread as thread ORDER BY thread.threadid" | ' . self::$sedCommand . ' -r \'s/\&\#[0-9]*?\;//ig\' ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_thread.txt');

use:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT thread.threadid AS thread_id, thread.forumid AS node_id, REPLACE(REPLACE(REPLACE(REPLACE(thread.title, \'&amp;\', \'&\'), \'&lt;\', \'<\'), \'&gt;\', \'>\'), \'&quot;\', \'\\"\') AS title, thread.replycount AS reply_count, thread.views AS view_count, thread.postuserid AS user_id, thread.postusername AS username, thread.dateline AS post_date, thread.sticky, IF(thread.visible = 0, \'moderated\', IF(thread.visible = 2, \'deleted\', \'visible\')) AS discussion_state, thread.open AS disucssion_open, IF(thread.pollid > 0, \'poll\', \'\') AS discussion_type, thread.firstpostid AS first_post_id, (SELECT COUNT(*) FROM ' . self::$tablePrefix . 'reputation as reputation WHERE reputation.postid = thread.firstpostid AND (reputation > 0 OR (reason = \'\' AND reputation = 0))) AS first_post_likes, thread.lastpost AS last_post_date, thread.lastpostid AS last_post_id, ' . (self::$isVB4 ? 'thread.lastposterid' : "'0'") . ' AS last_post_user_id, thread.lastposter AS last_post_username, \'0\' AS prefix_id, \'\' AS tags FROM ' . self::$tablePrefix . 'thread as thread ORDER BY thread.threadid" | ' . self::$sedCommand . ' -r \'s/\&\#[0-9]*?\;//ig\' ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_thread.txt');

Instead of:

Code:

fwrite($fp_user, str_replace('\\', '\\\\', "$user[userid]    " . html_entity_decode($user['username']) . "    $user[email]        " . ($user['customtitle'] ? html_entity_decode($user['usertitle']) : '') . "    1    0    " . $importModel->resolveTimeZoneOffset($user['timezoneoffset'], $user['options'] & 64) . "    1    1    $user[usergroupid]    $user[membergroupids]    $userGroupDisplay    $permissionComboId    $user[posts]    $user[pmunread]    $user[joindate]    $user[lastactivity]    0    0    $user[avatar_date]    192    192        " . ($user['usergroupid'] == 1 ? 'email_confirm' : 'valid') . "    " . ($user['usergroupid'] == 4 ? '1' : '0') . "    " . ($user['usergroupid'] == 3 ? '1' : '0') . "    " . ($is_banned ? 1 : 0) . "    $user[like_count]    $user[ipoints]    " . (($user['usergroupid'] == 4 OR $user['usergroupid'] == 3) ? '1' : '0')) . "\n");

use:

Code:

fwrite($fp_user, str_replace('\\', '\\\\', "$user[userid]    " . html_entity_decode($user['username']) . "    $user[email]        " . ($user['customtitle'] ? html_entity_decode($user['usertitle']) : '') . "    1    0    " . $importModel->resolveTimeZoneOffset($user['timezoneoffset'], $user['options'] & 64) . "    1    1    $user[usergroupid]    $user[membergroupids]    $userGroupDisplay    $permissionComboId    $user[posts]    $user[pmunread]    $user[joindate]    $user[lastactivity]    0    0    $user[avatar_date]    192    192        " . ($user['usergroupid'] == 1 ? 'email_confirm' : 'valid') . "    " . ($user['usergroupid'] == 4 ? '1' : '0') . "    " . ($user['usergroupid'] == 3 ? '1' : '0') . "    " . ($is_banned ? 1 : 0) . "    $user[like_count]    $user[ipoints]    " . (($user['usergroupid'] == 4 OR $user['usergroupid'] == 3) ? '1' : '0')) . "\t0\t0\n");

Instead of:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE user.userid, COALESCE(userban.adminid, 0) AS ban_user_id, COALESCE(userban.bandate, user.lastactivity) AS ban_date, COALESCE((SELECT MAX(expires) FROM ' . self::$tablePrefix . 'infraction as infraction WHERE userid = user.userid AND expires > 0 AND actiondateline = 0), (SELECT expires FROM ' . self::$tablePrefix . 'infraction as infraction WHERE expires > ' . time() . ' AND userid = user.userid ORDER BY expires DESC LIMIT 1), userban.liftdate, 0) AS end_date, COALESCE(userban.reason, \'Rules violations\') AS user_reason FROM ' . self::$tablePrefix . 'user as user LEFT JOIN ' . self::$tablePrefix . 'userban as userban ON (userban.userid = user.userid) WHERE user.usergroupid = ' . self::$bannedGroupVB . ' OR user.infractiongroupid = ' . self::$bannedGroupVB . ' ORDER BY user.userid" ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_user_ban.txt');

use:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE user.userid, COALESCE(userban.adminid, 0) AS ban_user_id, COALESCE(userban.bandate, user.lastactivity) AS ban_date, COALESCE((SELECT MAX(expires) FROM ' . self::$tablePrefix . 'infraction as infraction WHERE userid = user.userid AND expires > 0 AND actiondateline = 0), (SELECT expires FROM ' . self::$tablePrefix . 'infraction as infraction WHERE expires > ' . time() . ' AND userid = user.userid ORDER BY expires DESC LIMIT 1), userban.liftdate, 0) AS end_date, COALESCE(userban.reason, \'Rules violations\') AS user_reason, 0 AS triggered FROM ' . self::$tablePrefix . 'user as user LEFT JOIN ' . self::$tablePrefix . 'userban as userban ON (userban.userid = user.userid) WHERE user.usergroupid = ' . self::$bannedGroupVB . ' OR user.infractiongroupid = ' . self::$bannedGroupVB . ' ORDER BY user.userid" ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_user_ban.txt');

Instead of:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE user.userid, IF(user.showbirthday = 1 OR user.showbirthday = 2, 1, 0) AS show_dob_year, IF(user.showbirthday = 2 OR user.showbirthday = 3, 1, 0) AS show_dob_date, IF(options & 1, 1, 0) AS content_show_signature, \'1\' AS receive_admin_email, IF(options & 4096, 1, 0) AS email_on_conversation, \'0\' AS is_discouraged, IF(user.autosubscribe = -1, \'\', IF(user.autosubscribe = 0, \'watch_no_email\', \'watch_email\')) AS default_watch_state, \'\' AS alert_optout, \'1\' AS enable_rte, \'1\' AS enable_flash_uploader FROM ' . self::$tablePrefix . 'user as user ORDER BY user.userid" ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_user_option.txt');

use:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE user.userid, IF(user.showbirthday = 1 OR user.showbirthday = 2, 1, 0) AS show_dob_year, IF(user.showbirthday = 2 OR user.showbirthday = 3, 1, 0) AS show_dob_date, IF(options & 1, 1, 0) AS content_show_signature, \'1\' AS receive_admin_email, IF(options & 4096, 1, 0) AS email_on_conversation, \'0\' AS is_discouraged, IF(user.autosubscribe = -1, \'\', IF(user.autosubscribe = 0, \'watch_no_email\', \'watch_email\')) AS default_watch_state, \'\' AS alert_optout, \'1\' AS enable_rte, \'1\' AS enable_flash_uploader, 0 AS use_tfa FROM ' . self::$tablePrefix . 'user as user ORDER BY user.userid" ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_user_option.txt');

ivp · Oct 25, 2018

Patch for proper import of xf_forum and xf_node, since order of columns changed in XenForo 1.5.22

Instead of:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE forum.forumid AS node_id, forum.threadcount AS discussion_count, forum.replycount AS message_count, forum.lastpostid AS last_post_id, forum.lastpost AS last_post_date, ' . (self::$isVB4 ? 'forum.lastposterid' : "'0'") . ' AS last_post_user_id, forum.lastposter AS last_post_username, forum.lastthread AS last_thread_title, IF(forum.options & 8, 1, 0) AS moderate_messages, IF(forum.options & 2, 1, 0) AS allow_posting, IF(forum.options & 4096, \'1\', \'0\') AS count_messages, \'1\' AS find_new, \'\' AS prefix_cache, \'0\' AS default_prefix_id, \'last_post_date\' AS default_sort_order, \'desc\' AS default_sort_direction, \'0\' AS require_prefix, \'thread\' AS allowed_watch_notifications FROM ' . self::$tablePrefix . 'forum as forum WHERE forum.parentid > 0 ORDER BY forum.forumid" ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_forum.txt');

use:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE forum.forumid AS node_id, forum.threadcount AS discussion_count, forum.replycount AS message_count, forum.lastpostid AS last_post_id, forum.lastpost AS last_post_date, ' . (self::$isVB4 ? 'forum.lastposterid' : "'0'") . ' AS last_post_user_id, forum.lastposter AS last_post_username, forum.lastthread AS last_thread_title, IF(forum.options & 8, 1, 0) AS moderate_threads, IF(forum.options & 8, 1, 0) AS moderate_replies, IF(forum.options & 2, 1, 0) AS allow_posting, IF(forum.options & 2, 1, 0) AS allow_poll, IF(forum.options & 4096, \'1\', \'0\') AS count_messages, \'1\' AS find_new, \'\' AS prefix_cache, \'0\' AS default_prefix_id, \'last_post_date\' AS default_sort_order, \'desc\' AS default_sort_direction, 0 AS list_date_limit_days, \'0\' AS require_prefix, \'thread\' AS allowed_watch_notifications, 0 as min_tags FROM ' . self::$tablePrefix . 'forum as forum WHERE forum.parentid > 0 ORDER BY forum.forumid" ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_forum.txt');

Instead of:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE forum.forumid AS node_id, REPLACE(forum.title, \'&amp;\', \'&\'), forum.description, IF(LENGTH(forum.link) > 0, SUBSTRING(forum.link, 51), \'\\\\\\N\') AS node_name, IF(forum.parentid = -1, \'Category\', IF(LENGTH(forum.link) > 0, \'LinkForum\', \'Forum\')) AS node_type_id, IF(forum.parentid = -1, 0, forum.parentid) AS parent_node_id, forum.displayorder AS display_order, IF(forum.options & 1, 1, 0) AS display_in_list, \'0\' AS lft, \'0\' AS rgt, \'\' AS depth, forum.styleid AS style_id, forum.styleid AS effective_style_id FROM ' . self::$tablePrefix . 'forum as forum ORDER BY forum.forumid" ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_node.txt');

use:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE forum.forumid AS node_id, REPLACE(forum.title, \'&amp;\', \'&\') AS title, forum.description AS description, IF(LENGTH(forum.link) > 0, SUBSTRING(forum.link, 51), \'\\\\\\N\') AS node_name, IF(forum.parentid = -1, \'Category\', IF(LENGTH(forum.link) > 0, \'LinkForum\', \'Forum\')) AS node_type_id, IF(forum.parentid = -1, 0, forum.parentid) AS parent_node_id, forum.displayorder AS display_order, IF(forum.options & 1, 1, 0) AS display_in_list, \'0\' AS lft, \'0\' AS rgt, 0 AS depth, forum.styleid AS style_id, forum.styleid AS effective_style_id, NULL AS breadcrumb_data FROM ' . self::$tablePrefix . 'forum as forum ORDER BY forum.forumid" ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_node.txt');

ivp · Oct 25, 2018

Table xf_user_profile changed structure in XenForo 1.5.22.

Instead of:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE user.userid AS user_id, ABS(SUBSTRING(birthday, 4, 2)) AS dob_day, ABS(SUBSTRING(birthday, 1, 2)) AS dob_month, ABS(SUBSTRING(birthday, 7, 4)) AS dob_year, \'\' AS status, 0 AS status_date, 0 AS status_profile_post_id, usertextfield.signature, user.homepage, REPLACE(REPLACE(REPLACE(REPLACE(userfield.field2, \'&amp;\', \'&\'), \'&lt;\', \'<\'), \'&gt;\', \'>\'), \'&quot;\', \'\\"\') AS location, REPLACE(REPLACE(REPLACE(REPLACE(userfield.field4, \'&amp;\', \'&\'), \'&lt;\', \'<\'), \'&gt;\', \'>\'), \'&quot;\', \'\\"\') AS occupation, COALESCE((SELECT GROUP_CONCAT(DISTINCT relationid SEPARATOR \',\') FROM ' . self::$tablePrefix . 'userlist as userlist WHERE userlist.userid = user.userid AND userlist.type = \'buddy\'), \'\') AS following, \'\' AS ignored, LOWER(SUBSTRING(PASSWORD(MD5(CONCAT(user.username, user.userid))), 2)) AS csrf_token, \'0\' AS avatar_crop_x, \'0\' AS avatar_crop_y, REPLACE(REPLACE(REPLACE(REPLACE(userfield.field1, \'&amp;\', \'&\'), \'&lt;\', \'<\'), \'&gt;\', \'>\'), \'&quot;\', \'\\"\') AS about, ' . (self::$isVB4 ? 'CAST(user.fbuserid AS UNSIGNED INTEGER)' : "'0'") . ' AS facebook_auth_id, \'\' AS custom_fields FROM ' . self::$tablePrefix . 'user as user LEFT JOIN ' . self::$tablePrefix . 'usertextfield as usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN ' . self::$tablePrefix . 'userfield as userfield ON (userfield.userid = user.userid) ORDER BY user.userid" ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_user_profile.txt');

use:

Code:

exec('mysql -h' . $this->slaveDbHost . ' ' . $this->sourceDb . ' ' . $this->sourceDBuser . ' ' . $this->sourceDBpassword . ' -N -q -e "SET NAMES binary;SELECT SQL_NO_CACHE user.userid AS user_id, ABS(SUBSTRING(birthday, 4, 2)) AS dob_day, ABS(SUBSTRING(birthday, 1, 2)) AS dob_month, ABS(SUBSTRING(birthday, 7, 4)) AS dob_year, \'\' AS status, 0 AS status_date, 0 AS status_profile_post_id, usertextfield.signature, user.homepage, REPLACE(REPLACE(REPLACE(REPLACE(userfield.field2, \'&amp;\', \'&\'), \'&lt;\', \'<\'), \'&gt;\', \'>\'), \'&quot;\', \'\\"\') AS location, REPLACE(REPLACE(REPLACE(REPLACE(userfield.field4, \'&amp;\', \'&\'), \'&lt;\', \'<\'), \'&gt;\', \'>\'), \'&quot;\', \'\\"\') AS occupation, COALESCE((SELECT GROUP_CONCAT(DISTINCT relationid SEPARATOR \',\') FROM ' . self::$tablePrefix . 'userlist as userlist WHERE userlist.userid = user.userid AND userlist.type = \'buddy\'), \'\') AS following, \'\' AS ignored, LOWER(SUBSTRING(PASSWORD(MD5(CONCAT(user.username, user.userid))), 2)) AS csrf_token, \'0\' AS avatar_crop_x, \'0\' AS avatar_crop_y, REPLACE(REPLACE(REPLACE(REPLACE(userfield.field1, \'&amp;\', \'&\'), \'&lt;\', \'<\'), \'&gt;\', \'>\'), \'&quot;\', \'\\"\') AS about, \'\' AS custom_fields, ' . (self::$isVB4 ? 'CAST(user.fbuserid AS UNSIGNED INTEGER)' : "'0'") . ' AS external_auth, UNIX_TIMESTAMP() AS password_date FROM ' . self::$tablePrefix . 'user as user LEFT JOIN ' . self::$tablePrefix . 'usertextfield as usertextfield ON (usertextfield.userid = user.userid) LEFT JOIN ' . self::$tablePrefix . 'userfield as userfield ON (userfield.userid = user.userid) ORDER BY user.userid" ' . self::$extraOutCommand . ' > ' . self::$dataDir . 'xf_user_profile.txt');

ivp · Oct 29, 2018

Merging arrays bugs

Find:

Code:

array($user['usergroupid']) + explode(',', $user['membergroupids'])

replace with:

Code:

array_unique(array_merge(array($user['usergroupid']), explode(',', $user['membergroupids'])))

Find:

Code:

array_slice($pollVote, 0, 3) + array('poll_response_id' => $i)

replace with:

Code:

array_merge(array_slice($pollVote, 0, 3), array('poll_response_id' => $i))

Find:

Code:

$contentTypeThread + $contentTypePost

replace with:

Code:

array_merge($contentTypeThread, $contentTypePost)

ivp · Nov 3, 2018

Jake Bunce said:
I can't be sure it's the same issue but...

I recently had this problem with a client's forum. I did some debugging and found that the auth upgrade system was corrupting the auth data somehow. As of XF 1.2 the auth system will attempt to convert other auth schemes (such as the vB scheme) to the more secure default scheme. This happens on login.

I still need to find a solution for this. In the meantime I avoided the problem by creating these non-upgradable auth schemes (attached) and querying the xf_user_authentication table to update the handlers appropriately. In addition, I edited the XenForo_Authentication_Abstract class to specify 'XenForo_Authentication_CoreNoUpgrade' for the default auth scheme:

Code:

/** * Factory method to create the default authentication handler. * * @return XenForo_Authentication_Abstract */ public static function createDefault() { return self::create('XenForo_Authentication_CoreNoUpgrade'); }

Once I find a proper fix then the Abstract edit can be reversed and the handlers changed (to the equivalent default ones) in the xf_user_authentication table, then everything should work fine. This fix is a stopgap until I find a real fix.

@Jake Bunce have you found solution for this issue?

Our users with non-ASCII characters in their passwords cannot login.

Jake Bunce · Nov 6, 2018

ivanp said:
@Jake Bunce have you found solution for this issue?

Our users with non-ASCII characters in their passwords cannot login.

If memory serves, relatively few users were affected and my client was content to just deal with those situations with manual password resets. I didn't actually find a fix.

It's probably an encoding issue.

Slavik · Feb 3, 2019

Slavik updated vBulletin Big Board Importer [vBulletin 3 + vBulletin 4] with a new update entry:

Sunsetting

As XenForo 2.x is the primary focus moving forwards this importer will no longer be available for direct purchase for XenForo 1.x.

Please note service providers can still contact me directly for licensing as required.

A new version for XenForo 2.x will be available shortly.

Read the rest of this update entry...

vBulletin Big Board Importer [vBulletin 3 + vBulletin 4] [Paid] 1.5.0

Active member

Well-known member

Well-known member

Active member

Well-known member

Member

Member

XenForo moderator

Member

Active member

Active member

Active member

Active member

Active member

Active member

Active member

Active member

Active member

Well-known member

XenForo moderator

Similar threads

We value your privacy