XF 1.5 Correcting bad attachments after import

Tom McIntyre

Active member
Our vBulletin site had a problem with image references and attachments that led to numerous instances of images in the posts that were references to attachments on the same site. Typically these look like [IMG]http://mb.nawcc.org/attachment.php?attachmentid=1234567&stc=1[/IMG]. None of these were imported because they were not recognized by the import software.

We have about 7,000 posts that contain this pattern and many of those posts have multiple instances (for each image the post contained.) We have other posts that did not have that particular pattern, but another error that was just eliminated (deleted) during our import and setup process. In both cases we have all the information from our old site in a separate database and file structure.

I have been able to create a table of all the missing image references in our database and I can map the old attachmentid like the 1234567 above to the filedataid in vBulletin and the post id's on both systems. I collapsed all the vBulletin attachments into a single directory (that is way too big to be viewed) and I can reference the original attachments there. That allows me to substitute a valid image reference of the form [IMG]http://mb.nawcc.org/flatf/9876521.attach[/IMG] where 9876521.attach is the valid filename of the attachment data associated with the attachmentid 1234567.

I have done this a number of times by hand and it works fine since we have @AndyB Convert Image installed. Any edit to the post will convert my hacked reference to a proper attachment inserted into the post. If we succeed in getting all the images back with this approach, we will run Convert Image All to fix them without the need for a hand edit.

I wrote a query to make the substitution in the message field for the bad image reference to the repaired image reference. The remaining problem I have is that I am not sufficiently skilled in MySQL to be able to restore all the references in a single post. Instead, I get only the first reference found in the post. I think this is because of record locking of some sort. I think I could possibly use a regular expression replacement to edit the message field, but I have even less understanding there.

The query below will append the attachments to the post where they belong. Another version of it can use MySQL REPLACE instead of just CONCAT to substitute the new value for the old value above. Both suffer from the problem that they only fix the first instance.

Code:
UPDATE xfnawcc_xf.`xf_post` p
INNER JOIN xfnawcc_vblive.attachmentx a
ON p.post_id = a.contentid
SET p.`message` = 
concat( p.`message`,
concat('
[img]https://mb.nawcc.org/flatf/', a.filedataid,'.attach[/img]
'))

I am hopeful that someone here can just look at that and say "Oh, you need to add ..." Otherwise I may have quite a bit of study ahead to figure out how to do this.
 
Top Bottom