Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open
stretyak opened this issue Nov 29, 2024 · 4 comments · May be fixed by #1546
Open

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

stretyak opened this issue Nov 29, 2024 · 4 comments · May be fixed by #1546
Assignees
@bgrainger
Labels
question

Comments

@stretyak
Copy link

stretyak commented Nov 29, 2024 •
edited by bgrainger
Loading

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
Copy link
Member

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

@bgrainger bgrainger closed this as not planned Won't fix, can't repro, duplicate, stale Nov 29, 2024
@stretyak
Copy link
Author

stretyak commented Dec 1, 2024

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 bgrainger reopened this Dec 1, 2024
@bgrainger
Copy link
Member

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
Copy link
Member

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.

bgrainger added a commit that referenced this issue Feb 2, 2025
@bgrainger
Read GUIDs from out parameters. Fixes #1528
62eb603 
Signed-off-by: Bradley Grainger <[email protected]>
@bgrainger bgrainger linked a pull request Feb 2, 2025 that will close this issue
Open
bgrainger added a commit that referenced this issue Feb 2, 2025
@bgrainger
Read GUIDs from out parameters. Fixes #1528
38f9bfe 
Signed-off-by: Bradley Grainger <[email protected]>
bgrainger added a commit that referenced this issue Feb 2, 2025
@bgrainger
Read GUIDs from out parameters. Fixes #1528
944ced6 
Signed-off-by: Bradley Grainger <[email protected]>
@bgrainger bgrainger self-assigned this Feb 2, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@bgrainger bgrainger

Labels
question
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

Read GUIDs from out parameters mysql-net/MySqlConnector
2 participants
@bgrainger @stretyak