recovered forum... no posts since january 2008!

Started by taylor12k, December 05, 2009, 08:12:05 AM

Previous topic - Next topic

taylor12k

i finally may have recovered my forum... but, there are no posts on it newer than January 2008!

i still have access to my old server and have exported messages.sql and other exports that are clearly up to date... why isn't it importing files that are newer than nearly 2 years ago??

also, all of the users' avatars are missing...


any suggestions? thanks!

JimM

I have no idea.  Are you exporting or importing?  I would imagine that it's not importing files that are newer because you are selecting older files.

How do I ask support questions the smart way?
Jim "JimM" Moore
Former Support Specialist

taylor12k

i'm exporting the database from my old server ... of which the files are current up to a week ago... and importing onto the new server.. but only posts up to january of 2008 are showing up!

it's not good....!

Antechinus

Is it exporting the entire db or truncating some tables? You can tell by comparing the number of records in each table in phpMyAdmin.

Also, which tools are you using to export, and which tools are you using to import? This can make a difference.

taylor12k

no idea if it's truncating tables... i'm using three methods of exporting i know:

1. phpmyadmin.. selecting entire database and hitting EXPORT

2. phpmyadmin... selecting particular table (ie: messages.sql, members.sql, etc) exporting those individually

3. BACKUP function in cPanel



i'll then restore using the same techniques, but in import/restore mode...

vbgamer45

Open up the backup file .sql file and scoll to the end.  The end should contain INSERT INTO smf_topics that is the last part of a backup for a nomral smf install
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

taylor12k

thanks.. i didn't know about editing these in any way... the end of my sql looks like this.. do i add your line at the very end, or do i change "ALTER TABLE" into "INSERT INTO" ?:

