Analyzing a JPA UUID Lookup Failure: MySQL BINARY Padding Issue and Solution

When the Id column type of a JPA entity was specified as UUID, a lookup failure occurred in the MySQL database of the development environment. It worked fine in the H2 in-memory DB used for testing, but in the actual DB, calling repo.findById() returned null and an exception occurred.

The cause was a difference between Hibernate's default behavior of storing UUIDs as binary and the way MySQL handles the BINARY type. The entity schema was set to BINARY(255), and MySQL right-pads BINARY values up to the specified length when storing them. Since a UUID is 16 bytes per RFC 4122, the stored data consisted of 16 bytes of actual data plus 239 bytes of padding.

The core reason for the lookup failure was that the query condition did not include the padding. When querying with a condition like WHERE id = unhex(replace(...)), the 16-byte value did not match the 255-byte padded value, so no results were returned. To resolve this, applying rpad(unhex(replace(...)), 255, '\0') to the query condition to explicitly include the padding allowed the data to be looked up correctly.

As a fundamental solution, adding the @Column(columnDefinition = "BINARY(16)") annotation to change the column length to 16 bytes, the actual size of a UUID, was proposed. This eliminated the unnecessary padding issue, and normal operation was confirmed in both the test and actual environments.