MySQL Bugs: #53615: Requires an option to disable mapping char(36) to Guid

Bug #53615	Requires an option to disable mapping char(36) to Guid
Submitted:	13 May 2010 5:42	Modified:	13 May 2010 14:40
Reporter:	Xiaofeng Wang	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / NET	Severity:	S4 (Feature request)
Version:	6.2.3	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	Connector/Net, Contribution

Description:
By default, Connector/Net 6.1.1 and later automatically treat char(36) as Guid, 
but the users have no choice to disable this automatical mapping. It seems fine and the users will be very happy if the column is really expected as Guid from busniness requirements. Is this always true? No. In some scenario, the users will expect char(36) as the normal string other than Guid. Even worse, if the column data is in an invalid Guid format, the MySqlDataReader.Read method will stop by throwing a FormatException. For instance, the data can be empty string even if the column has the NOT NULL constrant.

How to repeat:
In the following example, I do not like the char(36) as Guid but String. In my business requiremnent, it's valid even if it's empty. Unfortunately, I can not read the line with MySqlDataReader.

-- create a table with a char(36) column
CREATE TABLE `char36_guid_fix` (
  `id` char(36) NOT NULL  
)

-- insert rows including none-guid data
INSERT INTO `char36_guid_fix` VALUES ('1CF8689A-7940-4ae1-B711-50DC1BA1BE20');
INSERT INTO `char36_guid_fix` VALUES ('');
INSERT INTO `char36_guid_fix` VALUES ('0BB424CF-53BB-4587-A185-210991425C60');

            // Populate the data .NET 
	    string query = "SELECT ID FROM char36_guid_fix";
            using (MySqlConnection conn = new MySqlConnection(connString))
            {
                MySqlCommand cmd = new MySqlCommand(query, conn);
                conn.Open();
                MySqlDataReader dr = cmd.ExecuteReader();
                try
                {
                    Console.WriteLine("{0}", dr.GetFieldType(0));
                    while (dr.Read()) // System.FormatException: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).
                    {
                        Console.WriteLine("{0}", dr[0]);
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex);
                }
            }

Suggested fix:
Supports an option, let's say, Treat Char As Guid, in the connection string. 
If users set Treat Char As Guid=false, the char(36) won't be regarded as Guid.
Tinyint(1) and Boolean have the same behavior, but it really has got a option, Treat Tiny As Boolean=false.

Of course, for now, there is a work-around: Setting 'Old Guids=true' will tell Connector/Net to treat binary(16) as Guid other than char(16). You see, the same issue  will still occur with binary(16). A ultimate solution is provide another option 'Treat Binary As Guid=false'.

Hi Xiaofeng and thanks for your report.

IMO this is a valid feature request both in terms of usability and consistency.

Xiaofeng, is there something specific in your application that requires fixed size char fields of exactly 36 chars? My guess would be that this situation will be extremely rare, unless the field in question is exactly Guid.

Changing triage to "Acceptable workaround" as one can change CHAR to VARCHAR in field definition.

Hi Vladislav, actually I agree with what you suppose. In our project, we are connecting to one legacy PHP + MySql system from ASP.NET. Even the char(36) was reall expected as Guid, there were some empty value. There are nothing wrong with PHP because it regards it as char. We are neither allowed to change the database schema nor to update the data. 

However, we still need the feature to support disabling the automatical mapping. As shown in my example above, because MySql has not the native Guid data type, the business application can simply put invalid format to the char(36). But the Connector/Net still assumes it's valid and parse the value to Guid. Even worse, MySqlDataReader will crash when adavancing with its Read method other than accessing with the GetGuid method, the exception does not indicate which row and which column generates this error, the application can not be recovery at this point. Imagine you are retrieve a big result set, it's a pain to do the troubleshooting. 

Thanks.

There is a workaround: use "old guids=true" in the connection string. Hopefully, this option will not be deprecated.