I got a very weird problem. I’ve attached photos of my job in post so you can have a better idea of what the issue might be, don’t hesitate to ask me for more details as my job is particularly complex.
I have four jobs that will execute the same tasks but with different input data. 3 of them work perfectly while the fourth one (which is meant to be exactly the same) has an issue.
For each job, I’ve set a tStatCatcher, linked to a tDBOutput. To set the table that would receive the data from my tStatCatcher, I’ve used the MySQL code provided by Talend on their website to get exactly the same columns as my tStatCatcher, so I don’t even need to use a tMap for my stats. The problem is that in one of my jobs, everything runs perfectly but the Stats won’t upload in my DB, and I get the following error message, twice (I guess at the beginning and at the end of the job, when the tStatCatcher tries to get the current datetime) :
Data truncation: Incorrect datetime value: '' for column 'moment' at row 1
It seems quite weird, because if I put a tLogRow after my tDBOutput, it displays the stats,including the « moment ».
So I have a first operation (blue one) that extracts CSV data from a tFileInputDelimited to a database table (jira_loader).
When this step is over, the next one (pink one) is a translation of the datas freshly uploaded. I use a translation CSV file to convert my data to IDs that correspond to a parameters table in my DB. The translation file contains the infos to translate 6 columns, therefore it is included 6 times in my job, using joins with the main input in my tMap to indicate which input column has to be translated with which file data. I then map the translated data into a result table (and also in a histo_mvt table to keep archives of everything).
My last task is then initiated (green one). I want to write my data from result table into a tFileOutputJSON. I use the same method as before to re-translate my IDs into the value corresponding, which are contained in my param table, so again I put as much tDBInputs as I need to translate all of my columns. Just before that I also want to filter the data taken from the result table so I put a filter on the tstatcatcher table to extract only the data concerning this job and not the 3 other ones, and only the data where the message column equals « success » to exclude the failures. Then I put a tAggregateRow to select only the maximum « moment » data, so in the end I get the date of the last successful job. In my tMap4, I compare it with the dat of my input data using the following filter expression in my output :
TalendDate.compareDate(jira_result_output.modification_time_ticket ,AggregateRow_output.moment) == -1 && jira_result_output.id_customer == 1
I’ve spent a lot of time trying to solve the problem but I can’t find any explanation or spot any difference between my job that could be linked to that stat problem.
Thanks for your help !
What does your date format look like? What's Mysql DB version are you using?
We are using the DateTime "YYYY-MM-DD HH:MMS" format and our My SQL Database version is 5.7.17.
We stay available for more informations.
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Part 2 of a series on Context Variables
Learn how to do cool things with Context Variables
Find out how to migrate from one database to another using the Dynamic schema