Product: | Syniti Data Replication, DBMoto |
Version: | All |
ID: | 1668 |
Summary: | MySQL error when char(36) type is automatically used as Guid type |
The following error can occur while refreshing/mirroring from a MySQL source:
Refresh completed successfully, inserted 0 records (Replication: 'XXXXX- Target table: 'Test')
Err Error executing Resultset.Next while reading source data (Replication: 'XXXXX' - Source table: 'Test')
System.FormatException: Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).
at System.Guid.GuidResult.SetFailure(ParseFailureKind failure, String failureMessageID, Object failureMessageFormatArgument, String failureArgumentName, Exception innerException)
at System.Guid.TryParseGuidWithNoStyle(String guidString, GuidResult& result)
at System.Guid.TryParseGuid(String g, 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.NativeDriver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue valObject)
at MySql.Data.MySqlClient.ResultSet.ReadColumnData(Boolean outputParms)
at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlDataReader.Read()
at ln.hs()
at ca.a(qx A_0)
EXPLANATION
This problem occurs when using any .NET provider for 6.1.1 or later. By default, Connector/Net 6.1.1 and later automatically treat char(36) as a Guid type, and users have no option to disable the automatic mapping. It works well for users who are expecting a Guid type based on their business requirements. However, in some scenarios, users expect char(36) to be a normal string. If the column data is in an invalid Guid format, the MySqlDataReader.Read method stops by throwing a FormatException. For instance, the data can be the empty string even if the column has a NOT NULL constraint.
WORKAROUND
Set the property “Old Guids=true” to tell Connector/Net to treat binary(16) as Guid rather than any char type. Follow the steps below on how to do this from Syniti DR/DBMoto.
1. Stop the Syniti DR Replication Agent/DBMoto Data Replicator.
2. In the Metadata Explorer, select the MySQL source connection.
3. From the right mouse button menu, choose Connection Properties.
4. In the Connection Properties dialog, select the Connection property and click on the 3-dots button that appears on the right to open the Connection Properties dialog.
5. Scroll to the end of the property list and add “old guids=true” to the Extended Properties field.
6. Click OK and restart the Replication Agent/Data Replicator.