Can mysqldump –hex-blob also dump DEFAULT values as hex?

November 15, 2021

We do database dumps of Shopware 6 databases. The system stores UUIDs in binary(16) fields.

Now when dumping databases with the --hex-blob option, the data columns are written properly as hex (0x12345….) but we saw that default values are still binary data (see cms_page_version_id)

CREATE TABLE `category` (
  `id` binary(16) NOT NULL,
  `version_id` binary(16) NOT NULL,
  `auto_increment` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` binary(16) DEFAULT NULL,
  `parent_version_id` binary(16) DEFAULT NULL,
  `media_id` binary(16) DEFAULT NULL,
  `cms_page_id` binary(16) DEFAULT NULL,
  `cms_page_version_id` binary(16) NOT NULL DEFAULT '^O©^\ãéjKÂ¾KÙÎu,4%',
  `product_stream_id` binary(16) DEFAULT NULL,

We use this version:

$ mysqldump --version
mysqldump  Ver 10.19 Distrib 10.3.31-MariaDB, for debian-linux-gnu (x86_64)

Is there an option of mysqldump to export also defaults as hex?

If we edit the dumps and save it with vi, the binary data is scrambled.

>Solution :

There is no such option to mysqldump. The --hex-blob option only applies to data values.

Mysqldump gets the CREATE TABLE statement using SHOW CREATE TABLE, which in turn relies on the INFORMATION_SCHEMA.

A bug was reported in 2013 that there’s effectively no way to get column DEFAULT values from this method if the value is binary and contains non-printable characters. https://bugs.mysql.com/bug.php?id=71172

The bug report was acknowledged, but so far it has not been fixed. Feel free to upvote the bug using the "Affects Me" button.

Or try to get MariaDB to fix it themselves, instead of the upstream MySQL code.