Environment: MySQL Server 8.0, .NET Core 3.1, MySql.Data 8.0.28, NHibernate 5.3.11
I have following table:
CREATE TABLE `Master` (
`Row_Id` char(36) NOT NULL DEFAULT (uuid()),
`Path` varchar(1000) NOT NULL,
PRIMARY KEY (`Row_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Following is entity definition and mapping:
public class MasterEntity
{
public virtual Guid RowId { get; set; }
public virtual string Path { get; set; }
}
internal sealed class MasterMap : ClassMapping<MasterEntity>
{
public MasterMap()
{
Table("Master");
Id
(
x => x.RowId,
map =>
{
map.Column("Row_Id");
map.Generator(Generators.GuidComb);
}
);
Property(x => x.Path, map => { map.Column("Path"); map.NotNullable(true); map.Type(TypeFactory.GetAnsiStringType(1000)); });
}
}
Following is how I INSERT
this entity using NHibernate:
using(ISession session = SessionFactory.OpenSession())
{
MasterEntity entity = new MasterEntity();
entity.Path = "c:\whatever";
session.Save(entity);
session.Flush();
}
This inserts the record correctly. Up to here, everything is fine.
Now, I change the definition of Row_Id
column as below:
`Row_Id` binary(16) NOT NULL DEFAULT (uuid_to_bin(uuid(),1)),
I do not change anything in my C# code. Now, the session.Flush();
call throws below exception:
NHibernate.Exceptions.GenericADOException: could not execute batch command.[SQL: SQL not available]
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Data too long for column 'Row_Id' at row 1
The error looks obvious. The Guid
in C# is 32 and column length is 16.
What changes I need to make in my mapping or entity definition (or other part of code) to insert C# Guid
into BINARY(16) DEFAULT (uuid_to_bin(uuid(),1))
column?
By default, MySql.Data will store a Guid
as CHAR(36)
. You can use BINARY(16)
instead by specifying Old Guids = True;
in your connection string.
From Connector/NET 8.0 Connection Options Reference:
The back-end representation of a GUID type was changed from
BINARY(16)
toCHAR(36)
. This was done to allow developers to use the server functionUUID()
to populate a GUID table -UUID()
generates a 36-character string. Developers of older applications can add'Old Guids=true'
to the connection string to use a GUID of data typeBINARY(16)
.