Resource icon

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

No permission to buy ($150.00)
Overview Updates (9) Reviews (3) History Discussion
CreationNation said:
I have a "thinking ahead" question. I am rebuilding caches right now for everything, and that's taken longer than the export/import process. My site is still live while I am doing this on the dev server.

Once I am ready to "throw the switch" - I will run export/import again, and rebuild caches again. Will it take just as long or will it only rebuild from new data?
Click to expand...

Rebuilds will start from the beggining.
 
Keep getting unknown column 'userfield.field4' in field list. Anything to be worried about?
 

Attachments

  • putty.webp
    putty.webp
    43.6 KB · Views: 13
  • Like
Reactions: ehd
The Geek said:
Keep getting unknown column 'userfield.field4' in field list. Anything to be worried about?
Click to expand...

That needs to be fixed, yes. Your vB forum is apparently missing one of the default profile fields.

Find this code in the Export file:

Rich (BB code): 
		$start = microtime(true);
		echo "          user profile";
		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');
		echo " (" . number_format(intval(shell_exec('wc -l ' . self::$dataDir . 'xf_user_profile.txt'))) . ' records in ' .  number_format(microtime(true) - $start, 2) . "s)...\r\n";

This is the relevant piece which references field4:

Code: 
, REPLACE(REPLACE(REPLACE(REPLACE(userfield.field4, \'&amp;\', \'&\'), \'&lt;\', \'<\'), \'&gt;\', \'>\'), \'&quot;\', \'\\"\') AS occupation

You can change that to some other field (if your vB forum has a different field for 'occupation'. Or you can replace the whole thing with this to not import any 'occupation':

Code: 
, '' AS occupation
 
How do I import miserable usergroup into discouraged state?

When I run the exporter, I get a fatal error Call to undefined function pcntl_fork() on line 793. This line relates to:
$this->pid['post'] = true;
if ($this->_runningAll)
{
echo " [forking process for posts]\r\n";
$this->pid['post'] = pcntl_fork();
}

What's going wrong?
 
Alfa1 said:
How do I import miserable usergroup into discouraged state?
Click to expand...

That is not handled by this importer. You will need to write out the IPs to the xf_ip_match table. Then run this query which should prompt a rebuild of that cache:

Code: 
DELETE
FROM xf_data_registry
WHERE data_key = 'discouragedIps';

Alfa1 said:
When I run the exporter, I get a fatal error Call to undefined function pcntl_fork() on line 793. This line relates to:
$this->pid['post'] = true;
if ($this->_runningAll)
{
echo " [forking process for posts]\r\n";
$this->pid['post'] = pcntl_fork();
}

What's going wrong?
Click to expand...

Check your phpinfo. Make sure "pcntl_fork" is not listed under "disable_functions".
 
Its installed now.
I am running into the following:
exporting posts...
postsiconv: conversion to UTF8 unsupported
iconv: try 'iconv -l' to get the list of supported encodings
rm: cannot remove `/importData/xf_post_*': No such file or directory
(0 records in 0.03s)...
reported post commentsiconv: conversion to UTF8 unsupported
iconv: try 'iconv -l' to get the list of supported encodings
-p destination: Broken pipe
 
Hi,

Would anyone be able to help us with our server setup for the import? We are importing a vBulletin 4.2 site with ~15M posts.
We have the Big Board importer running fine, in that the import is successful, but it takes a lot longer than I believe it should.
We are running it on a server with 64gb ram and 2x512gb SSD, and the script is the only thing running during the import.
This is an example of recent import times:

Exporting: Total time for posts: 1,923.03s
Importing: xf_post (13,507.96s)...
Import Total Time: 24,856.63s)...
like cache done, total time: 10,876.96s)...

We have a .cnf file defined for the import so I can easily give details of the MySQL config if I know what's relevant.
Are there other factors that could be affecting import time other than MySQL config?

Thanks,
Toby
 
Hi @Walter, I sent you details of our MySQL setup in a conversation - I would be very grateful if you would be able to give us some advice, we are planning to migrate at the end of this week.


In case Walter is not around, if anyone else would be willing to look at this we would really appreciate it. Just let me know and I will send you our my.cnf in a conversation.

Thanks
 
Last edited:
As a bit more background, MySQL has had some optimisation done already for InnoDB:
Code: 
innodb_buffer_pool_size = 24G
and
Code: 
innodb_flush_method = O_DIRECT
for example. However our trial imports are still much slower than they should be.
As I said it's a 64gb ram dedicated database server, which is doing nothing else during the import.

I'll post the my.cnf here in case anyone can spot what is causing it to be slow:

Code: 
[mysqld]
server-id=2
expire_logs_days=4
replicate-ignore-db = xf_database
tmpdir=/dev/shm
skip-federated
skip-name-resolve
back_log = 2048
max_connections = 600
key_buffer_size = 200M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 2048M
myisam_repair_threads = 1
myisam_use_mmap
myisam_mmap_size=2G
join_buffer_size = 8M
read_buffer_size = 4M
sort_buffer_size = 24M
table_definition_cache = 32000
table_open_cache = 320000 
thread_cache_size = 1200
wait_timeout = 300
connect_timeout = 30
tmp_table_size = 512M
max_heap_table_size = 512M
max_allowed_packet = 256M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 10M
bulk_insert_buffer_size = 16M
query_cache_strip_comments
query_cache_limit = 1512K
query_cache_size = 512M
query_cache_type = 1
query_cache_min_res_unit = 2K
#default-storage-engine = InnoDB
lock_wait_timeout=100
slave-skip-errors=1062

userstat = 0
#fast_index_creation=1
innodb_merge_sort_block_size=2M
innodb_stats_update_need_lock = 0
innodb_rollback_segments = 128
innodb_purge_threads = 1
innodb_log_block_size = 4096
innodb_flush_neighbor_pages = area
innodb_adaptive_flushing_method = keep_average
innodb_doublewrite = 1
#numa_interleave=1
#innodb_buffer_pool_populate=1
innodb_old_blocks_pct=37
innodb_old_blocks_time = 1000
innodb_sync_spin_loops=100
innodb_spin_wait_delay=12
innodb_read_ahead = none
innodb_file_per_table = 1
innodb_open_files = 5000
innodb_data_file_path= ibdata1:10M:autoextend
#innodb_buffer_pool_restore_at_startup = 300
innodb_buffer_pool_instances = 2
innodb_buffer_pool_size = 24G
innodb_log_files_in_group = 2
innodb_log_file_size = 1024M
innodb_log_buffer_size = 8G
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 24
innodb_thread_concurrency_timer_based=1
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1
innodb_io_capacity = 400
innodb_read_io_threads = 12
innodb_write_io_threads = 6
innodb_ibuf_accel_rate = 100
innodb_additional_mem_pool_size = 8G

[mysqld_safe]
log-error=/var/log/mysqld.log
nice = -5
open-files-limit = 65535

[mysqldump]
quick
quote-names
max_allowed_packet = 256M
net_buffer_length=65536

[myisamchk]
key_buffer = 256M
sort_buffer = 256K
read_buffer = 256K
write_buffer = 256K

[mysqlhotcopy]
innodb_log_files_in_group = 2
innodb_log_file_size = 1024M
innodb_log_buffer_size = 8G
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 24
innodb_thread_concurrency_timer_based=1
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1
innodb_io_capacity = 400
innodb_read_io_threads = 12
innodb_write_io_threads = 6
innodb_ibuf_accel_rate = 100
innodb_additional_mem_pool_size = 8G

[mysqld_safe]
log-error=/var/log/mysqld.log
nice = -5
open-files-limit = 65535

[mysqldump]
quick
quote-names
max_allowed_packet = 256M
net_buffer_length=65536

[myisamchk]
key_buffer = 256M
sort_buffer = 256K
read_buffer = 256K
write_buffer = 256K

[mysqlhotcopy]
innodb_log_files_in_group = 2
innodb_log_file_size = 1024M
innodb_log_buffer_size = 8G
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 24
innodb_thread_concurrency_timer_based=1
innodb_lock_wait_timeout=50
innodb_flush_method = O_DIRECT
innodb_support_xa=1
innodb_io_capacity = 400
innodb_read_io_threads = 12
innodb_write_io_threads = 6
innodb_ibuf_accel_rate = 100
innodb_additional_mem_pool_size = 8G

[mysqld_safe]
log-error=/var/log/mysqld.log
nice = -5
open-files-limit = 65535

[mysqldump]
quick
quote-names
max_allowed_packet = 256M
net_buffer_length=65536

[myisamchk]
key_buffer = 256M
sort_buffer = 256K
read_buffer = 256K
write_buffer = 256K

[mysqlhotcopy]
interactive-timeout

Thanks
 
I just purchased this script and have some questions:

1/ I will do a test export and import first to see if it works well before i run it on Production server/database. Can i rerun it after test was successful? Because i read that this script is for 1 site Import only. So as long as it it on same server, it is okie right?

2/ My old VB forum has Latin1 encoding. When i use this Importer, the new XF database has utf8 encoding. And i have problem with character not showing correctly ( my forum is in Vietnamese)
Code: 
Những tin tức nóng hổi về tình hình, phương hướng phát triển của . ++RẤT QUAN TRỌNG++
So when import should i change the Charset to Latin1? right now it is Binary.

3/ how to fix below Errors?
Code: 
exporting posts...
          [forking process for posts]
          post IPs (6,631 records in 2.04s)...
          post thanks as likes (419,719 records in 1.64s)...
          reported postsERROR 1054 (42S22) at line 1: Unknown column 'post.reportthreadid' in 'where clause'
(0 records in 0.01s)...
          reported post commentsERROR 1054 (42S22) at line 1: Unknown column 'post.reportthreadid' in 'where clause'
(0 records in 0.01s)...
          reported profile posts (0 records in 0.01s)...
          reported profile post comments (0 records in 0.01s)...
          reported private messages (0 records in 0.01s)...
          reported private message comments (0 records in 0.01s)...
          post edit history (167 records in 0.10s)...
Total time for posts: 3.83s
 
Last edited:
PeterChen said:
I just purchased this script and have some questions:

1/ I will do a test export and import first to see if it works well before i run it on Production server/database. Can i rerun it after test was successful? Because i read that this script is for 1 site Import only. So as long as it it on same server, it is okie right?

2/ My old VB forum has Latin1 encoding. When i use this Importer, the new XF database has utf8 encoding. And i have problem with character not showing correctly ( my forum is in Vietnamese)
Code: 
Những tin tức nóng hổi về tình hình, phương hướng phát triển của . ++RẤT QUAN TRỌNG++
So when import should i change the Charset to Latin1? right now it is Binary.

3/ how to fix below Errors?
Code: 
exporting posts...
          [forking process for posts]
          post IPs (6,631 records in 2.04s)...
          post thanks as likes (419,719 records in 1.64s)...
          reported postsERROR 1054 (42S22) at line 1: Unknown column 'post.reportthreadid' in 'where clause'
(0 records in 0.01s)...
          reported post commentsERROR 1054 (42S22) at line 1: Unknown column 'post.reportthreadid' in 'where clause'
(0 records in 0.01s)...
          reported profile posts (0 records in 0.01s)...
          reported profile post comments (0 records in 0.01s)...
          reported private messages (0 records in 0.01s)...
          reported private message comments (0 records in 0.01s)...
          post edit history (167 records in 0.10s)...
Total time for posts: 3.83s
Click to expand...

1) Yes. This import script is designed to allow you to run the import multiple times on the same XF installation while you tweak things and retest to get everything right for your specific import.

2) What characters where? By default the Export script should have this line:

Code: 
	// USE THIS SETTING TO SPECIFY AN EXTRA PIPE FOR ALL OUTPUT RECORDS
	// EXAMPLE IS TO PIPE OUTPUT THROUGH iconv IF CHARACTER ENCODING CHANGES ARE NEEDED
	// '| iconv -f LATIN1 -t UTF8'
	public static $extraOutCommand = '| iconv -f LATIN1 -t UTF8';

That should handle the conversion.

3) What version of vB? post.reportthreadid exists in vB3 and vB4. You may just have to add that column to your vB database.
 
You must log in or register to reply here.

Similar threads

K
WoltLab Burning Board/Suite (4/5) Importer and URL redirects [Deleted]
Replies
16
Views
1K
Wiebke
W
Paul B
  • Locked
  • Sticky
  • Question Question
XenForo Cloud FAQ
Replies
0
Views
5K
Paul B
Paul B
Slavik
vBulletin Big Board Importer for XenForo 2 [vBulletin 3 & vBulletin 4] [Deleted]
Replies
1
Views
803
Slavik
Slavik
Steffen
vBulletin 3/4 Moderator Log Import Command
Replies
0
Views
958
Steffen
Steffen
MySiteGuy
Migration, Conversion and Specialty Import Services - including Incremental Imports
5 6 7
Replies
128
Views
20K
MySiteGuy
MySiteGuy
Top Bottom