Bug #44507 Binary(16) considered as Guid
Submitted: 28 Apr 2009 6:58 Modified: 26 May 2009 13:14
Reporter: Christian Cunlif Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.0.3 OS:Any
Assigned to: Reggie Burnett CPU Architecture:Any

[28 Apr 2009 6:58] Christian Cunlif
Description:
Connector.Net 6.0.X introduced a special behavior for a Binary(16) field. DataReader in Connector.Net 6.0.3 considers a binary(16) field as a Guid 
(specifically with a length of 16. Other value are ok).

How to repeat:
My table definition:
CREATE TABLE `file` (
`ID` int(11) NOT NULL,
`file_name` varchar(256) COLLATE latin1_bin NOT NULL,
`signature` binary(16) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;

What I do:
[open Connection with no 'exotic' option, create DbDataReader, etc.]
reader.GetDataTypeName(2) ==> "BINARY(16)"
reader.GetFieldType(2).ToString() ==> "System.Guid" !!!
reader.GetBytes(2, 0, null, 0, 0) ==> MySqlException: GetBytes can only be called on binary columns

Suggested fix:
To mix possibilities of use:

On a Binary(16) field,

- GetFieldType should return the same response as for other Binary(X) fields.
- GetBytes should obvsiously works but it'd be nice to be able to do a GetGuid as well.
[28 Apr 2009 7:10] Tonci Grgin
Hi Christian and thanks for your report.

This is intended and documented:
Version 5.2 - 2/11/2008
--<cut>--
. BINARY(16) columns are now returned as Guid objects
--<cut>--

Please review changelog, documentation and bugsdb for more info on this.
[28 Apr 2009 16:43] Christian Cunlif
Hi Tony
Thanks for your answer but I disagree with it:
- I was already using v5.2.5 for a while, and v6.0.3 behaves differently -> something has changed meanwhile
- The fact that I cannot do a GetBytes() on a Binary(16) field is abnormal. Typically, I'm storing an MD5 hash in the DB... So Mysql with .NET cannot anymore store MD5 field??? Absurd!

Actually, the fact that you can fo a GetGuid on a Binary(16) should be seen as a facilitating shortcut, no?

=> I maintain that on a Binary(16), both GetGuid and GetBytes should not raise an exception but return what was asked... like it is in v5.2.5
[7 May 2009 10:00] Tonci Grgin
Hi Christian. I agree with your feature request (make BINARY(16) work with GetBytes + return consistent type for all BINARY columns).

Test case:
      MySqlConnection con = new MySqlConnection();
      con.ConnectionString = "DataSource=**;Database=test;UserID=**;Password=**;PORT=**;logging=True;allow batch=true";
      con.Open();
      MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS bug44507", con);
      cmdCreateTable.ExecuteNonQuery();
      cmdCreateTable.CommandText = "CREATE TABLE `bug44507`(`ID` int UNSIGNED NOT NULL auto_increment, `file_name` varchar(256) COLLATE latin1_bin NOT NULL, `signature` binary(16), `signaturever` binary(12), PRIMARY KEY (`ID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin;";
      cmdCreateTable.ExecuteNonQuery();
      Guid guid = new Guid(
        "01020304" +
        "05060708" +
        "09000A0B" +
        "0C0D0E0F");
      byte[] bytes = guid.ToByteArray();
      string hex = BitConverter.ToString(bytes);
      // 040302010605080709000A0B0C0D0E0F
      hex = hex.Replace("-", String.Empty);
      string query = String.Format(
        "INSERT INTO bug44507 VALUES (NULL, 'Testing',0x{0}, 0x040302010605080709000A0B);", hex);
      cmdCreateTable.CommandText = query;
      cmdCreateTable.ExecuteNonQuery();
      query = "SELECT * FROM bug44507";
      cmdCreateTable.CommandText = query;
      MySqlDataReader dr = cmdCreateTable.ExecuteReader();
      while (dr.Read())
      {
        Guid newGuid = dr.GetGuid(2);
        Assert.AreEqual(guid, newGuid);
        Assert.AreEqual("BINARY(16)", dr.GetDataTypeName(2));
        Assert.AreEqual("System.Guid", dr.GetFieldType(2).ToString());
        Assert.AreEqual("BLOB", dr.GetDataTypeName(3));
        Assert.AreEqual("System.Byte[]", dr.GetFieldType(3).ToString());
      }
      cmdCreateTable.Dispose();
      con.Close();
      con.Dispose();
Letting Reggie rule on this one.
[20 May 2009 16:30] Reggie Burnett
I agree that GetBytes should work on a binary(16) column.  However, we have to pick a type that will be returned by GetValue on such columns.  It either has to be a binary or a guid.  We chose for it to be guid.  We will review this decision as part of fixing this bug.
[21 May 2009 18:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/74740
[21 May 2009 18:27] Reggie Burnett
fixed in 6.0.4

We have plans to improve this part of the connector so that most if not all of the types can have GetBytes called on them but that is a larger project that I don't want to include in this changeset.
[22 May 2009 9:36] Christian Cunlif
"most if not all of the types can have GetBytes called on them" -> that's not really the way we are usually recommended to use .NET (managed & checked world), but could be very useful in some situations.
[26 May 2009 13:14] Tony Bedford
An entry was added to the 6.0.4 changelog:

The DataReader in Connector/NET 6.0.3 considered a BINARY(16) field as a GUID with a length of 16.