1657 - Converting a MySQL Zero Datetime

Unlike other relational databases, MySQL permits a zero value in Datetime fields: 0000-00-00 00:00:00. While it is not generally recommended to use a zero value in Datetime fields for replication, it is possible to handle this type of value using a script when performing a refresh (snapshot) replication to a different database.

The script converts the zero Datetime in MySQL to Null and inserts it into the target database. Note that this approach will not work if replicating in mirroring mode because once you set the Allow Zero Datetime option to True, the MySQL client will return the Datetime in a non-standard format. To work around this issue, you can either change all the zero Datetime fields to Null in your source table or set the Allow Zero Datetime option to True and use the above script to run the refresh, then stop the Data Replicator, and change the Allow Zero Datetime option to False. The mirroring should work as long as you do not insert 0 as a value in a Datetime field.

The steps below were tested with a Microsoft SQL Server target.

1. In the Syniti DR/DBMoto Management Center, expand the tree in the Metadata Explorer.
2. Right click on the MySQL connection and choose Connection Properties from the right mouse button menu.
3. In the Source Connection Properties dialog, select the Connection | MySQL.NET Driver field.
4. Click on the ... button in the field to open the Connection Properties for the MySQL Connector/Net.
   
   
5. Scroll down to the Optional section and locate the Allow Zero Datetime field.
6. Set the Allow Zero Datetime field value to True.
7. Click OK to close the MySQL Connection Properties dialog.
8. Click OK to close the Source Connection Properties dialog.
9. In the Metadata Explorer, right click the metadata name and select Global Script from the right mouse button menu.
10. Copy and paste the global script below into the Global Script Editor.
    
    Imports System
    Imports System.Data
    Imports Microsoft.VisualBasic
    Imports DBMotoPublic
    Imports DBMotoScript
    
    Namespace DBRS
      Public Class GlobalScript : Inherits IGlobalScript
        Public Shared Function AllowZero(obj as Object) as Object
          If obj is Nothing Then
            Return obj
          ElseIf not obj.IsValidDateTime Then
            Return DBNull.Value
          Else
            Return obj.GetDateTime()
          End If
        End Function
      End Class
      Public Class MappingRule : Inherits IMappingRule
      End Class
      Public Class GlobalEvents : Inherits IGlobalEvents
      End Class
    End Namespace
    
    
11. Click OK to compile the script and close the editor.
    
    
12. In the Metadata Explorer, right click the replication, and choose Replication Properties from the right mouse button menu.
13. In the Replication Properties dialog, click Mapping.
14. In the Fields Mapping dialog, right click the target SQL Server field with data type smalldatetime and select Map to Expression.
    
    
15. In the Expression Generator, type AllowZero([field_name]) where AllowZero is the name of the global script function and [field_name] is the name of the field you selected above.
    
    
16. Click OK
17. Start the Replication Agent/Data Replicator and run the replication.

The script will convert the 0 Datetime in the MySQL table to Null and insert it into the SQL Server target table.