PDA

View Full Version : Database Restore error


kartik786
01 Jun 2008, 15:45
Dear community,

My old hosting gave me a database backup dump split in 2 files. The total database is of the size 2.5 gb split in files of 1.5gb and 1 gb.

We concatenated the files with the command

cat db.tar.a db.tar.b >> db.tar

After unpacking the .sql file and importing it, we got the following error


ERROR at line 5804: Unknown command '\"'.

When checking in the tables phpmyadmin, the attachment tables which are the most important part of the site are missing.

Please guide as to how to go about solving this issue.

SEOvB
01 Jun 2008, 16:40
any chance you can just get one large DB backup from your host?

kartik786
01 Jun 2008, 16:46
NO, unfortunately the old host has said that they have sent the final database split in 2 files and thats about it :(

they have a http download limit of 2 gb and hence the 2 files split. I emailed them, but they replied negative.

WEBDosser
01 Jun 2008, 17:13
the missing tables.. are they in either of the two backup files and what did they split the file with?. or did they just select parts of the database for each file?..

--------------- Added 1212337097 at 1212337097 ---------------

maybe also try zcat.

kartik786
01 Jun 2008, 17:21
I am unable to see the missing tables because when restoring the database, I get the following error
ERROR at line 5804: Unknown command '\"'.

I am assuming that the attachment tables are missing because of this error.

did they just select parts of the database for each file?..

No, they dumped the whole database.

--------------- Added 1212337375 at 1212337375 ---------------

maybe also try zcat.

please elaborate.

WEBDosser
01 Jun 2008, 17:28
do a search google for zcat.. works like gzip & gunzip

options here..
http://www.esrl.noaa.gov/gmd/hats/insitu/cats/stations/qnxman/gzip.html

kartik786
01 Jun 2008, 17:36
Okay, since I am not tech savvy kiril_cvetkov (http://www.vbulletin.org/forum/member.php?u=209887) has offered his services :)

I will update him about your suggestion.

SEOvB
01 Jun 2008, 17:45
NO, unfortunately the old host has said that they have sent the final database split in 2 files and thats about it :(

they have a http download limit of 2 gb and hence the 2 files split. I emailed them, but they replied negative.

That sucks, i can't beleive they wouldn't even let you use SSH to transfer a site/db that is so large.

You'll have to put the two files together using the method you already tried, then open up the .sql file that results go to line 5804 and see what it says.

kiril_cvetkov
01 Jun 2008, 17:51
the database is more then 2gb in 2 parts.. the attachment table is the main table (takes the most of the space).. i try to fix.. but it's a huge thing..
I can't help :(

kartik786
01 Jun 2008, 18:04
Okay, the database file size is huge.. any other suggestions that can solve the problem ?

Ideal Web Tech
01 Jun 2008, 19:11
Your attachments should be stored in the file system then you wouldn't have this problem. Ive had this problem a few times before and it usually consisted of a problem because the attachment table store file data it can really range for what the contents are. If you can get a backup with individual insert statements you should be able to get all the attachments but the ones that contain the problematic data.

uberjon
01 Jun 2008, 19:36
After unpacking the .sql file and importing it, we got the following error


what did you unpack it with? if the database was .tar.gz'd in a linux environment. you dont dare try to "unpack" or "extract" it on a windows machine. or it will corrupt the DB

kartik786
01 Jun 2008, 20:13
what did you unpack it with? if the database was .tar.gz'd in a linux environment. you dont dare try to "unpack" or "extract" it on a windows machine. or it will corrupt the DB

Nope, i did not unpack it. the tech from where i purchased the new semi dedicated server performed all steps from downloading the dump to restoring them to informing me about the unknown command error that they are facing.

SEOvB
01 Jun 2008, 20:49
If you were on a dedicated machine before you should have ssh access where you can perform the database back up, and move yourself even if the host only allowed 2GB downloads for whatever reason.

using a command like

mysqldump -u mysql_user -p -h localhost > databaseback.sql

scp databaseback.sql user@new.server.ip:/home/location/to/store on the old machine

If they still dont want to help you out since you're moving. Try to access your old site via ip.add.res.s and move your attachments from the database into the file system using the tools in admincp.

Then get a new database dump, and move the attachment folder to your new host.

kartik786
01 Jun 2008, 22:14
Okay, here's an update, as for my old host , As I said before, these tables do not exist.


mysql> use my_vb_database

216 rows in set (0.00 sec)



You've been given full database dumps. We can't help you any further.



Now how foolish can some hosting company be, saying the vbulletin backup mysql did not have * attachment * tables.


Fortunately, I had a database dump dated Jan 2007, snakes1100 (http://www.vbulletin.org/forum/member.php?u=4123) here helped me restore those attachments in the latest database. So though many attachments will go missing, the forum still will function for now.

At the end of it, thanks to snakes1100 , my forum is back up and running :)

snakes1100
02 Jun 2008, 15:03
Well, this is more of a warning post for users who use dreamhost, i wont post all the details of the situation about this, but i will say this:

His db backup that was incomplete was rather odd looking, it had every table except the 3 required tables for attachments, due to why he changed hosts, i believe dreamhost might of intentionally removed the 3 tables in question from his backup before they sent it to him, seeing as every other table in the two db's existed, it would be kinda hard for the 3 tables in question to not be there if they did a dump via mysql.