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

[29 Nov 2018 10:23] Karl Johansson
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
[30 Nov 2018 3:25] Bradley Grainger
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-...
[30 Nov 2018 9:20] Karl Johansson
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....
[24 Dec 2018 13:46] Karl Johansson
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.
[24 Dec 2018 19:47] Bradley Grainger
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?