Unable to reload the tables due to '0000-00-00' datetime error

[Lina_Zhao]

After Qlik 2021.5 was applied, one of replicate servers failed to reload tables which contain DATETIME columns.

Error shows: Timestamp '0000-00-00 00:00:00.000000' is not recognized. 

The actual value in Oracle 19c is '01-JAN-10'. 

if we switch the task to direct connection, the tables are able to reload without any issues.

Source: Oracle 19c

Target: Snowflake

Thank you for your help

[Prabodh]

We have faced this in multiple versions: v6.6, v 7.0, v2021.5. It is super frustrating that Qlik is not able to explain why this happens and also not able to provide a concrete solution.

My observation has been that this happens when the source datatype is 'DATE' in Oracle and target datatype is timestamp. We are using logstream.

We have ensured that the 'bindDateAsBinary' parameter is not set in the Oracle source endpoint in both, logstream and the replication task.

Another issue we faced in v6.6 was that the date values were truncated to 18 characters. That is a problem as Oracle's Date datatype can also contain time values and the total length of the data in that case becomes 19 characters. Because of this problem Replicate was loading incorrect data in target. The problems are compounded is such column is part of PK and if 'apply changes' option is enabled. This issue is fixed in v7.0 and above.

To ensure both the problems mentioned above are truly resolved, we had to restart the logstream task with advanced start option from a timestamp. This forces Replicate to refresh the state of the task. 

We also reloaded all the tables by 'reloading' the replication task, as the data in Snowflake was bad because of issue #2.

[john_wang]

Hello @Lina_Zhao , @Prabodh ,

Please accept our apologies for any inconvenience.

You attached a task log file only instead of a diagnostics package  it's hard to tell the reason right now. Looks to me there are several doubts:

1. You mentioned "After Qlik 2021.5 was applied", was the task running fine before upgrade? what's the previous version please.

2. I cannot see Oracle client version from the task log file. it's a bit strange. Let me know what's the Oracle Client version.

3. The account you used to connect to the Oracle server lack of some privileges. see task log file line #147:

2021-10-19T21:39:46:901064 [SOURCE_UNLOAD   ]T:  The permission for 'select on dba_objects' is not granted

    Can you grant enough privilege to this account, or use a high privilege account to troubleshoot.

4. I noticed the UNLOAD SQL is (in log file line #202) 

2021-10-19T21:39:49:137324 [SOURCE_UNLOAD   ]T:  Select statement for UNLOAD is 'SELECT "ID","NAME",rawtohex( "CREATE_DATE" ),rawtohex( "UPDATE_DATE" ),"CREATED_BY","UPDATED_BY"  FROM "CDI"."ACCOUNT_STATUS"'

    The expression is rawtohex( "CREATE_DATE" ) , I do not understand why rawtohex() function is used here. Can you share the table creation DDL especially if it's TDE encrypted, and all the PK/Unique Index information, and it's supplemental logging information.

5. Can you try to run the below queries to see what's the DATE columns values :

SELECT "ID","NAME",rawtohex( "CREATE_DATE" ),rawtohex( "UPDATE_DATE" ),"CREATED_BY","UPDATED_BY"  FROM "CDI"."ACCOUNT_STATUS"

SELECT "ID","NAME","CREATE_DATE","UPDATE_DATE","CREATED_BY","UPDATED_BY"  FROM "CDI"."ACCOUNT_STATUS"

    And if any difference between the 2 queries results.

6. From line #19 :

2021-10-19T21:39:36:947108 [METADATA_MANAGE ]I:  ODBC driver version: '2.21.1'

    You are running an uncertified version of ODBC Client. You need ODBC driver 2.23.0 or above .

    Maybe this is not the root reason but please upgrade to supported ODBC Client version.

7. For troubleshooting purpose only, how about if you do not use GZIP in target endpoint. This is optional.

If the problem cannot be solved still, please send us above information,  keep the existing logging level and download the Diag Package, we are glad to analyze the issue for you.

Thank you,

John.

[john_wang]

Hello @Lina_Zhao ,

Please ignore (3.).

User Guide mentions that DBA_OBJECTS is required if the Oracle version is earlier than 11.2.0.3 . Your Oracle version is 19C however this message appears in TRACE level (it will not being printed in INFO level). You can safely ignore it.

Regards,

John.

[lyka]

Hi Lina,

Did you recently upgrade? We have seen cases wherein the metadata gets corrupted after an upgrade.

 the best way to fix the corruption is to resume both the parent and child tasks from the same timestamp. This should create a new timeline for the log stream task with new data files and metadata. The timestamp needs to be an exact match or the child task will not be able to resume.

Steps

1) Stop Child tasks

2) Stop Parent task

3) Start Parent task from a timestamp

4) Start Child tasks from the same timestamp

Hope this helps!

Thanks

Lyka