MySQL Bugs: #60945: Unwanted GUID conversion that causes Unrecognized Guid format exception

Bug #60945	Unwanted GUID conversion that causes Unrecognized Guid format exception
Submitted:	21 Apr 2011 12:04	Modified:	2 May 2011 18:38
Reporter:	Salvatore Pellegriti	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.2.4.0	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Guid

Description:
I have a table containing two strings which actually are two GUIDs (generated with MS.NET API).
Both of them are represented within the table scheme as char(36) attributes.
One of them is set to be never null while the other one can be null.

When I try to extract the data from that table with the following set of instructions in C#:

MySqlCommand command; 
string MyConString = "SERVER=" + QClient.DbServerAddress + ";" +
                                    "DATABASE=" + QClient.DbDatabaseName + ";" +
                                    "UID=" + QClient.DbUsername + ";" +
                                    "PASSWORD=" + QClient.DbPassword + ";" +
                                    "Connect Timeout=" + QClient.DbConnectionTimeoutInSecs +";";

MySqlConnection connection = new MySqlConnection(MyConString);
command = connection.CreateCommand();
command.CommandText = "SELECT id1, id2 from MyTestTable";
connection.Open();

MySqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.SingleResult);
try
{                
                while (reader.Read())
{
...
}
}catch...

I get the following exception on the Read command:

System.FormatException was caught
  Message=Unrecognized Guid format.
  Source=mscorlib
  StackTrace:
       at System.Guid.GuidResult.SetFailure(ParseFailureKind failure, String failureMessageID, Object failureMessageFormatArgument, String failureArgumentName, Exception innerException)
       at System.Guid.TryParseGuid(String g, GuidStyles flags, GuidResult& result)
       at System.Guid..ctor(String g)
       at MySql.Data.Types.MySqlGuid.MySql.Data.Types.IMySqlValue.ReadValue(MySqlPacket packet, Int64 length, Boolean nullVal)
       at MySql.Data.MySqlClient.NativeDriver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue valObject)
       at MySql.Data.MySqlClient.ResultSet.ReadColumnData(Boolean outputParms)
       at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
       at MySql.Data.MySqlClient.MySqlDataReader.Read()
       at QuamblaProto.Client.Synchronizer.DBTransfer.QSnapshot.ProcessSnapshot(Int32 revision) in C:\Users\salpe\Documents\Visual Studio 2010\Projects\QuamblaProto\QuamblaProto\Client\Synchronizer\Transfer\Sql\Mysql\QSnapshot.cs:line 66
  InnerException: 

The row that causes the exception to raise up is the one that contains the first null value for the second GUID string.

It seems that a GUID conversion is perfomed even if not explicity asked.

How to repeat:
Create a table with two columns.
The first one is CHAR(36) NOT NULL the second one is CHAR(36) NULLABLE.
Fill the table with some stub data (I used the GUID function from .NET) and let the second column to be sometime null.

Try to Query the content of the table in C# using MySqlDataReader.

Suggested fix:
I increased the size of the second GUID attribute in the table from CHAR(36) to CHAR (37) to work around some unwanted conversion and the problem did not appear again.

This is not a bug.  After reviewing your data, the line that is causing the problem has a zero length string for the second guid, not NULL.   If you change the '' in the line to NULL, then it will work as expected.

Thank you for you answer.
I cannot make more test now since I'm not at work, but your answer let me dubious.
Do you mean that raising that exception is the right way to handle zero length string, then?
Why I don't get it if I change the size of the attribute from 36 to 37, isn't that pretty strange behavior?

Thank you a lot for your help.
Regards,
Salvatore Pellegriti

The problem persists with zero length string only if the attribute has size of 36 chars.

No it is not strange behavior at all.  If the string has a length of 36 then it tries to convert it to a GUID.  A zero length string is invalid for a GUID where a string with length 37 is treated as simply a string value.

We chose to declare char(36) as always containing guids. If your column can contain nulls, I suggest you use NULL instead of '' to represent that.  If the column is not containing guids, then use char(37) or some other length.

But what if a column is set to char(36) for other reasons than a GUID? This seems like horrible practice; to let the API dictate how you need to store/query your data. There should at least be an option/setting somewhere to turn this feature off, instead of forcing it automatically and forcing a data column change. This is like saying "All red cars are fast."