MySQL Bugs: #77879: mysqldump silently fails to correctly generate inserts for binary data

Bug #77879	mysqldump silently fails to correctly generate inserts for binary data
Submitted:	30 Jul 2015 5:08	Modified:	16 Aug 2015 23:57
Reporter:	Prw H1x4	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S2 (Serious)
Version:	mysql Ver 15.1 Distrib 10.0.20-MariaDB	OS:	CentOS (7.1.1503)
Assigned to:		CPU Architecture:	Any

Description:
random binary data in a BINARY(16) column causes silent failures on export and the resulting file silently fails to import.

I'm not sure now how I go about exporting that data or importing it. It may be significant that dbForge crashed when trying to do a data comparison, so perhaps it uses MySQL utilities (or reproduces the bug).

I'm going to work around this by manually exporting the binary values and trying to suppress export of the affected columns.

The problem occurs repeatedly but I've only given you one example of data that causes it. I have more examples but my expectation is that if you simply use a random sequence starting at zero and counting up you will see the problem.

How to repeat:
mysqldump --single-transaction --routines --databases myalarm -uroot -p"PASSWORD"

when run against a database with this table in it:

CREATE TABLE `panels` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `serial_number` int(10) unsigned NOT NULL,
  `panel_types_id` int(11) NOT NULL,
  `all_panels_id` int(11) NOT NULL,
  `installers_id` int(11) DEFAULT NULL,
  `users_id` int(11) DEFAULT NULL,
  `packet_key` binary(16) NOT NULL,
  `user_deleted` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  ...

and random binary values in the packet_key column will sometimes silently leave off the trailing quote around the binary data. This produces a file that silently fails to import:

mysql -p"PASSWORD" -uroot --comments < FILENAME.sql

Adding "--skip-extended-insert" gives me a file that's much easier to debug, and I find that this line is generated:

INSERT INTO `panels` VALUES (1003,1,257126,141,6562,1,88891,'??\\\?ŖeV???,NULL);

Note that the terminating quote on the binary data is missing in the original. The actual content of that row is:

select hex(packet_key) from panels where id=1003;
--> DE77CF5C075CE002C596176556AAF9ED

Suggested fix:
Perhaps you should be using HEX() and UNHEX() or at least give me the option to do that for selected columns?

Oops, I meant to say "you simply use a sequence starting at zero and counting up you will see the problem.". The word "random" was not supposed to be left in there.

Did you try --hex-blob with mysqldump?

http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html#option_mysqldump_hex-blob

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

As a workaround --hex-blob seems to work. It does not address the "no warning on invalid output" problem, so the only way to know that the workaround is needed is to generate a dump file, realise it's useless, then look for a solution.

mysqldump should either generate valid output or report an error, silently failing should be a bug.