Post count error & vanished attachments

[a10]

So, my turn to learn something. 1.1.10, 2,5 years of smf without sign of a problem.

Yesterday night noticed the post count (and actual posts in the forum) suddenly had changed to approx 1175, previous 10300. afaik did not use or touch remove old posts! Did a 'recount all...' and the post count + posts came back to normal and all seemed well. Saved, I thought.

Then today, doing a db bu + complete ftp bu, I noticed the attachment folder had approx 2700 files missing, vanished. Went back trough older posts, to find attachment thumbnails gone. ftp attachment folder gone from approx 3350 to 650.

Otherwise, forum working without a glitch, all newer posts with or without attachment doing fine.

Got recent db bu's. + a quite recent complete ftp bu. attachments folder bu combined with what's left on the server seems to overlap, so could result in a complete 3350 attachment collection.

What could be the reason for the initial post count error, and deleting of attachments on server? Plain smf with no mods (have only taken away latest member in info center). Have contacted host who will check some logs looking for when and why.

edit: nothing in forum error log, and nothing in adm log.

So, to not mess up anything, seeking help for what to do from here, how to get the attachments back into the posts, without disturbing the latest posts.

Thanks for any help !

[a10]

Thinking trough the situation and looking at what bits and pieces are available, looks like I have to get this done:

1. transfer missing attachments form bu, no problem.

2. then splice a 14 days old database bu to the existing database content for the last 14 days of posting. There I haven't got a clue. Is it possible at all?

Any insight and tips most welcome.
Thanks

[a10]

Not a hint ? 

Situation: got all attachments intact in backup, + a previous attachments table backup. How to get their relation to posts working again.

Is all information about attachments and their relation\placement in posts located in the attachment sql table?

If so, is it possible to edit the current sql attachments table, inserting the missing content, which I've positively located in an attachments table backup, thus reinstating the missing links between posts and attachments for the affected period.

Do not need to get this done immediately, just would be very nice to know if I've got the right bits and pieces ready, and that the restore process is possible. Otherwise, the forums works perfect.

Thanks for any signs of light in the tunnel.

[Arantor]

The process is possible - but is likely to be messy and complex.

I'm more curious as to why that many files and posts just disappeared. Did anyone run the maintenance options to prune older attachments and posts?

[a10]

The process is possible

I'll put my faith in this.

I begin to wonder if a pruneing has been done, as to who and when, that's another discussion, will see to that it won't happen again, but there are no missing posts, attachments problem only. On the other hand there has been server errors + change of server etc going on, so can't say anything for sure. Hoster could not help or explain, & did not have recent backups. Main lesson, do much more frequent backups, both sql and ftp.

Back to the job: I've got the older content\backup overlapping with the actual forum content\recent backups, so got all needed information for the whole forum, but in 2 pieces! Needs to get them together. Am ready for some heavy work if needed. Being a very graphic orientated forum, it'll be worth the nights it may take.

All missing attachments are in a ftp bu, and ready to be transfered. My main question: is it enough to edit the current attachments SQL file, adding the relevant SQL content from the earlier bu?

Thanks

[Arantor]

Assuming you have all/mostly all of the files, you can pretty much splice the two SQL parts for that table together - the only sting you'll have to watch out for is that the first column - the id_attach - isn't duplicated (otherwise you'll get a stack of warnings)

A very simple example:
SQL file 1
INSERT INTO smf_attachments (id_attach, ........) VALUES
(1, ..............),
(2, ..............);

SQL file 2
INSERT INTO smf_attachments (id_attach, ........) VALUES
(2, ..............),
(3, ..............);

It won't look exactly like that but that's the general idea. As you can see there is an overlap between the first and second - with id 2. Just ensure that id 2 is only posted once and you'll be good.

[a10]

Thanks a lot, gives me hope and enthusiasm to start on the job (more questions sure to arise).

[Arantor]

Well, please do ask if you're not sure about something.

[a10]

Thanks. Now the beginner questions... no experience with mysql or phpmyadmin, except understanding the need to be careful (and doing backups).

Got all bits and pieces located and ready. Is it possible to edit smf_attachments table directly in phpmyadmin (2.9.1.1-Debian-8) and save the edited version?
Or must it be exported, edited and imported back ? Is notepad++ the tool for the job.

[YogiBear]

Just a thought as to why all this happened, a10gf. You wouldn't by any chance have Krisbarteo, MAKAVELLI or Mazurdurf in your list of members?

[Arantor]

I wouldn't edit it directly in phpMyAdmin, given the sheer number of rows you'll be working with (unless you want to edit them by hand for each row), but Notepad++ is a good tool.

YogiBear: Good idea for dissecting the cause, but personally I'd rather just get it up and running properly (then backed up to a known OK situation) before dealing with such things.

[a10]

Yogi, no signs of any hack activity, no error log entries, no missing posts etc. Extensive use of htaccess, strict adm approval registration for a long time. Not reading of any similar occurrences, am 99.9% sure it's down to adm error and\or a long period of host server problems followed by a server migration.

Forum is very active and works perfect, 'only' needs to get older attachments' relation to posts back in order. Which, if (or rather when!) successful, will give vital insight into important forum, sql and phpmyadmin processes, so all in all the problem can prove very educating and useful.

One lesson assimilated is to do much more frequent bu's, sql and ftp, and never believe the hoster may have something available in case of crisis.

--------------

Arantor, much thanks for the info. Will get on the job with notepad++. May ask for a few tips along the way, especially at table transfer time, don't want to risk messing up a stable and working forum!

[Arantor]

Let us know how it goes and remember we're always here for support.

[a10]

Update:

Have experimented a little, ftm with a few test-edits in attachments table, using phpmyadmin > SQL > Run SQL query/queries on database

INSERT INTO `smf_attachments`(`ID_ATTACH`, ...\...`file_hash`) VALUES (145, 0, ...\...), etc etc, and a ; at the end!

Works fine, but job far from finished. Seems I'll end up using this process, doing it in several operations, with a good chunk of attachments data at a time, prepared in notepad++ then paste into Run SQL query. Any comments to this process most welcome.

At least now got positive confirmation the problem can be fixed, and nothing wrong with the forum itself. Luckily I had all necessary material, cannot emphasize it enough: take backups often (sql + ftp), and keep them safe.

[a10]

Thanks for the info, Arantor. Looks like the (for me) seemingly impossible sql splice task worked, all attachments and their thumbs are back in place, and have been working fine again now for near 2 weeks, so seems stable.  Had to manually look trough 1000's of files and cross check. Took me some long nights to do it, and lots'a coffee, tobacco + valium  Anyway very educating stuff.
------------
Got an attachment upload + error logs entries problem the last days (looks fixed as well by now), and very probably not related to the former attachment collapse. See http://www.simplemachines.org/community/index.php?topic=341783.0

[Arantor]

Sounds like you had a lot of fun there, but I'm glad to know this is all resolved now