Devart.MySqlDump and Binary data

Amael · Post by **Amael** » Thu 30 Jun 2011 09:36

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.

Shalex · Post by **Shalex** » Thu 30 Jun 2011 15:32

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?

Amael · Post by **Amael** » Wed 20 Jul 2011 06:52

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.

Shalex · Post by **Shalex** » Fri 22 Jul 2011 07:05

We will investigate the possibility of making dump in the hexadecimal format for BLOBs and notify you about the results as soon as possible.

Amael · Post by **Amael** » Fri 22 Jul 2011 08:18

Thank you very much.

On my side, i will test dump and restore with this format.
I'll let you know if I have pb.

Amael · Post by **Amael** » Wed 03 Aug 2011 14:23

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.

Shalex · Post by **Shalex** » Fri 05 Aug 2011 17:02

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.

Amael · Post by **Amael** » Mon 08 Aug 2011 15:11

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

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);

The code we use to restore :

Code: Select all

MySqlConnection connection = new MySqlConnection(connectionString);
connection.Unicode = true;
connection.Open();
MySqlDump mySqlDump = new MySqlDump(connection);
connection.Database = "DbName";
mySqlDump.Restore(filename);

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

Amael · Post by **Amael** » Tue 09 Aug 2011 08:19

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.

Shalex · Post by **Shalex** » Fri 12 Aug 2011 11:43

Amael, we plan to implement the functionality of making dump in the hexadecimal format for BLOBs in the next public build of dotConnect for MySQL (in 2-3 weeks). We will notify you about the progress.

Amael · Post by **Amael** » Fri 12 Aug 2011 11:53

It's a great news.

In the meantime, we will do our backups manually by MySQL Administrator. This is quite fastidious in our migration process but it is a temporary solution.

Thank you very much.

Shalex · Post by **Shalex** » Mon 29 Aug 2011 10:42

The MySqlDump.HexBlob property for making dump in hexadecimal format for BLOB is implemented. We will post here when the corresponding build of dotConnect for MySQL is available for download.

Shalex · Post by **Shalex** » Wed 07 Sep 2011 11:45

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 .

Amael · Post by **Amael** » Fri 30 Dec 2011 11:49

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 :

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;

Here is a line of dump text :

Code: Select all

INSERT INTO acl_action VALUES ('������M�:��\0�^�', 'EditFicheSuiviDossier', NULL, NULL);

Is it possible to have the same type of property (for example HexBinary) for making dump in hexadecimal format for BINARY columns ?

StanislavK · Post by **StanislavK** » Thu 05 Jan 2012 17:26

Thank you for the clarification, we will consider the possibility of dumping BINARY columns in the hexadecimal format.