restored SMF database does NOT show recent posts although they are in database

marcnyc · June 29, 2009, 11:50:25 PM

I restored a 3 weeks old database (the most recent I had) but I have one problem... The database contains posts with unix time stamp 1243226682 (May 25 200) but when I look at the latest posts in my boards (http://www.chaindlk.com/community/) all I see are posts from 2008 and back... I tried all the forum repair/maintenance that I saw in my site's Cpanel and in SMF's admin section (optimize tables, repair tables etc) but nothing dated 2009 will show up...

What am I doing wrong? How can it not appear on the web if it shows in the database?

Thanks a lot

Antechinus · June 29, 2009, 11:54:29 PM

Could be several things. Is it only posts with a 2009 time stamp or is it random? Is anything else missing or just posts?

marcnyc · June 30, 2009, 12:00:30 AM

It's hard to say... I see that the "last posts" are mostly from 2008 and some 2007... I don't see a single 2009 post I don't think.
I've attached a screenshot and you can also access the forum at www.chaindlk.com/community

Antechinus · June 30, 2009, 12:09:02 AM

You have some "last posts" from 2004 and 2005 there too. Couple of things I can think of. My server is a bit weird in that if I export a backup from SMF admin and restore it using phpMyAdmin the result will be a lot of dropped content because it doesn't like the syntax. Not sure if the same affects your server.

The other thing is are you quite sure the backup was fully imported? Sometimes they time out and need to be re-submitted several times to force all content to be restored. About the only way you can check this is by comparing the backup file content against the number of entries in the messages table.

marcnyc · June 30, 2009, 12:29:31 AM

That's exactly what I have done.
I am looking at the database table smf_messages and the highest ID row has a time stamp of May 25th 2009...
The database has been imported by my host's technicians directly rather than going through PHPMyAdmin because it is too big to do with PHPMyAdmin

Antechinus · June 30, 2009, 12:38:33 AM

Sure but even when the host does it I have seen them stop before a full import. Once I had to put this largish (200+ meg unzipped) db back together for someone and his host imported it, but I had to get on their case and tell them exactly what counts they'd need to see in the db before they could say it was finished. Took them a few goes to get it done.

marcnyc · June 30, 2009, 12:46:23 AM

I understand. Even if that were the case, if I see a post dated May 2009 in the database shouldn't I see the same post on the web too?
Also I compared the sizes of the DB from my cpanel and the file I told them to import and it is the exact same size in Mb

Antechinus · June 30, 2009, 12:53:02 AM

Yup. Ok I have an idea of what it could be. As I said my server is funny with syntax. I've had talks with other team members about this but the bottom line is that SMF admin exports backups with apostrophes saved as \'

This is apparently correct syntax for MySQL when it is stored in the database, or so I'm told. However with my server if I restore a backup with that syntax I get dropped content all over the place, even though all the entries are in the backup file and in the database. To get a clean restore I have to change it to use a syntax which they insist is not right. Beats me as I am no expert on sql. Now you could try doing the same and see if it works for you but I can't gaurantee the results.

marcnyc · June 30, 2009, 02:25:21 AM

how would I go about this? I am not sure I understand...
would I open the SQL and change the syntax? and change it to what?

Antechinus · June 30, 2009, 02:52:12 AM

Yep, basically you have to edit the backup file manually by changing the syntax for apostrophes. If you do it with the mass find/replace option it goes fairly quickly even for quite large files but you need an editor that is up to the job. Also pays to scroll through the thing afterwards and check for remaining errors. It's tedious but not all that difficult.

The change is to save single apostrophes as '' (two singles) rather than as \'

marcnyc · June 30, 2009, 11:18:27 AM

I will give this a try.
Thanks for the suggestion

marcnyc · June 30, 2009, 03:08:15 PM

Hey Antechinus, thanks for the idea but unfortunately even after replacing all \' with '' it still looks like there are no posts from 2009 anywhere to be found... I don't understand how this is possible..
does anyone else have any suggestions?

Antechinus · June 30, 2009, 06:18:39 PM

One question: if you make a new post does that show on the boards or not?

Rumbaar · June 30, 2009, 10:25:47 PM

Can you please check to see if you have two databases in your mySQL lists. Then can you check via your settings.php that it is in fact pointing to the correct database.

From initial reading it would seem to me that there are two databases and you're currently pointing to an older restored version of the db.

marcnyc · July 01, 2009, 02:33:17 AM

there is only one sql database on the new server and to answer the previous question, I tried making a post and it worked... right now it says the post's date is "today" so I'll check it tomorrow and see if it says 2009, if it does it will be the only 2009 post i have

Rumbaar · July 01, 2009, 04:23:01 AM

The post with the UNIX time stamp of 1243226682, can you find search for that one and find it in your current displayed forum?

If you go to the section/thread it is suppose to be in, directly, is it there? If you find the TOPIC ID can you hit it directly via the URL?

ie http://www.chaindlk.com/community/index.php?topic=IDofTOPIC

marcnyc · July 01, 2009, 11:35:06 AM

Thanks for all these troubleshooting ideas I hadn't had. The answer to all three of your questions is NO.
Just to show you I am not crazy, here's a screenshot of my database latest posts... they are in the dB just nowhere to be found on the web..

Rumbaar · July 01, 2009, 06:27:01 PM

You might have only one database on your mySQL server, but I see you have duplicate tables. Can you check the $db_prefix attribute in your settings.php and make sure it's not set too anything other than smf_.

marcnyc · July 02, 2009, 04:15:21 PM

Wow Rumbaar, you are absolutely correct. I found that somehow the DB prefix was set to backup_smf_ prefix... This finally solved my issue thanks to you!
Now the only issue I am left with is wondering how the hell it got switched from smf_ to backup_smf_
I am glad I left my previous host because this crash really created a lot of weirdness... I am not sure even how they got ahold of that backup settings file when recovering from their crash

Rumbaar · July 02, 2009, 10:38:04 PM

Yeah, whoever recreated the forum from the transfer must have altered those settings. Or maybe they were like that from the start and the newly restored backup was put in the correct table structure. Though I did say in my very first post to check your settings.php settings

But either way, it's all fixed now. You're welcome and enjoy.

marcnyc · July 02, 2009, 10:49:55 PM

sorry for not having noticed...
I DID check the settings but I was looking at the mysql settings (username, pw and database name)...
still a mystery how it happened but thanks!

News:

restored SMF database does NOT show recent posts although they are in database