Damir's Corner.

Guid values in MySQL with EF Core

I've been recently involved in migrating a .NET project from Microsoft SQL Server to MySQL. While most of it went pretty smoothly, we did encounter some challenges with GUID/UUID values.

Based on the MySQL documentation, the recommended data type for UUID values in MySQL 8 is binary(16), so that's what we decided to use:

CREATE TABLE Persons (
    Id BINARY(16) NOT NULL,
    FirstName VARCHAR(200) NOT NULL,
    LastName VARCHAR(200) NOT NULL,
    PRIMARY KEY (Id ASC)
);

Of course, we wanted to map the UUID field to a Guid property in our code:

public class Person
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public string FirstName { get; set; }
    public string LastName { get; set; }

    public Person(string firstName, string lastName)
    {
        FirstName = firstName;
        LastName = lastName;
    }
}

We used the Pomelo MySQL database provider for Entity Framework Core:

private static readonly string connectionString =
    "server=localhost;user=root;password=root;database=blogsample";

private SampleDbContext CreateDbContext()
{
    var serverVersion = ServerVersion.Create(8, 0, 35, ServerType.MySql);
    var optionsBuilder = new DbContextOptionsBuilder<SampleDbContext>()
        .UseMySql(connectionString, serverVersion)
        .LogTo(Console.WriteLine, LogLevel.Information)
        .EnableSensitiveDataLogging()
        .EnableDetailedErrors();

    return new SampleDbContext(optionsBuilder.Options);
}

However, when we tried to insert the first record using code like this:

var newPerson = new Person("John", "Doe");

using (var context = CreateDbContext())
{
    context.Add(newPerson);
    context.SaveChanges();
}

It failed with the following error:

MySqlConnector.MySqlException : Data too long for column Id at row 1

We (correctly) assumed that there was a mismatch in data type mapping, so we added a Column attribute to the Guid property to explicitly specify the database type:

[Column(TypeName = "binary(16)")]
public Guid Id { get; set; } = Guid.NewGuid();

It did prevent the error and the record was successfully inserted into the database. However, on closer inspection, we noticed that our .NET code and database used different ordering of bytes in the textual representation. For example, the GUID value in our code 5d62f02b-4d95-4e6e-8d7b-095176e63e1c was returned as follows when querying the database directly:

We first found a rejected MySQL bug report about the same issue. Upon further investigation, we realized that these were 2 different variants of encoding UUID values:

• MySQL uses the more common variant 1 which is encoded in big-endian format.
• .NET and Microsoft SQL Server use variant 2 which is encoded in little-endian format.

Since all interaction with the data is supposed to happen via our REST API implemented in .NET, this wasn't that big of an issue. Still, at least developers are going to be looking at the database directly and seeing UUID values in different formats would require constant mental effort, which we wanted to avoid.

Support for big-endian encoding of Guid values has been added in .NET 8. But we still needed to find a non-invasive way to use that as part of MySQL EF Core data mapping. Fortunately, this is already supported in MySqlConnector, the MySQL driver used under the hood by the Pomelo MySQL database provider for EF Core we were using.

We just needed to use the correct MySqlGuidFormat: Binary16 which uses the big-endian byte order. The preferred format can be specified as a connection string option:

private static readonly string connectionString =
 "server=localhost;user=root;password=root;database=blogsample;GuidFormat=Binary16";

With this change, the string representation of UUID values in our code and in MySQL started to match. And we didn't even need the [Column] attribute on the Guid properties anymore to specify the data type.

You can find a working sample project in my GitHub repository. It also contains a docker-compose.yml file to set up a local MySQL server and a SQL script to create a sample database table. The two previous commits show the other two approaches: the broken one without the attribute or the connection string option, and the half-working one with the attribute.

When evaluating the impact of switching the database from Microsoft SQL Server to MySQL, we haven't expected to encounter a low-level issue such as different encoding of UUID values. This just confirmed that such changes should always be thoroughly tested as soon as possible. If we hadn't noticed and addressed it that early, it could have affected the data migration. And fixing that at a later time would certainly require much more effort.