Database error


#1

I have messed up my site, I dont understand what happened, but i went into phpmyadmin, i
clicked on export to backup everything, it all went ok i assume, but
then i went to import and clicked on restore back in the mysql
section in the dreamhost control panel, it said there was no
backups, so i went back to the phpmy admin to look for the backup, i
clicked on import and found a file called “shophrm-complete_2006-11-
25.sql” that was saved on my hard drive, it was an sql file so i
figured thta was it, but when i did that i got an error and now my
homepage looks like this.

im assuming i did something wrong, does anyone know how to fix this?


#2

Before you do anything else, make a couple of backup copies of shophrm-complete_2006-11-25.sql

Why did you do a backup and then try to import it back into your existing database? Just curious.

Take a look at that .sql file. It should all be in plain text, and include that shophrm.XXXXX table it claims is missing. Just do a text search and it should show up. Usually in a couple of statements to DROP_TABLE and CREATE_TABLE then an INSERT_INTO command with the necessary data.

I do my restores via command line from SSH or Telnet:
mysql -u USERNAME -p -h YOURDB.DOMAIN.COM DATABASENANE < shophrm-complete_2006-11-25.sql

-Scott


#3

forgive my noobism but i lost you at

Usually in a couple of statements to DROP_TABLE and CREATE_TABLE then an INSERT_INTO command with the necessary data.

I do my restores via command line from SSH or Telnet:
mysql -u USERNAME -p -h YOURDB.DOMAIN.COM DATABASENANE < shophrm-complete_2006-11-25.sql


#4

View that .sql file in a word processor. It should contain SQL statements such as DROP_TABLE, etc., and have some familiar information in there from your site.

To do the restore, FTP that .sql file to your home directory on your site. The directory that has Mail, logs, YOURDOMAIN.COM folders. Then use PuTTY to log in and restore your database using the ‘mysql’ command I listed in my first post.

Post again if you have any more questions.

-Scott


#5

ok, so i got the sql file i had, backed it up a few times and ftp’d it where you said, then i tried to use that putty program you told me about but i had no idea what to do. Its very frustrating, i know tons about computers but nothing about this stuff.

so anyways after i couldnt get putty to work i tried the restore button again in my mysql and it actually brought me to the restore page, said success it will be up in 5-10 min, but nothing has changed on my site. was i supposed to edit the sql file that i uploaded?

also i dont know if this matters but the “shophrm-complete_2006-11-25.sql” file is from when i used the backup database feature on my smf website’s admin center, and it was only 17kb


#6

You shouldn’t edit the sql file. It sounds like you’re almost there. The restore gets put into a job queue, so it may be more than 10 minutes. I’ve never tried a restore like that, so I don’t know if it’ll email you when it’s done. You can always browse the database using phpMyAdmin to see if it’s been updated.

17k sounds a bit small for a database backup. The comments alone are usually about 5k. Again, if you open the .sql file in a word processor, you should see some hint of real data.

-Scott


#7

k, well it still isnt working and its been about 45 min, i looked at the sql file and there is definate hints to my site because i see boards that i have made as welll as some other things.

i went into the phpmyadmin and looked at the shophrm database (according to the stats in mysql the database is about 800kb but the file i got from the website as i said was only 17kb, anyways i looked at the database and it looks like the restore did what it said it was going to by copying the files over with the _timestamp. soo… im getting a bad feeling :frowning:

i am however looking at this again, I see that the error on my site is saying
"Table ‘shophrm.smf_shophrmlog_online’ doesn’t exist
File: /home/.maren/shophrm/shophrm.com/Sources/Subs.php
Line: 2529"

and i dont see that file in the backup in phpmyadmin, is this a file thats missing that is causing my site not to work? it didnt get backed up or placed in my database or something?


#8

I just tried the phpmyadmin export feature and noticed that it has a bunch of options for the export. By default, it won’t overwrite your database or data if you try to restore from that file. This may be a problem.

If your existing database is messed up, a restore won’t help since it won’t delete the bad data and then do a complete restore. It lacks the DROP and CREATE statements to rebuild your database.

Without seeing your .sql file, I can’t be sure of where things stand. My assumptions are that your current database is hosed and that you have a valid backup.

Let me know how things look after the restore happens. At worst, it looks like it’s a new installation of SMF in which case your best bet may be to clear out your database and reinstall the software.

-Scott


#9

do you have msn messanger? i can send you the file to look at if you wouldnt mind helping me out that way, my hotmail is shophrm@hotmail.com

btw the restore that it did hasnt done anything yet, i really dont want to have to start all over again, but two urgent e-mails to support in the last 24hrs hasnt gotten their attention yet to help me, i appreciate yours though


#10

if i do create a new database and reinstall smf, i dont suppose the “shophrm-complete_2006-11-25.sql” backup from my site can then be uploaded to the correct directory and restore it all?