MySQL Bugs: #116695: Unhelpful values in duplicate key errors on binary columns

Bug #116695	Unhelpful values in duplicate key errors on binary columns
Submitted:	18 Nov 2024 12:28	Modified:	18 Nov 2024 13:24
Reporter:	Henning Pöttker (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	9.1.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
A column type like BINARY(16) has become a popular option to store UUID values and there are use cases that require a unique constraint or even primary key on these UUIDs.

Unique constraints on BINARY(16) work fine in general but they tend to produce unhelpful error messages on duplicate entries as bytes that correspond to printable ASCII values are printed as their corresponding ASCII character, and unprintable ASCII values are prefixed with \x, which makes it harder than necessary to copy and paste the value for further analysis.

How to repeat:
The problems can be recreated with the following simple table

mysql> CREATE TABLE `test` (`uuid` BINARY(16) PRIMARY KEY);
Query OK, 0 rows affected (0.04 sec)

The translation into ASCII characters can be seen here:

mysql> INSERT INTO `test` VALUES (0x556e68656c7066756c2076616c756521);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO `test` VALUES (0x556e68656c7066756c2076616c756521);
ERROR 1062 (23000): Duplicate entry 'Unhelpful value!' for key 'test.PRIMARY'

The prefixing with \x can be seen here:

mysql> INSERT INTO `test` VALUES (0x07070707070707070707070707070707);
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO `test` VALUES (0x07070707070707070707070707070707);
ERROR 1062 (23000): Duplicate entry '\x07\x07\x07\x07\x07\x07\x07\x07\x07\x07\x07\x07\x07\x07\x07\x07' for key 'test.PRIMARY'

Translation and prefixing can also occur mixed, of course:

mysql> INSERT INTO `test` VALUES (0x01926b074e4b7f03b2a21a5404dd2c8c);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `test` VALUES (0x01926b074e4b7f03b2a21a5404dd2c8c);
ERROR 1062 (23000): Duplicate entry '\x01\x92k\x07NK\x7F\x03\xB2\xA2\x1AT\x04\xDD,\x8C' for key 'test.PRIMARY'

Suggested fix:
It would be nice if the binary values in the error messages were printed in e.g. the formats that are used in the INSERT statements above. This allows to copy and paste them into queries for further analysis.

Hi Mr. Pottker,

Thank you very much for your bug report.

Actually, this is not a bug, but a feature request.

This feature request can not be implemented, since majority of users prefer the current error message format for BINARY or VARBINARY data types. Next, we can not add configuration variables for the error messages, since error messages (for each error) are constant strings.

Furthermore, as our Reference Manual clearly states, BINARY and VARBINARY  data types are designed to   store byte arrays. 

Hence, that is why you get the error message with an array of bytes.

Not a bug.