Devart.MySqlDump and Binary data
Devart.MySqlDump and Binary data
Hello,
I'm new user of dotConnect for MySQL. I have a question about format of binary data in MySqlDump.
Before Devart.MySqlDump, i was using mysqldump.exe (provided with MySQL) to backup my database and i realized that the format of binary data is different in dump generated with Devart.MySqlDump and mysqldump.exe :
Mysqldump.exe : Oid = 0x0C01F4354348E7458C939F04012BF9ED
Devart.MySqlDump : Oid = '¢ç\tŸfN‹\Ÿ+ù¾'
Is it possible to "format" binary data in dump generated by Devart.MySqlDump like in dump generated with mysqldump.exe ?
Thank you.
I'm new user of dotConnect for MySQL. I have a question about format of binary data in MySqlDump.
Before Devart.MySqlDump, i was using mysqldump.exe (provided with MySQL) to backup my database and i realized that the format of binary data is different in dump generated with Devart.MySqlDump and mysqldump.exe :
Mysqldump.exe : Oid = 0x0C01F4354348E7458C939F04012BF9ED
Devart.MySqlDump : Oid = '¢ç\tŸfN‹\Ÿ+ù¾'
Is it possible to "format" binary data in dump generated by Devart.MySqlDump like in dump generated with mysqldump.exe ?
Thank you.
Last edited by Amael on Tue 09 Aug 2011 07:13, edited 1 time in total.
1. I have created a backup of the same binary data with both Devart.Data.MySql.MySqlDump (6.30.172) and mysqldump.exe (5.5.12), opened the result files as text files - they both contained something like '¢ç\tŸfN‹\Ÿ+ù¾' for binary data.
a) How are you opening dump files to see its content?
b) Please show us the command line you are using to generate dump with mysqldump.exe. Also specify your version of this tool.
2. Why are you interested in a particular "format" of dumped binary data? Does it influence the restore process?
a) How are you opening dump files to see its content?
b) Please show us the command line you are using to generate dump with mysqldump.exe. Also specify your version of this tool.
2. Why are you interested in a particular "format" of dumped binary data? Does it influence the restore process?
Hi, sorry to answer late but I was not at the office these days.
To answer your questions :
1.a) I open dump files using PSPad
1.b) I wrote a mistake, we generated dump with MySQL Administrator, not with mysqldump.exe
Finally, we tried to dump data with different softwares, here are the results :
dotConnect for MySQL (6.30.172) : '¢ç\tŸfN‹\Ÿ+ù¾'
Mysqldump.exe (10.13 Distrib 5.5.11, for Win64) : '¢ç\tŸfN‹\Ÿ+ù¾'
MySQL Workbench export (MySQL dump 10.13 Distrib 5.5.9, for Win32) : '¢ç\tŸfN‹\Ÿ+ù¾'
MySQL Administrator dump (1.4) : 0x0C01F4354348E7458C939F04012BF9ED
2) I prefer use hexadecimal format for BLOB because I don't think a string is the format best suited to represent binary data and I have some apprehensions about the transformations binary string. I fear that to manipulate binary data in the form of strings causes data corruption (interpretation of these characters (special chars) in fct of the character set, platform or software used to dump or restore data)
I will test dump and restore with this format.
To answer your questions :
1.a) I open dump files using PSPad
1.b) I wrote a mistake, we generated dump with MySQL Administrator, not with mysqldump.exe
Finally, we tried to dump data with different softwares, here are the results :
dotConnect for MySQL (6.30.172) : '¢ç\tŸfN‹\Ÿ+ù¾'
Mysqldump.exe (10.13 Distrib 5.5.11, for Win64) : '¢ç\tŸfN‹\Ÿ+ù¾'
MySQL Workbench export (MySQL dump 10.13 Distrib 5.5.9, for Win32) : '¢ç\tŸfN‹\Ÿ+ù¾'
MySQL Administrator dump (1.4) : 0x0C01F4354348E7458C939F04012BF9ED
2) I prefer use hexadecimal format for BLOB because I don't think a string is the format best suited to represent binary data and I have some apprehensions about the transformations binary string. I fear that to manipulate binary data in the form of strings causes data corruption (interpretation of these characters (special chars) in fct of the character set, platform or software used to dump or restore data)
I will test dump and restore with this format.
Hi,
I tried to dump and restore with Devart MySqlDump (Backup and Restore methods) and i encountered pb during restore.
I'm unable to restore data because many insert statements are invalid.
By analysing dump files, i see that some byte in binary data are interpreted like simple quote (') and collide with those that are the real string delimiters in the insert statement.
For example, i have insert statement that contains '¢ç\tŸf(simple_quote)\Ÿ+ù¾'.
-> MySQL understand '¢ç\tŸf' instead of '¢ç\tŸf(simple_quote)\Ÿ+ù¾'
How can i do to avoid this pb with binary data ?
Thank you.
I tried to dump and restore with Devart MySqlDump (Backup and Restore methods) and i encountered pb during restore.
I'm unable to restore data because many insert statements are invalid.
By analysing dump files, i see that some byte in binary data are interpreted like simple quote (') and collide with those that are the real string delimiters in the insert statement.
For example, i have insert statement that contains '¢ç\tŸf(simple_quote)\Ÿ+ù¾'.
-> MySQL understand '¢ç\tŸf' instead of '¢ç\tŸf(simple_quote)\Ÿ+ù¾'
How can i do to avoid this pb with binary data ?
Thank you.
We put backslash (\) before simple quote (') when making dump. This should cover this situation.
Please post here or send us:
a) your connection string (roughly, without credentials). Have you tried the "Unicode=true;" connection string parameter?
b) charset of your server (and the version of your MySQL server);
c) the regional settings of your machine: Control Panel > Reginal and Language Options > the "Standards and formats" drop-down value, Location, and Language for non-Unicode programs;
d) if possible, send us your test data so that we can reproduce the problem in our environment.
Please post here or send us:
a) your connection string (roughly, without credentials). Have you tried the "Unicode=true;" connection string parameter?
b) charset of your server (and the version of your MySQL server);
c) the regional settings of your machine: Control Panel > Reginal and Language Options > the "Standards and formats" drop-down value, Location, and Language for non-Unicode programs;
d) if possible, send us your test data so that we can reproduce the problem in our environment.
You explained me that you put backslash before simple quote when making dump. But does it work the same way for binary data ?
I think that my pb occurs because binary data are dumped in string format instead of hexadecimal notation.
a) connectionString = "Data Source=xxxx;User ID=xxxx;Password=xxxx"
The code we use to dump
The code we use to restore :
b) latin1 - Default Collation (MySql Server 5.1.48 )
c) Standards and formats : "Français"
Location : France
Language for non-Unicode programs : "Français (France)"
d) I can't send you test data because the size of dump file is 15 Mb and It is not possible to send it by email or to join it on Contact form.
Thanks
I think that my pb occurs because binary data are dumped in string format instead of hexadecimal notation.
a) connectionString = "Data Source=xxxx;User ID=xxxx;Password=xxxx"
The code we use to dump
Code: Select all
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Unicode = true;
connection.Open();
MySqlDump mySqlDump = new MySqlDump(connection);
connection.Database = "DbName";
mySqlDump.IncludeDrop = true;
mySqlDump.GenerateHeader = true;
mySqlDump.Backup(filename);
Code: Select all
MySqlConnection connection = new MySqlConnection(connectionString);
connection.Unicode = true;
connection.Open();
MySqlDump mySqlDump = new MySqlDump(connection);
connection.Database = "DbName";
mySqlDump.Restore(filename);
c) Standards and formats : "Français"
Location : France
Language for non-Unicode programs : "Français (France)"
d) I can't send you test data because the size of dump file is 15 Mb and It is not possible to send it by email or to join it on Contact form.
Thanks
Hello Shalex,
This pb backup / restore is really handicapping: I have to put into production a new version of the software we develop. This new version requires changes in the existing database (structure and data).
We have developed a tool that performs the updates to the database. This tool was developed with your library "dotConnect for MySQL" (especially the component MySqlDump)
It is currently impossible to restore the backups generated by the component mysqldump. We are completely blocked in our testing phase and this will delay delivery of the new version of our software.
In precedent post, you said that you will investigate the possibility of making dump in hexadecimal format for BLOBs... Do you have some news ? Do you think it will be possible to make dump in the hexadecimal format for BLOBs ?
Thank you.
This pb backup / restore is really handicapping: I have to put into production a new version of the software we develop. This new version requires changes in the existing database (structure and data).
We have developed a tool that performs the updates to the database. This tool was developed with your library "dotConnect for MySQL" (especially the component MySqlDump)
It is currently impossible to restore the backups generated by the component mysqldump. We are completely blocked in our testing phase and this will delay delivery of the new version of our software.
In precedent post, you said that you will investigate the possibility of making dump in hexadecimal format for BLOBs... Do you have some news ? Do you think it will be possible to make dump in the hexadecimal format for BLOBs ?
Thank you.
New version of dotConnect for MySQL 6.50 is released!
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=21943 .
It can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=21943 .
Hi,
We tried to dump with dotConnect for MySQL 6.60 and we encounter the same problem in spite of adding "mySqlDump.HexBlob = true".
Our binary columns are not dumped in hexadecimal notation.
It seems that this option has only effect with BLOB columns but doesn't work for BINARY columns.
Here is the table structure :
Here is a line of dump text :
Is it possible to have the same type of property (for example HexBinary) for making dump in hexadecimal format for BINARY columns ?
We tried to dump with dotConnect for MySQL 6.60 and we encounter the same problem in spite of adding "mySqlDump.HexBlob = true".
Our binary columns are not dumped in hexadecimal notation.
It seems that this option has only effect with BLOB columns but doesn't work for BINARY columns.
Here is the table structure :
Code: Select all
CREATE TABLE `acl_action` (
`oid` binary(16) NOT NULL,
`nom` varchar(255) NOT NULL,
`description` varchar(255) DEFAULT NULL,
`is_restricted` tinyint(1) DEFAULT NULL,
PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Code: Select all
INSERT INTO acl_action VALUES ('������M�:��\0�^�', 'EditFicheSuiviDossier', NULL, NULL);
-
- Devart Team
- Posts: 1710
- Joined: Thu 03 Dec 2009 10:48