MySQL Bugs: #41452: MySqlDataReader's GetGuid method does not treat BINARY(16) column as Guid

Bug #41452	MySqlDataReader's GetGuid method does not treat BINARY(16) column as Guid
Submitted:	14 Dec 2008 10:37	Modified:	6 Mar 2009 15:38
Reporter:	Christopher Jerdonek	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.2.5	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	connector, getstring, Guid, IMySqlValue, IsGuid, MySqlBinary, MySqlDataReader, ToString

Description:
The GetGuid method of MySqlDataReader does not treat BINARY(16) columns as a Guid.  In fact, it errors out on such columns with a FormatException.

This is in contrast to the MySql Connector/NET 5.2 documentation, which says, "BINARY(16) columns are now treated as GUIDs."

How to repeat:
Create a table with a BINARY(16) column, as follows:

DROP TABLE IF EXISTS `test`.`guid_table`;
CREATE TABLE  `test`.`guid_table` (
  `guid_column` binary(16) 
  NOT NULL default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Then run code which starts with a .NET Guid, converts it to a byte array, inserts it into the column, and then attempts to retrieve it using the MySqlDataReader's GetGuid method.

When GetGuid is called, MySql Connector/NET will throw a FormatException.

Here is such code:

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

/// <summary>
/// This method converts a Guid into a .NET byte array, 
/// inserts the byte array into a BINARY(16) column, and
/// then attempts to retrieve the Guid back using MySql 
/// Connector/NET's DbDataReader.GetGuid method.
/// </summary>
/// <remarks>
/// The following table should be present:
/// 
/// DROP TABLE IF EXISTS `test`.`guid_table`;
/// CREATE TABLE  `test`.`guid_table` (
///   `guid_column` binary(16) 
///   NOT NULL default '\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0\0'
/// ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/// </remarks>
static private void GuidTest()
{
	string connectionString = 
		"server=localhost;port=3306;uid=test;pwd=test;database=test;";

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

	MySqlClientFactory mysql = MySqlClientFactory.Instance;

	DbConnection connection = mysql.CreateConnection();
	connection.ConnectionString = connectionString;

	DbCommand command = mysql.CreateCommand();
	command.Connection = connection;

	byte[] bytes = guid.ToByteArray();

	string hex = BitConverter.ToString(bytes);

	// The variable hex becomes this:
	// 040302010605080709000A0B0C0D0E0F
	hex = hex.Replace("-", String.Empty);

	string query = String.Format(
		"TRUNCATE test.guid_table;" +
		"INSERT INTO test.guid_table (guid_column) VALUES (0x{0});",
		hex);

	command.CommandText = query;

	try
	{
		if (connection.State == ConnectionState.Closed)
		{
			connection.Open();
		}

		command.ExecuteNonQuery();

		query = "SELECT guid_column FROM test.guid_table;";

		command.CommandText = query;

		DbDataReader dr = command.ExecuteReader();

		while (dr.Read())
		{
			// Throws a FormatException:
			// Guid should contain 32 digits with 4 dashes 
			// (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).
			Guid newGuid = dr.GetGuid(0);

			bool areEqual = (guid == newGuid);

			Debug.Assert(areEqual);
		}
	}
	finally
	{
		connection.Close();
	}
}

Suggested fix:
Alter the MySqlDataReader's GetString method to treat BINARY(16) columns as GUIDs.

For example:

/// <include file='docs/MySqlDataReader.xml' path='docs/GetString/*'/>
public override String GetString(int i)
{
	IMySqlValue val = GetFieldValue(i, true);

	if (val is MySqlBinary)
	{
		if (((MySqlBinary)val).IsGuid)
		{
			// Then render the column as a Guid string, e.g.
			// 01020304-0506-0708-0900-0a0b0c0d0e0f
			return ((Guid)val.Value).ToString();
		}
		
		byte[] v = ((MySqlBinary)val).Value;
		return fields[i].Encoding.GetString(v, 0, v.Length);
	}

	return val.Value.ToString();
}

Perhaps a cleaner fix would be along these lines:

Add a ToString() method to the internal IMySqlValue interface, and implement this interface method explicitly in each IMySqlValue.  For example, in the MySqlTime class (which contains a public override of ToString()), the implementation could look like--

string IMySqlValue.ToString()
{
	return Value.ToString();
}

Then MySqlDataReader's GetString method could be simplified to something like the following.

/// <include file='docs/MySqlDataReader.xml' path='docs/GetString/*'/>
public override String GetString(int i)
{
	IMySqlValue val = GetFieldValue(i, true);

	return val.ToString();
}

This way, the explicit IMySqlValue.ToString() method of the MySqlBinary class can contain the code special to MySqlBinary instances, along with the logic that tests for when IsGuid is true.

Hi Christopher and thanks for your report.

Verified as described using 5.2 SVN branch against MySQL server 5.1.30 on W2K8 x64 localhost. .NET FW 2

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

    //THIS WORKS
    DataSet myDS = null;
    using (MySql.Data.MySqlClient.MySqlDataAdapter sqlDA = new MySql.Data.MySqlClient.MySqlDataAdapter(myCommand))
    {
        myDS = new DataSet();
        sqlDA.Fill(myDS);
    }
    if (null != myDS)
        if (myDS.Tables.Count > 0)
            if (myDS.Tables[0].Rows.Count > 0)
            {
                Console.Out.WriteLine("GUID returned from mysql: " + myDS.Tables[0].Rows[0]["guid"].ToString());
            }

THIS DOES NOT
    MySqlDataReader dr = myCommand.ExecuteReader();
    dr.Read();
    MessageBox.Show(dr.GetName(0));
    MessageBox.Show(dr.GetGuid(0).ToString());

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/68409

fixed in 5.2.6

An entry was added to the 5.2.6 changelog:

The GetGuid() method of MySqlDataReader did not treat BINARY(16) column data as a GUID. When operating on such a column a FormatException exception was generated.