Mysql Question posted in
The official MySQL documentation can be found here.

`MySqlDataReader.GetGuid` returns incorrect UUID

I generate UUIDs by using UUID_TO_BIN(UUID(), true) and storing them as BINARY(16).

However, when fetching the values:

var uuid = dataReader.GetGuid(name);
Console.WriteLine(uuid.ToString());

I do get an uuid string printed, but it is not the same string as when running SELECT BIN_TO_UUID("col") on the column.

What am I doing wrong?

  • I tried setting OldGuids in connection string to true but still got the same incorrect uuid back.
  • I am using MySql.Data .NET Connector version 8.3.0. And server is version 8.0.36.
  • Reason I’m using UUID_TO_BIN(UUID(), true) and storing them as BINARY(16) is that, according to my readings, I thought this was the most efficient way to store uuids and using them as primary keys.

Thanks

Tags:

2

Answers


  1. 0

    Try to specify the value of GuidFormat of the connection string as Binary16:

    var sb = new MySqlConnectionStringBuilder
{
    GuidFormat = MySqlGuidFormat.Binary16,
};

    The reason for this is that MySQL constructs GUID string in byte by byte order, while .NET converts the first three sections to integer form in default endian. MySqlDataReader.GetGuid doesn’t change the endian by default, so it will be LE in general, unless the GuidFormat property is specified.

    Incidentally, to get the same result as BIN_TO_UUID("col", 1), you need to use TimeSwapBinary16.

    Login or Signup to reply.
  2. 0

    If you’re creating and storing them with UUID_TO_BIN(UUID(), true), then you will need to read them back with BIN_TO_UUID(columnName, true).

    MySqlDataReader.GetGuid in MySQL Connector/NET (i.e., MySql.Data) has no ability to do this. Some options are:

    1. Change your SQL to use BIN_TO_UUID(columnName, true) everywhere, remove Old Guids=true from your connection string, and use GetGuid to read the string that is returned.
    2. Stop using the true flag when using UUID_TO_BIN to store the GUID (and just use UUID_TO_BIN(UUID())); this will put the bytes in the order that GetGuid expects.
    3. Switch to MySqlConnector and use GuidFormat = TimeSwapBinary16 in your connection string.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search