MySQL char (36) is considered to be a GUID-caused BUG and Solution

Sometimes Toad or program may encounter the following errors:

System. FormatException The GUID should contain 32 digits (xxxxxxxx-xxxx-xxxxxxxxxxxx) with four dashes ). Stack Trace: In System. Guid .. ctor (String g) In MySql. Data. Types. MySqlGuid. MySql. Data. Types. IMySqlValue. ReadValue (MySqlPacket packet, Int64 length, Boolean nullVal) In MySQL. Data. mysqlclient. nativedriver. readcolumnvalue (int32 index, mysqlfield field, imysqlvalue valobject) In MySQL. Data. mysqlclient. resultset. readcolumndata (Boolean outputparms) In MySQL. Data. mysqlclient. resultset. nextrow (commandbehavior behavior) In MySQL. Data. mysqlclient. mysqldatareader. Read () In quest. Toad. DB. toaddataadapter. internalreadbackground ()

The reason is that if a field is defined as CHAR (36), the official MySQL connector regards it as the GUID type, in some cases, you are required to enter a string in the form of (xxxxxxxx-xxxx-xxxxxxxxxxxx). Otherwise, an error is returned. In fact, sometimes a field happens to be CHAR (36), but our intention is not to use a GUID.

(For example, if you use the Fill method of MySqlDataAdapter to Fill the DataTable, an Exception is thrown. You can use TableEditor attached to the connector installation to repeat this Bug)

Today, I specifically tracked a MySql. Data Code: I found that MySqlCommand will call the Driver's PrepareStatement method during Prepare, initialize the ResultSet, and call MySqlField. SetTypeAndFlags. The Code is as follows:

 

if (((this.Type == MySqlDbType.String) && (this.CharacterLength == 0x24)) && !this.driver.Settings.OldGuids){       this.mySqlDbType = MySqlDbType.Guid;}

 

The solution is to change this field to VARCHAR (36) or CHAR (40). In short, it is not CHAR (36.