MySQL Bugs: #93399: First query after application restart always fails with GUID error

Bug #93399	First query after application restart always fails with GUID error
Submitted:	29 Nov 2018 10:23	Modified:	23 Jul 2020 18:41
Reporter:	Karl Johansson	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	8.0.12, 8.0.16	OS:	Ubuntu
Assigned to:		CPU Architecture:	x86

Description:
I'm having this problem with .net core 2.1 where every time I restart an application on Ubuntu 16.04 that's using the MySQL.Data package I get the following exception on the very first query that's sent to the database:

System.FormatException: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)

The trace is

at System.Guid.GuidResult.SetFailure(ParseFailureKind failure,
String failureMessageID,
Object failureMessageFormatArgument,
String failureArgumentName,
Exception innerException)
  at System.Guid.TryParseGuidWithNoStyle(ReadOnlySpan`1 guidString, GuidResult& result) 
  at System.Guid.TryParseGuid(ReadOnlySpan`1 guidString, 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.ResultSet.ReadColumnData(Boolean outputParms)
  at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
  at MySql.Data.MySqlClient.MySqlDataReader.Read()
  at MySql.Data.MySqlClient.Driver.LoadCharacterSets(MySqlConnection connection)
  at MySql.Data.MySqlClient.Driver.Configure(MySqlConnection connection)
  at MySql.Data.MySqlClient.MySqlConnection.Open()
  at System.Data.Common.DbConnection.OpenAsync(CancellationToken cancellationToken)

I haven't managed to trigger it on Windows in my development environment, no matter the configuration. It happens on every Ubuntu 16.04 server and VM we have, which is a lot.

The application works as expected for query 2 ... n, it's only the very first one that fails.

How to repeat:
Deploy app to Ubuntu 16.04 using MySql.Data, open a connection, run query

Possible duplicate of bug #92982.

Has also been reported on Stack Overflow: https://stackoverflow.com/questions/53209369/c-sharp-with-mysql-error-guid-should-contain-...

Yes, it's probably the same thing. But the fixes suggested are basically "edit your my.cnf" which not everyone might be able to do or "switch to a third party library" which I don't even know what to say about....

Hello,

I've looked at the source code of connector/NET because this problem is giving us a lot of grief in production. The problem is in line 227 of Field.cs.

if (Type == MySqlDbType.String && CharacterLength == 36 && !driver.Settings.OldGuids)
Type = MySqlDbType.Guid;

The error happens because SHOW COLLATIONS outputs a field with a CHAR(36) definition which matches the above statement, but the data is not a GUID.

That's just a very strange thing to do to assume that a string is a GUID if it's 36 characters long. Since MySQL doesn't even have a GUID type built in, why does the connector attempt to guess? Is it not time to remove this guess now that you have increased the connector version with a major step to 8? If this were something I could just work around in my own code it would be one thing but when it happens during connection startup it cannot be fixed.

It happens every time a new connection is spawned by the thread pool which means that when load goes up, connections start failing unexpectedly even if the site appears to be running fine under normal circumstances.

I gave it a shot to fix it myself but since the column data does not appear to be available in the method that does this assumption I'm not sure how to best do it. I also don't understand why this doesn't happen on Windows, could it be that SHOW COLLATION produces different column types on Windows?

Would really appreciate an answer here, either a recognition that this is a problem and is being looked at or some pointer about how to write the fix myself.

The explanation is here https://dev.mysql.com/doc/connector-net/en/connector-net-8-0-connection-options.html:

> The back-end representation of a GUID type was changed from BINARY(16) to CHAR(36). This was done to allow developers to use the server function UUID() to populate a GUID table - UUID() generates a 36-character string.

Because MySQL doesn't have a native GUID type, but ADO.NET developers expect there to be one, matching the column type of UUID(), i.e. CHAR(36), makes sense in general. 

As the primary author of the alternate library you mentioned in your previous comment, may I suggest reevaluating it to see if it provides an immediate solution to this problem?

I can add that the MySQL connector for the Microsoft PowerBI service (i.e. cloud scheduled refresh) doesn't work because of this bug when the server is MySQL 8.0. It doesn't allow for editing the connection string either.

It's crazy that this isn't given attention since it is affecting every service that use the official MySQL connector. 

Again, the problem isn't necessarily with the connector, it's that the MySQL Server appears to return values with type CHAR(36) on initial connection (SHOW COLLATIONS).

Hello Karl Johansson,

Thank you for the report and feedback.

regards,
Umesh

Bug #92982 marked as duplicate of this one

I don't understand why it sees PAD_ATTRIBUTE as CHAR(36) when it is ENUM: 'PAD SPACE' and 'NO_PAD'. Isn't that even bigger issue?

I will offer two solutions: 

1. Quickest and bit dirty: Instead of using show collation and then using only two columns, you can use select id, charset from INFORMATION_SCHEMA.COLLATIONS. 

2. Now, back to the real problem: 

2.1. Instead of supposing it is GUID only if it is char(36), at line 227 in https://github.com/mysql/mysql-connector-net/blob/8.0/MySQL.Data/src/Field.cs leave this for later. After reading the first value for that field, check if it is 36 characters long, whether it is formatted 8-4-4-4-12, and then set it to guid type, and even if you wish, now - re-read this value. 

2.2. Make a try/catch around line 207 at  https://github.com/mysql/mysql-connector-net/blob/8.0/MySQL.Data/src/ResultSet.cs. Then, if this fails with "System.FormatException: Guid should contain" - change the type of the field to String and try reading it again. 

For both solutions, you can keep another property in Field.cs which is verified_type or verified_guid, so you set this only when it is not set and after the first successful read.

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.22 release, and here's the proposed changelog entry from the documentation team:

Every column of type CHAR(36) was interpreted as a GUID, which could cause
the first query made by a restarted application using the MySQL.Data
package to return an exception. This fix introduces a dedicated format
(8-4-4-4-12) to interpret the column type properly.

Thank you for the bug report.