After migration to 8.0.1 dates are 1 day before

[AIjel]

Hello all,

I recently migrated to Open Data Integration 8.0.1 from 6.4.1

I have a mysql 5 database

when i run my process all my dates are 1 day before what is expected!

I tried to use UTC on both jdbc connection (

serverTimezone=UTC)

and mysql server (default-time-zone='+00:00') but that didn't fix the issue.

It seems it is an compatibility issue with mysql 8 jdbc driver. Does anyone faced this issue?

here is my JDBC connection:

jdbc:mysql://localhost:3306/<myTable>?noDatetimeStringSync=true&allowMultiQueries=true&useJDBCCompliantTimezoneShift=true&useLegacyDatetimeCode=false&serverTimezone=UTC

any idea how to solve this issue?

thanks

Ammar

[Anonymous]

Hello,

We are supposing that it may be due to daylights savings time

Could you also try the solution: Open the Run Job viewer, click on 'Advanced settings' panel and check 'use JVM parameter' box, add the following parameter.

-Duser.timezone=UTC

 Feel free to let me know if it works.

Best regards

Sabrina

[AIjel]

Hello,

Yes I already did that yesterday after reading some posts but result still the same. I am still having the issue.

just in case, here are the MySql drivers i see in Talend!

 

[AIjel]

in fact i did that in .ini file:

-vm

C:\Program Files\Java\msjdk_11.0.15\bin\javaw.exe

-vmargs

-Xms4096m

-Xmx16384m

-Dfile.encoding=UTF-8

-Dosgi.requiredJavaVersion=11

-Duser.timezone=UTC

-XX:+UseG1GC

-XX:+UseStringDeduplication

-XX:MaxMetaspaceSize=512m

--add-opens

java.base/java.lang=ALL-UNNAMED

--add-opens

java.base/java.util=ALL-UNNAMED

--add-modules=ALL-SYSTEM

Just in case, I am using open jdk 11.0.15

[Anonymous]

Hello,

Does this issue repro on Mysql 5 or Mysql 8? Or both?

What does your job design look like? Mysql 5 and Mysql 8 are both in one job?

Best regards

Sabrina

[AIjel]

It is about MySQL 5 database​

[AIjel]

And data source file is a CSV ​

[Anonymous]

Hello,

We had resolved a similar daylights savings time issue before via setting it -Duster.timezone=EST

Here is an article about:

https://stackoverflow.com/questions/7605953/how-to-change-mysql-timezone-in-a-database-connection-us...

Let us know if it helps.

Best regards

Sabrina

[AIjel]

as you can see, i am already using 'useLegacyDatetimeCode=false' in my URL. i see nothing else in the topic. am i missing something?

[AIjel]

Hello,

I found the issue! it seems to be an issue in Talend.

in fact, when i run the process directly, dates are correct. However, when i run the process from a main as a sub process using tRunJob dates are not correct!

any explanation?

here is a test process

run directly here is the output of the 2 logs from the csv and from the database

.-------------+--------------+------------.

|        tLogRow_1        |

|=------------+--------------+-----------=|

|sales_doc_nbr|hdr_start_date|hdr_end_date|

|=------------+--------------+-----------=|

|2136379907  |01/01/2017  |12/31/2020 |

'-------------+--------------+------------'

.-------------+--------------+------------.

|        tLogRow_3        |

|=------------+--------------+-----------=|

|sales_doc_nbr|hdr_start_date|hdr_end_date|

|=------------+--------------+-----------=|

|2136379907  |01-01-2017  |31-12-2020 |

'-------------+--------------+------------'

however, when i run the process from a main process

here is what i get

.-------------+--------------+------------.

|        tLogRow_1        |

|=------------+--------------+-----------=|

|sales_doc_nbr|hdr_start_date|hdr_end_date|

|=------------+--------------+-----------=|

|2136379907  |01/01/2017  |12/31/2020 |

'-------------+--------------+------------'

.-------------+--------------+------------.

|        tLogRow_3        |

|=------------+--------------+-----------=|

|sales_doc_nbr|hdr_start_date|hdr_end_date|

|=------------+--------------+-----------=|

|2136379907  |31-12-2016  |30-12-2020 |

'-------------+--------------+------------'

now, if i check 'Use an independent process to run subjob' i main

 

the output is correct

.-------------+--------------+------------.

|        tLogRow_1        |

|=------------+--------------+-----------=|

|sales_doc_nbr|hdr_start_date|hdr_end_date|

|=------------+--------------+-----------=|

|2136379907  |01/01/2017  |12/31/2020 |

'-------------+--------------+------------'

.-------------+--------------+------------.

|        tLogRow_3        |

|=------------+--------------+-----------=|

|sales_doc_nbr|hdr_start_date|hdr_end_date|

|=------------+--------------+-----------=|

|2136379907  |01-01-2017  |31-12-2020 |

'-------------+--------------+------------'

any idea what's happening here?

Attached the process and the input csv file

thanks

Ammar