Backup database different from original

man123 posted 4 years ago in Import/Export
WE have backed up a mysql 5.0 dbase using heidei on a second identical machine but find that the copy is not the same:

Firstly there are characters like é or ê in the original which is converted to garbage in the backup copy...

Secondly the table contains a column which has a filepath with the backslash \ or double backslash \\ . After backup single backslash have been removed and \\ have been converted to single backslash. Now the filepath is corrupt and cannot be used...

Thanks for help to solve these problems...
ansgar posted 4 years ago
How exactly did you do the export? Please describe which options you turned off/on in the export dialog. And most important, which HeidiSQL revision was used.

* Backslash is escaped by putting a second backslash in front of it. So that's very normal, but this escape character does not get stored when you import such a string. MySQL just needs it to see that this is a special character.
* Broken characters are either caused by wrong collations in the target columns. Please compare CREATE TABLE code of the source and target tables. Or, the file you created was in UTF-8 encoding for example and read as an ASCII file.
man123 posted 4 years ago
Dear Anse
Thanks for your reply.
The Heidi is ver 3.2 Rev1129
MysQL ver is 5.0.45
Both machines have the same char set (when seen in he Host-variable page)

The dbase backup is done using Export and "Export tables as SQL"and Import.

The dbase import is done using IMPORT and LOAD SQl file

Pl see att.

Sorry I am new to mysql and Heidi and did not understand the solutions you suggested for the backslash and the garbage... Grateful if you could explain it to me.


ansgar posted 4 years ago
Hmmmm... HeidiSQL 3.2. Do you know how old that is? I'll tell you: It's about 6 years old, from Nov 2007. Regarding that date I cannot believe you are really new to HeidiSQL and MySQL, are you?

Please go and update your HeidiSQL immediately, and redo the export + import. Or, even easier, export it directly to the target server with the "Export SQL" dialog. That means, select "Server: xyz" as target.
man123 posted 4 years ago
Dear Ansgar

The character garbage got solved by choosing "UTF-8" instead of "Autodetect" while loading SQL in the encoding option .
As to the backslash problem it did not appear when the export was done a second time. Must look for the issue the next time.

Would like to know what it the latest official STABLE version of Heidisql available for

(a) mysql ver 5.0.45 and WIN XP
(b) mysql ver 5.0.45 and Win 7
(c) mysql ver 5.0.45 and Win2003 Ent ver

Thank you for the good work... keep it on

ansgar posted 4 years ago
Look at the download page please. There is just one stable version listed there. HeidiSQL versions are not mapped to MySQL versions.

Please login to leave a reply, or register at first.