(1469,0,3,11893,11893,1,1,0,0,30,0),(1470,0,3,11897,11897,1,1,0,0,14,0);
/*!40000 ALTER TABLE `smf_topics` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2009-12-03 20:50:35

vbgamer45

The dump looks good and to be complete. Sometimes a server could reach a memory limit and only part of the sql dump will be made.
In your case we can see the end of the dump file.

For the user avatars/attachments make sure you copy of the attachments server from your old server.
Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

MrPhil

Look for the text of a recent post that you know should be in the backup (.sql file). Is it there? If not, are you sure you're using the latest backup? It didn't get overwritten by an earlier backup being copied over it? The EXPORT didn't die early due to some system file size or time limitation? If it is there, it sounds like the backup is current and complete. Are you getting any messages during phpMyAdmin's IMPORT that some limit has been reached on database size or the like? Are you hitting any limit on process TIME allowed? You'll have to talk to your host about getting around either of these limits. The time limit might be dealt with by using an editor to split up the .sql file into multiple .sql files, and importing them one at a time. Be sure to run an .sql file CREATEing a TABLE before you run one INSERTing INTO the table! If you're running into database size limits, you might EMPTY out all the log files (smf_log_*) and remove their INSERT INTO entries. That might give you some breathing room, but you'll have to do something about your too-big database.

taylor12k

hmm.. thanks... i looked at the sql file and saw a post that contained the word "twig"... i searched the currently "restored" forum for "twig" and it didn't show up... so, that to me implies there are messages in this SQL that did not make it to the restore...

what is the proper way to restore these files? simply going to cpanel>backup and select the file in the RESTORE command?

or, do i use myphpadmin and import it there? if so, do i first select the db on the left (there are 2) and then IMPORT? or do i just IMPORT without selecting a db on the left?


MrPhil

No, look in the .sql file (the backup) for 'twig', using a text editor. If it's not in there, your backup ends with January 2008 for some reason. Maybe you got backups mixed up and/or copied an old one over a new. Or, the backup quit only part way through for some reason.

As far as "what's the proper way to restore", it depends on whether you're trying to preserve your current database structure and table contents, or wiping out the whole thing and starting afresh. Does the backup contain CREATE TABLE commands? Does it DROP TABLE IF EXISTS first? Is it just INSERT INTO? Who produced this backup? If it was SMF, it may have some problems in it that will produce errors during restore (but they can be fixed) . These include INSERT commands with no VALUES (if a table has 250n rows), and strings with only digits missing their quotes '.

I'm not familiar with cpanel > backup > restore, and prefer to use phpMyAdmin IMPORT (after selecting your SMF database). See what's in the backup first -- it can't CREATE TABLEs if those tables already exist and it doesn't DROP them first. Are you looking to completely restore your old data, and get rid of anything new? EMPTY your tables (if the .sql file does not contain CREATE TABLE commands) or DROP your tables (if the .sql file does contain CREATE TABLE commands). I wouldn't attempt to add old data to an existing database, unless you know exactly how to get rid of duplicate data first, and deal with any duplicate key conflicts.

Time to go shovel the driveway now...

taylor12k

i really appreciate your help on this....

so, i went into myphpadmin and DROPPED the tables.. as my backup DOES include "CREATE"... i dropped them, and then IMPORTED the backup and got the following error:

SQL query:

# # Dumping data in `smf_log_search_subjects` # INSERT INTO `smf_log_search_subjects` (`word`, `ID_TOPIC`) VALUES ('#1', 513), ('–', 1434), (0, 1026), (0, 1207), (0, 1430), (00, 1272), (001, 133), (001, 366), (002, 355), (002, 449), (003, 534), (003, 875), (004, 1109), (005, 748), (005, 1084), (005, 1101), (006, 931), (007, 1064), (01, 1300), ('01-11-2008', 413), ('016_audio', 1398), (02, 1103), (027, 124), (029, 603), (03, 1062), (05, 593), (05, 748), (06, 640), (06, 1220), (06, 1260), (07, 703), (08, 424), (08, 574), (08, 640), (08, 703), (08, 748), (08, 1300), (09, 748), (09, 1038), (09, 1062), (09, 1176), (09, 1259), (09, 1260), (09, 1423), ('09-02-07', 216), ('09-12-07', 216), (0x03, 303), ('0º', 1054), ('0º', 1058), (1, 40), (1, 155), (1, 205), (1, 210), (1, 421), (1, 426), (1, 493), (1, 529), (1, 564), (1, 584), (1, 923), (1, 1018), (1, 12[...]

MySQL said:

#1062 - Duplicate entry '5-748' for key 1

taylor12k

also... one of my first backup attempts was 661mb large.. the rest were always 9mb or less.. .. this seems like a good one to work from (probably contains all of the attachments).... but, my server wont' let me upload such a large file via cPanel.

taylor12k

also.. does the installed version of SMF have to be the same as the old one that i'm trying to recover?

that one was 1.1.9.. should i make sure the new server has 1.1.9 on it while i try to recover? or can i put 1.1.11 on?


thanks....

MrPhil

I don't think there were any database differences between 1.1.9 and 1.1.11, so it should be safe to bring in a 1.1.9 database into a 1.1.11 installation. BTW, did you confirm that this backup is current, as opposed to the one from January 2008? 1.1.9 was not out yet in January 2008 (I think 1.1.4 or thereabouts was current then, and I think there were database changes since 1.1.4). You can look in the .sql's smf_settings table for the database version, row "smfVersion", and see what it says.

661MB for one backup and 9MB for the others is cause for concern. Can you look inside these files and see what's there? I don't think that it's merely a matter of compression -- 73:1 seems a bit high, even for excellent compression algorithms. You really ought to have some idea of what you're trying to restore... See what tables are defined and what data is INSERTed. Compare the different files to see if most are incomplete, or just single tables, and which is the most recent. Attachments are not part of the database (they are separate files), although a "backup" you did from your site cp might include both the database (.sql file) and various attachments, avatars, and even the SMF code. As I said, understand what's in all these files before you go trying to restore them. A "backup" is not simply a "backup".

The duplicate entries is something that can be straightened out later. At worst, you can simply leave the "log" files empty (remove the INSERT INTO commands from the .sql file). The message is saying that in table smf_log_search_subjects, there are two entries with (probably) '5-' for word and '748' for TOPIC_ID (the "key" is both fields together). If you can find both entries, remove one set of values (or just remove the entire INSERT INTO if you don't need to keep this information).


taylor12k

Quote from: MrPhil on December 06, 2009, 04:34:08 PM
I don't think there were any database differences between 1.1.9 and 1.1.11, so it should be safe to bring in a 1.1.9 database into a 1.1.11 installation. BTW, did you confirm that this backup is current, as opposed to the one from January 2008? 1.1.9 was not out yet in January 2008 (I think 1.1.4 or thereabouts was current then, and I think there were database changes since 1.1.4). You can look in the .sql's smf_settings table for the database version, row "smfVersion", and see what it says.


yes, i still have access to the old server where the good forum exists... in its latest state as of a week ago. so i'm exporting from that database.

Quote from: MrPhil on December 06, 2009, 04:34:08 PM
661MB for one backup and 9MB for the others is cause for concern. Can you look inside these files and see what's there? I don't think that it's merely a matter of compression -- 73:1 seems a bit high, even for excellent compression algorithms. You really ought to have some idea of what you're trying to restore... See what tables are defined and what data is INSERTed. Compare the different files to see if most are incomplete, or just single tables, and which is the most recent. Attachments are not part of the database (they are separate files), although a "backup" you did from your site cp might include both the database (.sql file) and various attachments, avatars, and even the SMF code. As I said, understand what's in all these files before you go trying to restore them. A "backup" is not simply a "backup".


yeah, all good advice.. unfortunately, i know nothing about this stuff.. i merely run the site. i'm having a programmer look into it and he may be able to determine what's wrong. i just figured "backup" and "restore" would have been things i was capable of.!

Quote from: MrPhil on December 06, 2009, 04:34:08 PM
The duplicate entries is something that can be straightened out later. At worst, you can simply leave the "log" files empty (remove the INSERT INTO commands from the .sql file). The message is saying that in table smf_log_search_subjects, there are two entries with (probably) '5-' for word and '748' for TOPIC_ID (the "key" is both fields together). If you can find both entries, remove one set of values (or just remove the entire INSERT INTO if you don't need to keep this information).


ok, thanks.. yeah, i've basically got aboug 20 different exports and backups and have tried importing and restoring all of them.. trying to see if i can get anything working.. frustrating, as you could imagine...

thanks again for your help...


Antechinus

Note: there is a bug in phpMyAdmin.

If you export from phpMyAdmin and import the same way it will work. However there are limits on the size of the db it will handle without truncating tables.

If you export from another utility (like SMF admin or whatever) and try to import that backup via phpMyAdmin it will result in the forum having dropped content all over the place even though all records show in the database.

MrPhil

Is it the database size that's limited or the size of individual .sql files? If the latter, you could split up one massive .sql file into a number of smaller ones, and IMPORT them one by one. That would also take care of phpMyAdmin exceeding a system time limit (for PHP files) with a single huge file. Sometimes hosting tech support will import extra large .sql files for you, if you are running into time limits.

SMF admin's backup facility has several problems, which are fixable in the .sql file.

Advertisement: