Incorrect Guid order

Question

Ok I have a GUID f5cc4100-f1b4-4af6-9e9e-224b0eb74166 and I am inserting it to MySQL database using the ADO .NET Connector.

I can do it in two ways:

1) Feed byte array to .NET Connector

string query = "insert into test (pk_id) values (@id)";
using (var c = new MySqlCommand(query, conn))
{
    c.Parameters.AddWithValue("@id", new Guid("f5cc4100-f1b4-4af6-9e9e-224b0eb74166").ToByteArray());
    c.ExecuteNonQuery();
}

2) Rely on MySQL standard function to convert hexadecimal to binary string.

string query = "insert into test (pk_id) values (UNHEX(@id))";
using (var c = new MySqlCommand(query, conn))
{
    c.Parameters.AddWithValue("@id", "f5cc4100-f1b4-4af6-9e9e-224b0eb74166".Replace("-", "");
    c.ExecuteNonQuery();
}

The problem I face is the two above methods inserts the same guid in slightly different character order.

If I am selecting back the inserted guid like this:

string query = "select PK_id from test";
using (var c = new MySqlCommand(query, conn))
{
    using (var r = c.ExecuteReader())
    {
        while (r.Read())
        {
            var k = new Guid((byte[])r[0]);
        }
    }
}

I get f5cc4100-f1b4-4af6-9e9e-224b0eb74166 and 0041ccf5-b4f1-f64a-9e9e-224b0eb74166 back. But if I am reading like this:

string query = "select hex(PK_id) from test";
using (var c = new MySqlCommand(query, conn))
{
    using (var r = c.ExecuteReader())
    {
        while (r.Read())
        {
            var k = new Guid((string)r[0]);
        }
    }
}

I get 0041ccf5-b4f1-f64a-9e9e-224b0eb74166 and f5cc4100-f1b4-4af6-9e9e-224b0eb74166.

In short, if I am inserting the GUID as bytearrays, then I need to read them as such and cast as byte arrays to get the right GUID. And if I am inserting the GUID as UNHEX of the hexadecimal guid string, then I need to read from db using its equivalent HEX function.

Why are writing and reading different with the above two methods?
What would be the more appropriate way of handling GUIDs between the two?

I'm not sure why they're different (I don't have MySql), but you should store the guid as CHAR(16) binary. — Chris Gessler, Jun 24 '12 at 12:56
@ChrisGessler I am storing them as binary(16). I do not think a char(16) can hold all of a guid. Did you mean char(36)? — nawfal, Jun 24 '12 at 13:00
Read here char(36) or char(16) binary seems to be the way to go. http://stackoverflow.com/questions/412341/how-should-i-store-guid-in-mysql-tables — Chris Gessler, Jun 24 '12 at 13:03
@ChrisGessler It will be big ask to modify db now. Anyway thanks for letting me know.. — nawfal, Jun 24 '12 at 13:09
See also [this Stack Overflow question](http://stackoverflow.com/questions/10190817/) and [this MSDN question](http://social.msdn.microsoft.com/Forums/en-US/netfxbcl/thread/c734755e-8102-4aba-9d0f-6c7eba8f14c5). — Jeppe Stig Nielsen, Jun 24 '12 at 13:20
... and [this one](http://stackoverflow.com/questions/3320501/). — Jeppe Stig Nielsen, Jun 24 '12 at 13:34
@JeppeStigNielsen Ok that answers why they are different. But as to which to use reliably, I am still confused.. — nawfal, Jun 24 '12 at 13:37
@JeppeStigNielsen Can you post it as an answer? Or should I close this question? — nawfal, Jun 24 '12 at 13:38
The MySQL Connector/NET has explicit support for Guids, so adding the value as a `Guid` object would seem like a natural choice. If you then enabled `oldguids` (http://dev.mysql.com/doc/refman/5.1/en/connector-net-connection-options.html), your application would be using the connector as intended by the connector developers … — hangy, Jun 24 '12 at 13:45
@hangy `oldguids=true` has got nothing to do with ordering of bytes which is the core problem in my question. [Catch something on what oldguids=true do here](http://stackoverflow.com/a/10681245/661933) — nawfal, Jun 27 '12 at 18:13

score 1 · Accepted Answer · edited May 23 '17 at 10:30

Since the Original Poster asked for my comments (which are just links) to be posted as an answer, here it comes:

SO: Guid Byte Order in .NET

MSDN: System.Guid .ToByteArray swapping first 4 bytes

SO: C#: Why isn't Guid.ToString(“n”) the same as a hex string generated from a byte array of the same guid?

It seems like it's not clearly documented what endianness the different components of the Guid possess, when converting to and from Byte[].

score 1 · Answer 2 · answered Jun 27 '12 at 23:22

Nielsen answers one part of the question. As to which to use, there is nothing that makes a good choice. Either way it fine. If the values in the db will be required to read later outside the .NET world, then MySQL hexing and unhexing will be better. There is a slight (very very slight) performance penalty associated with hexing and unhexing in my tests. If it matters for you, then stick with ToByteArray()

Incorrect Guid order

2 Answers2

Linked