Fixed Import Problem with attachment

[denns]

Hi,

i have done the following import steps: phpbb2 => phpbb3.1 => XF1 => XF2.1

First everything seems fine, but now i notice that there is a problem with incorrect linked attachments in some posts. Most attachment are linked correct, but a few appear in posts where no attachment was uploaded in phpbb2. In phpbb 3.1 the error does not occur.

Does anyone have an idea what the issue could be?

 

[Paul B]

Do you have examples of a post URL from phpbb3 and XF1 for an affected attachment?

 

[denns]

i do not have the XF1 URL any more but XF2: threads/titlexy.49922/#post-590251

phpbb3.1: viewtopic.php?f=3&t=49922#p590251 or viewtopic.php?p=590251#p590251

 

[Paul B]

We would really need to see the posts to determine whether the attachment is correctly in place after the phpB3 -> XF1 import.

 

[denns]

I just found out that the incorrect attachments originally belong to a private message with same ID 590251.

 

[denns]

In the recent XF database all attachments have the Content_type "post". Is it possible to select all attachment_id from private Messages in phpbb Database (in_message = 1) and then change the content_type of these entries to "conversation_message" in XF?

the table in phpbb is named: phpbb_attachments

 

[Mike]

Unfortunately, it requires more changes than that as there are multiple tables involved, though that would be better than leaving them.

This step of the import isn't actually supposed to be importing PM attachments (so we wouldn't import them). Ideally, you'd apply the fix to that step and redo the import (which would mean a fresh install and losing any content/customization done since then).

If that's not viable, I'd probably have to recommend just deleting the attachments via the XF interface, assuming it isn't that many.

The importer fix would be to change library/XenForo/Importer/PhpBb3.php. Find:

AND post_msg_id > 0

And add this below it:

AND in_message = 0

 

[denns]

ok, but where can i choose and delete these attachments in the xf admin panel? Or is it possible to delete them via database?

 

[Mike]

Generally, the attachments would need to be removed by editing the post in question that has them (to delete them). How many are we talking about?

If you know the ID of the message that contained the attachment that was incorrectly brought over, you can go to <url>/posts/1234/ (changing 1234 to the correct ID) to be taken directly to the post. (There are some control panel options surrounding attachment management, though I don't think they'd really work for this particular situation.)

 

[denns]

we are Talking about 10.000 attachments in private messages. there may be a lot which appear in public posts now.
It would be important to me that these attachments are deleted (or just disappear) in the posts.

 

[Mike]

Right, definitely too many to do by hand.

As an initial step, I have a query which may hide the attachments though it's not really perfect as it would leave counts incorrect. Before you run this, I haven't been able to test this, so please take a backup before you run it. I've also assumed that IDs have been maintained -- if they haven't, this gets more complicated.

UPDATE xf_attachment
SET content_type = 'invalid'
WHERE attachment_ID IN (
    SELECT attach_id
    FROM phpbb_db.phpbb_attachments
    WHERE is_orphan = 0
    AND post_msg_id > 0
    AND in_message = 1
);

It's possible this query may take quite some time to run, so be aware of that. Note that in order to run, the same DB user needs to be able to access the XF and phpBB databases. This query is run on the XF DB and you need to change phpbb_db to your actual phpBB DB name (and the phpbb_ table prefix, if that's different).

Let me know how this goes.

 

[denns]

thank you. 5846 entries do have the content_type 'invalid' now and they are not shown in the posts any more.
Do you think there are any further steps necessary?

 

[Mike]

It's not perfect, but at the least that should have hidden them.

Given that query having been run, I think this set of queries should actually reassociate them with the conversation messages. As before, I haven't been able to test these queries so I strongly recommend backing up before hand and checking the results closely afterwards:

UPDATE xf_post AS post
INNER JOIN xf_attachment AS attachment
ON (attachment.content_id = post.post_id)
SET post.attach_count = post.attach_count - 1
WHERE attachment.content_type = 'invalid';

UPDATE xf_conversation_message AS conv_message
INNER JOIN xf_attachment AS attachment
ON (attachment.content_id = conv_message.message_id)
SET conv_message.attach_count = conv_message.attach_count + 1
WHERE attachment.content_type = 'invalid';

UPDATE xf_attachment
SET content_type = 'conversation_message'
WHERE content_type = 'invalid';