Qlik Replicate and MySQL source: How to handle zero date

john_wang — Mon, 25 Sep 2023 12:03:41 GMT

MySQL allows the zero date (and/or zero month) in data rows, which is a specific behaviour of MySQL and does not exist in most other RDBMS. This behaviour causes invalid date errors while replicating data to heterogeneous databases.

In this article, we will explain the solutions in both Full Load and CDC stages.

Resolution

To handle zero date in Qlik Replicate for both Full Load and CDC stages:

How to handle zero date in Full Load stage

MySQL provides an ODBC parameter ZERO_DATE_TO_MIN to control this behavior. please take note MySQL adds one day for zero date values.

Open MySQL source endpoint
Go to the Advanced tab
Go to the Additional ODBC connection properties
Add below connection string in the input label:

ZERO_DATE_TO_MIN=1;

Based on this setting, the Full Load stage gets a non-zero date. For example, the source value 2022-12-00 in MySQL will become 2022-12-01 in the target endpoint after the Full Load is finished.

How to handle zero date in CDC stage

The Full Load steps will not have an impact on the CDC stage. That means the source value 2022-12-00 will be 2022-12-00 still in the CDC stage. To mitigate this, we need to create a transformation to replace the zero date 00 with 01 as we do in the Full Load stage.

The simple global transformation expression can be:

date(substr($AR_M_SOURCE_COLUMN_DATA,1,8)||replace(substr($AR_M_SOURCE_COLUMN_DATA,9,2),'00','01'))

For more information on how to set transformations, see: Starting the Global Transformation Rules wizard.

Environment

all versions
MySQL all versions

Internal Investigation ID(s)

Support case #00107317