Mysql 5.6, output MysqlParameter, binary(16) -> Guid conversion issue

[stretyak]

Software versions
MySqlConnector version: 2.3.7
Server type (MySQL, MariaDB, Aurora, etc.) and version: Mysql 5.6
.NET version: any

Describe the bug
Mysql 5.6 stores guids in binary format. binary(16)
When you define a guid output parameter mysqlconnector cannot convert byte[] received from mysql to Guid.
Because it tries to convert it via ToString -> Parse

Exception

System.FormatException
  HResult=0x80131537
  Message=Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).
  Source=System.Private.CoreLib
  StackTrace:
   at System.Guid.GuidResult.SetFailure(Boolean overflow, String failureMessageID)
   at System.Guid.TryParseExactN(ReadOnlySpan`1 guidString, GuidResult& result)
   at System.Guid.TryParseGuid(ReadOnlySpan`1 guidString, GuidResult& result)
   at System.Guid.Parse(String input)
   at MySqlConnector.Core.TypeMapper.<>c.<.ctor>b__3_13(Object o) in C:\git\MySqlConnector\src\MySqlConnector\Core\TypeMapper.cs:line 114
   at MySqlConnector.Core.DbTypeMapping.DoConversion(Object obj) in C:\git\MySqlConnector\src\MySqlConnector\Core\DbTypeMapping.cs:line 14
   at MySqlConnector.MySqlDataReader.<ReadOutParametersAsync>d__111.MoveNext() in C:\git\MySqlConnector\src\MySqlConnector\MySqlDataReader.cs:line 671
   at MySqlConnector.MySqlDataReader.<InitAsync>d__107.MoveNext() in C:\git\MySqlConnector\src\MySqlConnector\MySqlDataReader.cs:line 486
   at MySqlConnector.Core.CommandExecutor.<ExecuteReaderAsync>d__0.MoveNext() in C:\git\MySqlConnector\src\MySqlConnector\Core\CommandExecutor.cs:line 56
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at System.Runtime.CompilerServices.ConfiguredValueTaskAwaitable`1.ConfiguredValueTaskAwaiter.GetResult()
   at MySqlConnector.MySqlCommand.<ExecuteNonQueryAsync>d__78.MoveNext() in C:\git\MySqlConnector\src\MySqlConnector\MySqlCommand.cs:line 309
   at System.Threading.Tasks.ValueTask`1.get_Result()
   at MySqlConnector.MySqlCommand.ExecuteNonQuery() in C:\git\MySqlConnector\src\MySqlConnector\MySqlCommand.cs:line 108
   at ConsoleApp3.Program.TestNullOutParam(String connStr) in C:\git\MySqlConnector\ConsoleApp3\Program.cs:line 52
   at ConsoleApp3.Program.Main(String[] args) in C:\git\MySqlConnector\ConsoleApp3\Program.cs:line 16

Code sample

var p = new MySqlParameter("@name1", MySqlDbType.Guid);
p.Direction = System.Data.ParameterDirection.Output;
cmd.Parameters.Add(p);
cmd.ExecuteNonQuery();

stored proc sample:

DROP PROCEDURE IF EXISTS `aa_test_out_par`$$

CREATE PROCEDURE `aa_test_out_par`
(
	OUT v_OrigID BINARY(16) 
)
BEGIN
	SELECT id INTO v_OrigID FROM ....

END$$

v_OrigID is guid stored in binary(16)

Expected behavior
it is expected when byte[] is recieved from Mysql side and the output parameter type is Guid
then mysqlconnector should create guid from byte[] like New Guid(byte[]) instead of converting the object to string and parsing back to Guid.

[bgrainger]

The default GUID format is Char36. If you want to store GUIDs as binary, set GUID Format=Binary16 in your connection string.

https://mysqlconnector.net/connection-options/#GuidFormat

[stretyak]

Neither LittleEndianBinary16 nor Binary16 specified in the connection string works.
there are no issues with the data received using a reader. it works fine with LittleEndianBinary16.
The problem is only with output parameters.
but there is a workaround
var p = new MySqlParameter("@name1", MySqlDbType.Binary) works.
and then to create manually the guid from byte[]

[bgrainger]

Another workaround is to not specify the DbType at all, but just let MySqlConnector infer it:

var p = new MySqlParameter { ParameterName = "v_OrigId", Direction = ParameterDirection.Output };
command.Parameters.Add(p);

[bgrainger]

When MySqlConnector needs to return OUT parameters, it issues a SELECT @outParamN, ... query to read them. MySQL Server will set the type of that column to MEDIUMBLOB, which MySqlConnector detects as a byte[].

We could adjust the statement to be SELECT CAST(@outParamN AS BINARY(16)) which would cause the result set to have the right column type.

However, CachedProcedure is not currently sensitive to the MySqlConnection.GuidFormat, so it will type that column as a byte[], still. We could probably plumb that through (for BINARY(16) and CHAR(36) columns) to ensure that GUID columns are read according to the connection string setting.

[stretyak]

Thank you for fixing it.
Any plans to release 2.3.7+that fix?