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.
LVL 8
thenoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.