• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 248
  • Last Modified:

Restoring a database file.sql

Im running mysql 5.0 and when I restore a database file .sql I get alot of insert errors and then I get an internal server error and the restore stops.Do I have to manually edit the .sql file in notepad or could this be another issue.Also when I backed up the database I recieved no errors.So im a little puzzled.
0
thenone
Asked:
thenone
  • 20
  • 9
  • 8
2 Solutions
 
todd_farmerCommented:
Did you back it up from a different server version?  For example, many users have problems restoring a database that was created on a 4.1+ version onto a 4.0- version because the table definition will say "... ENGINE=InnoDB" instead of "... TYPE=InnoDB".

I don't  know if this is relevant to your situation or not - it might help if you would post some of the actual errors that you are getting.
0
 
thenoneAuthor Commented:
no its the same version mysql 5.0.19 and the main error I get is internal server error and it stops.I'm using the gui mysql administrator to do this from mysql.com.
0
 
NovaDenizenCommented:
What insert errors do you get?
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
thenoneAuthor Commented:
The other errors that I get is mysql server doesn't know how to handle blah blah blah and I also opened the .sql file in notepad just to confirm everything was there and it is but when I restore it the main error that I recieve is internal server error and not all of the tables are restroed.
0
 
NovaDenizenCommented:
blah blah blah?

Did you run mysql_install_db before you started the server for the first time?
0
 
thenoneAuthor Commented:
huh
0
 
NovaDenizenCommented:
Do you have a valid var directory?  You're supposed to run mysql_install_db to set up the basic files in your var directory for an empty database before you run mysqld for the first time.  
0
 
thenoneAuthor Commented:
I ran the install or setup right from the setup folder.I am running this on windows paltform.The setup completed succesfully.
0
 
NovaDenizenCommented:
What error messages are you getting?
0
 
thenoneAuthor Commented:
The error masseages that I am getting when restoring the database is a couple of insert errors that say please check your mysql documentation for the correct mysql syntax.
2. on inserting mysql server doesn't know how to handle unique id (3333)
                                                                                                (4444)


3.The final error I get is mysql internal server error and the restore ends up failing.

Then I check the schema and not all of the tables are restored but when I open the .sql in notepad they are  there.

Is there a program that will fix discrepcencies in the actual .sql file.
0
 
NovaDenizenCommented:
Could you cut and paste the exact error messages, please?  
0
 
thenoneAuthor Commented:
I can't cut and paste the internal server error message.
0
 
thenoneAuthor Commented:
ok here is the error

The server has returned this error message:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'http://www.thefeeder.com/','TheFeeder, Feeder, Bottom Feeder, TheFeeder.com, sha' at line 2
An internal error occured.
0
 
NovaDenizenCommented:
What is the line in the .sql file that this corresponds to?
0
 
todd_farmerCommented:
Can you post the corresponding line from the dump file?  It seems like perhaps there are characters that are escaped and need to be specified to be used as escape characters when loading the data.
0
 
thenoneAuthor Commented:
it doesn't give a line number.I'm using mysql administrator to load the file.
0
 
todd_farmerCommented:
You'll need to open your .sql file in a text editor and search for the offending line (contains http://www.thefeeder.com/', 'TheFeeder, Feeder...)
0
 
thenoneAuthor Commented:
INSERT INTO `Products` (`site`,`url`,`keywords`,`body`,`id`,`outbound`) VALUES
 ('','http://canuckscentral.com/','',',257760,0),
 ('','http://www.thefeeder.com/','TheFeeder, Feeder, Bottom Feeder, TheFeeder.com, sharks, San Jose Sharks, San Jose, hockey, NHL, Compaq Center, San Jose Arena, Owen Nolan, Teemu Selanne, Vincent Damphousse, Darryl Sutter, Mike Ricci, Brad Stuart,','',257761,4),
0
 
todd_farmerCommented:
The problem is the fourth value in each of the rows:

('','http://canuckscentral.com/','',',257760,0),

there is a single apostrophe (single-quote).  I'm not sure how this was generated, but that's going to cause problems across the board.
0
 
thenoneAuthor Commented:
how should it be also I used mysql backup to do this.
0
 
NovaDenizenCommented:
Maybe keywords is a SET type with a binary value that isn't showing up properly.  Are you doing anything that might change the contents of the file?
0
 
NovaDenizenCommented:
naah, that's probably not right.
0
 
todd_farmerCommented:
What is the body column, and how is it being populated?
0
 
thenoneAuthor Commented:
('','http://canuckscentral.com/','',',257760,0),

How should this be then.
0
 
thenoneAuthor Commented:
and no I didn't modify the file.
0
 
thenoneAuthor Commented:
The body column is longtext and its being poulated through vb
0
 
todd_farmerCommented:
0
 
thenoneAuthor Commented:
so how come this is messed up?
0
 
thenoneAuthor Commented:
Todd what do you use to backup and restore your database or what would you reccommend.Something tells me its the way mysql administrator backed it up.
0
 
todd_farmerCommented:
I use the mysqldump command-line client:

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
0
 
thenoneAuthor Commented:
Do you think this is why I am getting so many errors?
0
 
thenoneAuthor Commented:
Because my tables work fine before they are backed up or restored?
0
 
todd_farmerCommented:
It is possible and worth checking, but I'm not sure.  MySQL Administrator probably invokes mysqldump, but there may be application-level bugs that impact just the MySQL Administrator element.
0
 
thenoneAuthor Commented:
Im sure it is because my whole database is corrupted.Question so you just do the dump from the command line and this doesn't cause any problems?
0
 
todd_farmerCommented:
That is correct.
0
 
thenoneAuthor Commented:
how do you dump from the commad line?
0
 
thenoneAuthor Commented:
it looks like I am not the only one having trouble backing up and restoring data from mysql admin

http://www.techienuggets.com/Detail?tx=40


Looks like I will be using the command prompt for now on.Thanks Todd for your help.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 20
  • 9
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now