MySQL Bugs: #45208: MySqlGuid.ReadValue returns incorrect Guid value

Bug #45208	MySqlGuid.ReadValue returns incorrect Guid value
Submitted:	30 May 2009 19:10	Modified:	4 Jul 2009 18:28
Reporter:	Muhammad Mosa	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.0.3	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Guid, MySqlGuid

Description:
Case is described on this thread
http://forums.mysql.com/read.php?38,226652
And the summary is that the returned Guid is incorrect.

My database charset is laten1
My guid/uuid field is of type binary(16) and it is PK.

How to repeat:
My database charset is laten1
My guid/uuid field is of type binary(16) and it is PK.

To reproduce:
*Create a database with column Id of type binary(16)
*Insert some records in the table
*Try to perform query by id as the follwoing:

var conn = new MySqlConnection("server=localhost;user id=root;password=youpass;persist security info=True;database=dbName");

var cmd = new MySqlCommand("select * from GuidTable where Id=@categoryId", conn);

var param = new MySqlParameter("categoryId", MySqlDbType.Guid);
param.Value = "454F46CE-F259-44F3-985D-94C018956390";
cmd.Parameters.Add(param);
cmd.CommandType = CommandType.Text;
conn.Open();
var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while(reader.Read())
{
  Console.WriteLine("Original {0}, Selected {1}", param.Value, reader.GetGuid("Id"));
}
reader.Close();

Now Compare the selected fetched Guid with your Query parameter Guid.
Expected to tbe the same, but they are different. The last 8 bytes are correct. First 8 are flipped

Suggested fix:
I made my own work around to resolve this issue by modifying MySqlGuid.ReadValue method as the following.

IMySqlValue IMySqlValue.ReadValue(MySqlPacket packet, long length, bool nullVal)
{
            var g = new MySqlGuid {isNull = true};
		    if (!nullVal)
            {
                if (length == -1)
                    length = packet.ReadFieldLength();

                var newBuff = new byte[length];
                packet.Read(newBuff, 0, (int)length);

                var correctBuffer = new byte[length];

                newBuff.CopyTo(correctBuffer, 0);
                correctBuffer[0] = newBuff[3];
                correctBuffer[1] = newBuff[2];
                correctBuffer[2] = newBuff[1];
                correctBuffer[3] = newBuff[0];
                correctBuffer[4] = newBuff[5];
                correctBuffer[5] = newBuff[4];
                correctBuffer[6] = newBuff[7];
                correctBuffer[7] = newBuff[6];

                g = new MySqlGuid(new Guid(correctBuffer));
            }
     return g;
}

This seems to be duplicate of 45206.  Can you provide a complete test case that includes showing how you inserted the records?

I inserted the records using Entity Framework.
Also I tried it by using this statement
MySqlComment command = new MySqlCommand("insert into tablename value (@id);",connection);

MySqlParameter param = new MySqlParameter("id");
param.Value = Guid.NewGuid();

connection.Open();
command.ExecuteNonQuery();
connection.Close();

I forgot to add the parameter in the previouse example.
Anyway using Entity framework should reproduce it.
Another input to this post. I am using my binary(16) as primary key?! does this has any effect?!

Because really if returns an in correct guid presentaion.

Considering that 6.0.3 didn't support Guid type in the entity framework correctly I'm wondering how you inserted them.  I tried with the following test case and it works ok.  can you post a complete command sequence that reproduces the problem with 6.0.3?

        [Test]
        public void GuidOnWhereClause()
        {
            execSQL("CREATE TABLE Test(id INT, g BINARY(16))");

            Guid guid = Guid.NewGuid();
            MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES(1, @g)", conn);
            cmd.Parameters.Add(new MySqlParameter("@g", MySqlDbType.Guid));
            cmd.Parameters[0].Value = guid;
            cmd.ExecuteNonQuery();

            cmd.CommandText = "SELECT * FROM Test WHERE g=@g";
            cmd.Parameters[0].Value = guid.ToString("D");
            using (MySqlDataReader r = cmd.ExecuteReader())
            {
                r.Read();
                Guid g = r.GetGuid("g");
                Assert.AreEqual(guid, g);
            }
        }

I test it with Entity Framework after modifying the Manifest file to include GUID.
I already posted another bug related to this which I discovered later that it is already posted before.
And actually I am extensivly using this with EF.
I noticed something in the test case you submitted

this line:
cmd.Parameters[0].Value = guid.ToString("D");

I don't use it this way. I just code:
cmd.Parameters[0].Value = guid;

Also if I made this:
[Test]
        public void GuidOnWhereClause()
        {
            execSQL("CREATE TABLE Test(id INT, g BINARY(16))");

            Guid guid = Guid.NewGuid();
            MySqlCommand cmd = new MySqlCommand("INSERT INTO Test
VALUES(1, @g)", conn);
            cmd.Parameters.Add(new MySqlParameter("@g",
MySqlDbType.Guid));
            cmd.Parameters[0].Value = guid;
            cmd.ExecuteNonQuery();

            cmd.CommandText = "SELECT * FROM Test WHERE id=@id";
            cmd.Parameters[0].Value = 1;
            using (MySqlDataReader r = cmd.ExecuteReader())
            {
                r.Read();
                Guid g = r.GetGuid("g");
                Assert.AreEqual(guid, g);
            }
        }
this fail with me.
because guid and g are not equal

Muhammad,

I'm sorry I don't have time to dig through your open source project.  If you can post on this bug report a short (10-20 lines) test case that shows the problem I'll push a fix today.

You can even take the test case that I included and make whatever changes are needed to show the problem.  As I said, the test case I posted works fine for me with 6.0.3

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".