mysql restore not working with tables that have special characters (

[esscher]

Posts: 17


View Profile Email Personal Message (Online)


mysql restore not working with tables that have special characters (," ')
« on: Today at 01:41:12 PM »
Reply with quoteQuote
In my database that I need to backup and restore, I have a lot of tables that have special characters such as parenthesis, double and single quotes, and commas. Here is an example of one that causes problems with restore:

2,,3,3',4,4',5,7-heptahydroxyflavanbioside

It seems like backing up using the standard command:


c:> mysqldump -u root -ppassword -databases mydb > mydb.sql


Works, because I can read through the sql file and its all there.

Problem is when i try to restore with the command:



C:> mysql -u root -ppassword mydb_to_restore_to < mydb.sql


It seems to process fine and returns to the command prompt with no errors. However, after checking the database, nothing has been restored. The whole process works fine with databases that don't have funky characters.

help?

 

[DonQuichote]

Is there an unescaped [ctrl]-z character anywhere in the file? in that case, the command line handling could see that as an End-Of-File.

Are there international characters and is the proper encoding set in the SQL file?

Does the SQL file start a transaction but fails to close it?

Just a few ideas...

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[esscher]

First of all thanks for replying. Second, I have to admin I am somewhat new to mysql, so the last 3 suggestions are beyond what i've learned. I doubt any ctrl-z's are in the database, but i'll check.

could you suggest a way for me to check the encoding? I can't imagine any international characters (other languages? mandarin, etc?) in the database.

I'm not sure how to check the transaction either. Point me in the direction and I'll read up on it or carry out a command you can suggest.

Thanks again.

Esscher

 

[obadare]

I am no expert, but in my experience, you may need to modify the mydb.sql manually bu adding use name of db to restore to.

Thanks for listening