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:
Category:Connector / NET Severity:S3 (Non-critical)
Version: OS:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: Guid

[21 Apr 2011 12:04] Salvatore Pellegriti
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";

MySqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.SingleResult);
                while (reader.Read())

I get the following exception on the Read command:

System.FormatException was caught
  Message=Unrecognized Guid format.
       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

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.
[22 Apr 2011 20:36] Reggie Burnett
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.
[22 Apr 2011 20:57] Salvatore Pellegriti
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.
Salvatore Pellegriti
[28 Apr 2011 10:38] Salvatore Pellegriti
The problem persists with zero length string only if the attribute has size of 36 chars.
[2 May 2011 16:59] Reggie Burnett
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.
[4 Aug 2016 13:51] Tim Velo
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."