MySQL Bugs: #48063: dotNet connector guid as binary error

Bug #48063	dotNet connector guid as binary error
Submitted:	14 Oct 2009 23:10	Modified:	16 Oct 2009 14:08
Reporter:	Jacob Cagley	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.1.2	OS:	Windows
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any
Tags:	dotNet connector guid

Description:
I was upgrading from 1.0.7 where I had made some revisions and posted them back to the group.  I tried to implement the string version to get around this problem but I ran into bug 47985.

The guid in not being returned in the same order as it was created.

How to repeat:
See bug 41452 for a src sample to repeat

Suggested fix:
This is from the 1.0.7 src I am using now.  The function will swap the order of the bytes around so that they will be created in the correct order.

internal override MySqlValue ReadValue(PacketReader reader, long length)
    {
      if (length == -1)
        length = (long)reader.GetFieldLength();

      byte[] newBuff = new byte[length];
      reader.Read( ref newBuff, 0, length );

      int j = 0;
      if (newBuff.Length < 16) {
        j = newBuff.Length;
        byte[] t = new byte[16];
        newBuff.CopyTo(t, 0);
        newBuff = t;

        for (int i = j; i < 16; i++)
          newBuff[i] = 0x20;
      }

      byte[] f = new byte[16];
      j = 3;
      for (int i = 0; i <= j; i++)
        f[j - i] = newBuff[i];

      j = 5;
      for (int i = 4; i <= j; i++)
        f[j - i + 4] = newBuff[i];

      j = 7;
      for (int i = 6; i <= j; i++)
        f[j - i + 6] = newBuff[i];

      for (int i = 8; i < 16; i++)
        f[i] = newBuff[i];

      Guid m = new Guid(f);

      return new MySqlUuid( m, mySqlDbType );
    }

Hi Jacob and thanks for your report.

I do not really get it... Are you saying that the value of GUID returned is wrong or is there something else? Can you elaborate better what you expected and what you got and attach a test case demonstrating this please.

sample src to display the error

using System;
using System.Data;
using System.Data.Common;
using MySql.Data.MySqlClient;

namespace JDMX.UnitTest {
  public class mysqltest {
    [STAThread]
    public static void Main( string[] args ) {
      string connectionString = "server=localhost;port=3306;uid=test;pwd=test;database=mysqltest;old guids=true;";

      // The Guid is this:
      // 01020304-0506-0708-0900-0a0b0c0d0e0f
      Guid guid = new Guid(
        "01020304" +
        "05060708" +
        "09000A0B" +
        "0C0D0E0F" );

      Console.WriteLine( "The guid defined in the src" );
      Console.WriteLine( guid.ToString( "N" ) + " - " + guid.ToString() );
      Console.WriteLine( "" );

      DbConnection connection = new MySqlConnection( connectionString );
      if ( connection.State == ConnectionState.Closed )
        connection.Open();

      DbCommand command = connection.CreateCommand();

      command.CommandText = "USE mysql";
      command.ExecuteNonQuery();

      command.CommandText = "DROP DATABASE mysqltest";
      command.ExecuteNonQuery();

      command.CommandText = "CREATE DATABASE mysqltest DEFAULT CHARACTER SET utf8";
      command.ExecuteNonQuery();

      command.CommandText = "USE mysqltest";
      command.ExecuteNonQuery();

      command.CommandText = "CREATE TABLE `guid_table` ( `guid1` binary(16) NOT NULL, `guid2` binary(16) NULL )";
      command.ExecuteNonQuery();

      command.CommandText = "INSERT INTO guid_table (guid1) VALUES (CAST(0x" + guid.ToString( "N" ) + " AS BINARY));";
      command.ExecuteNonQuery();

      command.CommandText = "SELECT guid1 FROM guid_table;";

      DbDataReader dr = command.ExecuteReader();
      while ( dr.Read() ) {
        Guid newGuid = dr.GetGuid( 0 );

        Console.WriteLine( "The guid as it is pulled from the database" );
        Console.WriteLine( newGuid.ToString( "N" ) + " - " + newGuid.ToString() );
        Console.WriteLine( "" );

        //bool areEqual = (guid == newGuid);
        //Debug.Assert( areEqual );
      }
      connection.Close();

      Console.WriteLine( "Notice the first 4 bytes are reversed, followed by the next 2, then the next 2 again.  The last 8 are the same." );
      Console.WriteLine( "" );

      Console.WriteLine( "Press enter to continue." );
      Console.ReadLine();
    }
  }
}

After verifying the error, this is the solution to fix it
In MySql.Data -> Source -> Types -> MySqlGuid

replace the ReadOldGuid functoin with the following

