Inconsistent Outputs from uuid_to_bin() in MySQL vs. Percona XtraDB Cluster

Question

I'm currently working with both MySQL and Percona XtraDB Cluster and have encountered a curious difference in the behavior of uuid_to_bin() and bin_to_uuid() functions across these two systems.

• I am using MySQL version 8.1.0 (for my local) and Percona XtraDB Cluster version 5.7.39-42-57-log (for production).
• My work involves handling UUIDs in both environments.

For example, the query select uuid_to_bin('01ae07f7-8513-11ee-98f4-0242ac110005') produces different outputs in MySQL and Percona XtraDB Cluster. It makes it harder to debug in different environments.

Why does this query yield different results in MySQL and Percona XtraDB Cluster?

Answer 1

From the comments above, it seems someone has used CREATE FUNCTION on the Percona XtraDB Cluster 5.7 instance to create a function named uuid_to_bin, but their implementation is slightly incompatible from the builtin function of the same name added in MySQL 8.0.

This causes confusion because your uses of that function in your development instance gets different results than they do in production. The function exists by the same name, but it's a different function.

It's important to use the same version of database software in development and testing as you will use in production. Every release includes many changes, new features, or even feature removal. Some of these changes are harmless, but others cause incompatibility.

You don't want to waste your time using a feature that is present in a newer version, and then deploy to an older version in production and get a surprise that the feature is not implemented in the older version. That's frustrating and more importantly, it can cause issues because you have to undo some code you had already thought was done. Your employer won't be happy with the time wasted.

So you should develop and test with the same version of software that you run in production, as much as possible.