Fixed IPB 3.1 Import Issues

[lasertits]

Hello,

I installed a fresh install of XF 1.1 and then went to test the IPB import - last time I tested the import was on 1.0 and it had errors which were apparently fixed in 1.1. This time around I got these errors:

Import Status Updates:

Mysqli statement execute error : Column 'first_comment_date' cannot be null

Zend_Db_Statement_Mysqli->_execute() in Zend/Db/Statement.php at line 297
Zend_Db_Statement->execute() in Zend/Db/Adapter/Abstract.php at line 479
Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 632
Zend_Db_Adapter_Abstract->update() in XenForo/Importer/IPBoard.php at line 1952
XenForo_Importer_IPBoard->stepStatusUpdates() in XenForo/Importer/Abstract.php at line 77
XenForo_Importer_Abstract->runStep() in XenForo/ControllerAdmin/Import.php at line 180
XenForo_ControllerAdmin_Import->_runStep() in XenForo/ControllerAdmin/Import.php at line 132
XenForo_ControllerAdmin_Import->actionImport() in XenForo/FrontController.php at line 310
XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 132
XenForo_FrontController->run() in /var/www/-snip-/htdocs/admin.php at line 13

I'm going to restart the import and skip the status updates step and see what else error's out if any. I will update this post accordingly. Thanks.

Edit: Ran the importer again, the only step I had issues on was import user status updates, which gives me the error about 11% in.

 

[Jake Bunce]

Really need to see the source data. But it appears that a source record has a null date.

Try running this query on your IPB database to find records with null dates:

SELECT *
FROM member_status_replies
WHERE reply_date IS NULL;

This might find the offending record(s).

 

[lasertits]

Really need to see the source data. But it appears that a source record has a null date.

Try running this query on your IPB database to find records with null dates:

SELECT *
FROM member_status_replies
WHERE reply_date IS NULL;

This might find the offending record(s).

Ran this:

SELECT *
FROM ibf_member_status_replies
WHERE reply_date IS NULL;

/* 0 rows affected, 0 rows found. Duration for 1 query: 0.078 sec. */

0 rows apparently , and looking through the table itself, I haven't spotted any rows that don't contain a valid reply_date.

 

[Jake Bunce]

How big is the database? I can take a look if you give me a backup of the database.

 

[lasertits]

It's just over 200mb. What about just the ibf_member_status_replies table?

 

[Jake Bunce]

It's just over 200mb. What about just the ibf_member_status_replies table?

Ideally I need the whole thing so I can run it through an import. That will allow me to see what records are failing. Then I can try to come up with a fix.

 

[lasertits]

Just realized it's 19 MB compressed. Sending ya a PM as soon as it's uploaded. Thanks for taking the time to assist.

 

[Jake Bunce]

Ok. I am not very familiar with IPB's schema. But after examining the import code and your database I found the problem to be some incorrect counters in your database.

Run this query on your IPB database to fix the counters:

UPDATE ibf_member_status_updates AS msu
SET msu.status_replies = (
	SELECT COUNT(*)
	FROM ibf_member_status_replies AS msr
	WHERE msr.reply_status_id = msu.status_id
);

Then run the import again. This fixed the problem for me.

Backup first to be safe.

 

[lasertits]

I don't think I would have ever found that out hah. Can't say I'm too surprised as that database has been through some years of abuse and moves, but you're right, that did the trick! It completed that step and the entire import successfully.

Thanks so much for taking the time to get it sorted for me. I feel comfortable now to go forth with the final port next week.

 

[Jake Bunce]

Oh, and for the devs... the problem was this line:

XenForo_Importer_IPBoard

				if ($statusUpdate['status_is_latest'])
				{
					$db->update('xf_user_profile', array
					(
						'status' => $import['message'],
						'status_date' => $import['post_date'],
						'status_profile_post_id' => $profilePostId
					), 'user_id = ' . $db->quote($userId));
				}

				$total++;

				$importUpdate = array();

				if (!empty($statusUpdate['status_replies']))
				{
					$replies = $sDb->fetchAll('
						SELECT replies.*,
							members.name
						FROM ' . $prefix . 'member_status_replies AS replies
						INNER JOIN  ' . $prefix . 'members AS members ON
							(replies.reply_member_id = members.member_id)
						WHERE replies.reply_status_id = ' . $sDb->quote($statusUpdate['status_id']) . '
						ORDER BY replies.reply_date
					');

					$replyUserIdMap = $model->getUserIdsMapFromArray($replies, 'reply_member_id');

Some of the status updates had an incorrect "status_replies" value. The error in the first post happened when there was a non-zero "status_replies" value, but in fact the status update had zero replies, so it would try to import null replies. You may want to modify the importer to not trust the "status_replies" value. Otherwise this is just a problem with the source data and is not a bug.

 

[Mike]

Fixed now, by checking that we actually got replies.