private MySqlGuid ReadOldGuid( MySqlPacket packet, long length ) {
      if ( length == -1 )
        length = (long)packet.ReadFieldLength();

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

      //check just incase the buffer is to short for a valid guid.  pad the missing elements with 0x20
      if ( buff.Length < 16 ) {
        int j = buff.Length;
        byte[] t = new byte[16];
        buff.CopyTo( t, 0 );
        buff = t;
        for ( int i = j; i < 16; i++ )
          buff[i] = 0x20;
      }

      byte[] f = new byte[16];
      for ( int i = 0; i <= 3; i++ )
        f[3 - i] = buff[i];

      for ( int i = 4; i <= 5; i++ )
        f[5 - i + 4] = buff[i];

      for ( int i = 6; i <= 7; i++ )
        f[7 - i + 6] = buff[i];

      for ( int i = 8; i < 16; i++ )
        f[i] = buff[i];

      MySqlGuid g = new MySqlGuid( f );
      g.OldGuids = OldGuids;
      return g;
    }

Verified as described by Jacob.

Second assertion fails with "01020304" being reversed though all looks fine in database:
mysql> SELECT HEX(guid) FROM guid_table;
+----------------------------------+
| HEX(guid)                        |
+----------------------------------+
| 010203040506070809000A0B0C0D0E0F |
+----------------------------------+
1 row in set (0.00 sec)

Now, there is a workaround in my test case (ie first assertion does not fail) in that one should convert GUID to ByteArray:
            using (MySqlConnection cn = new MySqlConnection("DataSource=**;Database=test;UserID=**;Password=**;PORT=**;logging=True;charset=utf8;old guids=true"))
            {
                cn.Open();
                try
                {
                    Guid guid0 = new Guid(
                      "01020304" +
                      "05060708" +
                      "09000A0B" +
                      "0C0D0E0F"); //For failing part

                    Guid myguid = Guid.NewGuid();
                    byte[] guid = myguid.ToByteArray(); //For working part

                    MySqlCommand cmd = new MySqlCommand("DROP TABLE IF EXISTS guid_table", cn);
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "CREATE TABLE `guid_table`(`guid` BINARY(16) NOT NULL PRIMARY KEY)ENGINE=INNODB";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO `guid_table`(`guid`) VALUES (?guid)";
                    cmd.Parameters.AddWithValue("?guid", guid);
                    cmd.Prepare();
                    cmd.ExecuteNonQuery();
                    cmd.Dispose();

                    string szSelect = "SELECT * FROM guid_table";
                    MySqlCommand myCommand = new MySql.Data.MySqlClient.MySqlCommand(szSelect, cn);

                    MySqlDataReader dr = myCommand.ExecuteReader();
                    dr.Read();
                    System.Console.Out.WriteLine("GUID in text form: " + myguid.ToString());
                    Assert.AreEqual(dr.GetGuid(0).ToString(), myguid.ToString()); //Works
                    dr.Close();

                    cmd.CommandText = "TRUNCATE `guid_table`";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO guid_table VALUES (0x" + guid0.ToString("N") + ");";
                    cmd.ExecuteNonQuery();
                    dr = myCommand.ExecuteReader();
                    dr.Read();

                    System.Console.Out.WriteLine("GUID2 in text form: " + guid0.ToString());
                    Assert.AreEqual(dr.GetGuid(0).ToString(),guid0.ToString()); //Fails
                    dr.Close();

                    myCommand.Dispose();
                    cn.Close();
                }
                catch (Exception ex)
                {
                    Console.Out.WriteLine(DateTime.UtcNow.ToLongTimeString() + "  " + "Exception: " + ex.Message);
                    throw;
                }
            }

Jacob, I'll close this as not a bug. Guid is a thing that is dependend on endianess. its definition is basically {int4,int2,int2, byte[8]}.

To demonstrate how bytes are swapped when Guid is converted to byte array this, check out:

 Guid guid0 = new Guid(
      "01020304" +
      "05060708" +
      "09000A0B" +
      "0C0D0E0F"); //For failing part
 Console.WriteLine(guid0);
 byte[] b = guid0.ToByteArray();
 Console.WriteLine(BitConverter.ToString(b));

To avoid such subtleties, it is much better to use parametrized queries and pass Guid instead of hex values.

Example:
 Guid g = new Guid("32A48AC5-285A-46c6-A0D4-158E6E39729C");
 MySqlCommand cmd = new MySqlCommand("INSERT INTO Test VALUES (1, ?guid)", con);
 cmd.Parameters.AddWithValue("guid",g);
 cmd.ExecuteNonQuery();

And if you read the value back it will be is the same Guid as you put in.

One last comment.  I do the binary insert within the sql because I am building the query dynamically so I am not going use parametrized queries.  I can move the conversion so that I order the bytes correctly before the insert so that it works properly instead of the pull from the db.

One thing to keep in mind is that a query pull in the MySql Query Browser as such

SELECT hex(guid1) FROM Test;

will end up showing all of the bytes in the specified reverse order since that is how they are stored in the db.

I think in your case, the most simple thing would be just to hex-encode the result of

new Guid(guidString).toByteArray()

Bug#49080 was marked as duplicate of this report.