Bug #104840 hex-blob not respected in DDL during mysqldump
Submitted: 6 Sep 2021 12:06 Modified: 14 Mar 2023 17:22
Reporter: Hans Höchtl Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: mysqldump Command-line Client Severity:S2 (Serious)
Version:8.0.26 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqldump, mysqlpump

[6 Sep 2021 12:06] Hans Höchtl
Dumping a database with extensive use of binary fields is very complicated, because `--hex-blob` is not respected in the dumped DDL.

How to repeat:
If you have a DDL like:
create table category
    id                        binary(16)                                                  not null,
    version_id       binary(16)       default 0x0FA91CE3E96A4BC2BE4BD9CE752C3425 not null,
    primary key (id, version_id)

and then dump with `--hex-blob` you won't get this representation as default value but a binary value which often fails when importing the dump.

Suggested fix:
The argument `--hex-blob` should also respect binary values in the DDL.
[7 Sep 2021 15:54] MySQL Verification Team

Thank you for the report.
[16 Nov 2022 15:07] Hans Höchtl
Any updates on this one? Is still pretty annoying
[14 Mar 2023 17:22] Christine Cole
Posted by developer:
Fixed as of the upcoming MySQL 8.0.33 release, and here's the proposed changelog entry from the documentation team:

If mysqldump or mysqlpump could not convert a field's default value to
UTF-8 (for instance, if the field was of type BINARY and the default value
did not coincide with valid UTF-8), the operation produced results that
were not valid to import. Further, using the --hex-blob option did not
resolve the issue. This patch converts the default value to the system
character set. If this fails, the server sends the value as hexadecimal
instead to make it more human-readable.

Thank you for the bug report.