The release of Percona Server for MySQL 8.4.0 includes the new UUID_VX component, which implements UUID versions 1, 3, 4, 5, 6, and 7 according to recently published RFC 9562. UUIDs (Universally Unique Identifiers) are unique identifiers that can be generated independently without a central authority or coordination with other parties. Unlike sequential integer identifiers, which […]

In MySQL 8.0.0 we introduced many new features; among those, three new functions that ease and enhance the support for working with UUIDs.

Background

A UUID is just a 128-bit value and it is usually represented in human-readable format as an UTF8 string composed of 5 groups of hexadecimal characters separated by dashes.…

MySQL 8.0.0 introduces three new miscellaneous UUID functions of IS_UUID(), UUID_TO_BIN() and BIN_TO_UUID() joining the UUID() (in 5.0) and UUID_SHORT() (in 5.1) functions. See 8.0.0 Release Notes.

Thanks to the great work and hosting by Marcus Popp anybody can test out the SQL syntax of MySQL 8.0.0 using db4free without installing anything. If you want a minimal install Giuseppe Maxia provides docker minimal images of 5.0+ versions …

After seeing that several blogs discuss storage of UUID values into MySQL, and that this topic is recurrent on forums, I thought I would compile some sensible ideas I have seen, and also add a couple new ones.

Different techniques

Say we have a table of users, and each user has a UUID.…

A few years ago Peter Zaitsev, in a post titled “To UUID or not to UUID,” wrote: “There is timestamp based part in UUID which has similar properties to auto_increment and which could be used to have values generated at same point in time physically local in BTREE index.”

For this post I’ve rearranged the timestamp part of UUID (Universal Unique Identifier) and did some benchmarks.

Many people store UUID as char (36) and use as row identity value (PRIMARY KEY) because it is unique across every table, every database and every server and allow easy merging of records from different databases. But here comes the problem, using it as PRIMARY KEY causes the problems described below.

Problems with UUID

• UUID has 36 characters which makes it bulky.
• InnoDB stores data in …

I think that getting 128-bit types into MySQL would be good. There are a few use cases for this and right now we have to work around them. That should not be necessary.  While not essential they would make things easier. The headline is easy to understand, but is this really needed? First we need … Continue reading Time to get some 128-bit types into MySQL?

If you’ve run into the following error, then there area couple quick fixes:

Errno: 1593
Fatal error: The slave I/O thread stops because master and slave
have equal MySQL server UUIDs; these UUIDs must be different for
replication to work.

To give some background first, starting in MySQL 5.6, the server generates a true UUID in addition to the –server-id supplied by the user. This is ultimately to help with new replication features.

From a under-the-hood view, when starting, the MySQL server automatically obtains a UUID as follows (per the manual):

1. Attempt to read and use the UUID written in the file data_dir/auto.cnf (where data_dir is the server’s data directory); exit on success.
2. Otherwise, generate a new UUID and save it to this file, creating the file if necessary.
…