We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Restoring a database file.sql

thenone
thenone asked
on
Medium Priority
269 Views
Last Modified: 2012-08-13
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.
Comment
Watch Question

Top Expert 2006
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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.
What insert errors do you get?

Author

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.
blah blah blah?

Did you run mysql_install_db before you started the server for the first time?

Author

Commented:
huh
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.  

Author

Commented:
I ran the install or setup right from the setup folder.I am running this on windows paltform.The setup completed succesfully.
What error messages are you getting?

Author

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.
Could you cut and paste the exact error messages, please?  

Author

Commented:
I can't cut and paste the internal server error message.

Author

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.
What is the line in the .sql file that this corresponds to?
Top Expert 2006

Commented:
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.

Author

Commented:
it doesn't give a line number.I'm using mysql administrator to load the file.
Top Expert 2006

Commented:
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...)

Author

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),
Top Expert 2006

Commented:
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.

Author

Commented:
how should it be also I used mysql backup to do this.
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?
naah, that's probably not right.
Top Expert 2006

Commented:
What is the body column, and how is it being populated?

Author

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

How should this be then.

Author

Commented:
and no I didn't modify the file.

Author

Commented:
The body column is longtext and its being poulated through vb
Top Expert 2006

Commented:

Author

Commented:
so how come this is messed up?

Author

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.
Top Expert 2006

Commented:
I use the mysqldump command-line client:

http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

Author

Commented:
Do you think this is why I am getting so many errors?

Author

Commented:
Because my tables work fine before they are backed up or restored?
Top Expert 2006

Commented:
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.

Author

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?
Top Expert 2006

Commented:
That is correct.

Author

Commented:
how do you dump from the commad line?

Author

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.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.