One Star

My job stops executing after just a few rows, but there are thousands

My job reads an Excel file and depending on various logic either updates or inserts records into several tables in a database.
After adding the middle stage (the one that outputs only to an Excel file for logging) to catch items with no "SOLid" but to try and match against names instead, the job just runs for a short amount of time and then stops running. I have never seen this before, what can the reason be? There is no error output either.
Thanks.
26 REPLIES
Community Manager

Re: My job stops executing after just a few rows, but there are thousands

Hi
There should be an error output on console once the job has an exception/error or hang for ever. Please check if there is a logic error in job design.
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Employee

Re: My job stops executing after just a few rows, but there are thousands

It could be a database concurrency issue... Does the job block or it crashes with a specific error ?
One Star

Re: My job stops executing after just a few rows, but there are thousands

The console shows no error message until I switch between Debug and Basic Run, then this suddenly shows up at the bottom:
Job SugarCRMInsertMySQL ended at 15:28 16/02/2011.
Column 'email_address' cannot be nullColumn 'email_address' cannot be nullColumn 'email_address' cannot be nullColumn 'email_address' cannot be null

How can I find out which schema or tMap to solve this in?
One Star

Re: My job stops executing after just a few rows, but there are thousands

So the problem seems to be in tMap_6 - I deleted that part and it now runs.
What I am trying to do in there is, before updating the two tables that handles email addresses, to tie the email address to a lead via a relation table called email_addr_bean_rel (the bean in this case being a lead, it's a SugarCRM thing). I have the lead id and the email address but I need to look up the email_address id first, which is what I try to do there - is there a better way to do this, maybe in the tMap_2 where I do all the other logic for the update?
One Star

Re: My job stops executing after just a few rows, but there are thousands

It could ba a database locking problem. I once had a similar issue where I was reading a table then applying updates and the job would hang. My only solution was to output the updated to a file and then update the database from thhe file.
One Star

Re: My job stops executing after just a few rows, but there are thousands

Thanks. This could be the case, maybe. I am writing to the leads table, then using it in a lookup later on in the flow. Could this be it?
One Star

Re: My job stops executing after just a few rows, but there are thousands

I notice in your job you write to leads in two places without a commit in between. Could be the cause of the locking.
One Star

Re: My job stops executing after just a few rows, but there are thousands

Ok, then the next question is how to insert a Commit in the middle of the workflow? I have only used the Commit component as a final stage of the flow.
One Star

Re: My job stops executing after just a few rows, but there are thousands

Test if this is the problem first. Disable the bottom half of the updates and run to see if everything is processed, then if ok test bottom half. If they both work then there is a conflict when running together. You may need to to all the top half then do an on subjob ok to run the bottom half.
One Star

Re: My job stops executing after just a few rows, but there are thousands

This might sound silly, but how do I disable a part of a job? I have been wishing for a "mute" function but didn't think there was one.
One Star

Re: My job stops executing after just a few rows, but there are thousands

right click on the component icon and select deactivate the component/subjob/completejob.
One Star

Re: My job stops executing after just a few rows, but there are thousands

Thank you!
One Star

Re: My job stops executing after just a few rows, but there are thousands

More of the same, I have added an inner join lookup on the email_addr_bean_rel table (marked with yellow in the picture) that seems to freeze the whole job. Any ideas why this would happen? There is a commit at the end of the flow (look to the left).
One Star

Re: My job stops executing after just a few rows, but there are thousands

I would really like to understand what stops an execution like this, and how to avoid it. The only thing I can think of is that a Commit is necessary somewhere in the middle of the flow, but is that even possible?
One Star

Re: My job stops executing after just a few rows, but there are thousands

Having read a bit more on this forum I found recommendations to set "Commit on every" to 1 instead of 10000 which I have done for all the tMysqlOutput components, but the job still freezes.

Re: My job stops executing after just a few rows, but there are thousands

This is most likely database locking. I suspect the "email_addresses" table you've circled is using a different session than the "email_addresses" table near the bottom of the job.
go through all of your DB input/outputs and make sure they are using an existing connection-- your tMysqlConnection_1
One Star

Re: My job stops executing after just a few rows, but there are thousands

Loks like you're trying to update a row that's already been updated in another component. Why not split your job into 3 processes.
One Star

Re: My job stops executing after just a few rows, but there are thousands

This is most likely database locking. I suspect the "email_addresses" table you've circled is using a different session than the "email_addresses" table near the bottom of the job.
go through all of your DB input/outputs and make sure they are using an existing connection-- your tMysqlConnection_1

Thank you, I will do this.
One Star

Re: My job stops executing after just a few rows, but there are thousands

Loks like you're trying to update a row that's already been updated in another component. Why not split your job into 3 processes.

When you say 3 processes, do you mean 3 separate jobs or 3 subjobs within the same job?
Also, only one of the rightmost update/insert options can occur for each record since it is the rejects that are being funneled "downstream" in the job.
One Star

Re: My job stops executing after just a few rows, but there are thousands

This is most likely database locking. I suspect the "email_addresses" table you've circled is using a different session than the "email_addresses" table near the bottom of the job.
go through all of your DB input/outputs and make sure they are using an existing connection-- your tMysqlConnection_1

I have done this now (and it made sense for all to use the same connection) but it didn't solve the problem.

Re: My job stops executing after just a few rows, but there are thousands

Start by disabling all of your DB outputs.
Then re-enable them in groups until you find the output that is causing the problem. From your screenshots, it looks like its one of the outputs in the "Starting" state that is causing the issue. I would start by disabling these two "clusters" of outputs and seeing if the job runs. Then re-enable them one by one until you find the problem.
Once you know which one it is, debugging the issue will be easier.
One Star

Re: My job stops executing after just a few rows, but there are thousands

Thank you. I agree this is the right method and I can clearly see that it is the cluster of outputs that handle the email and email-rel tables that cause the problem - there is a second tMap with a Lookup there to find out what the id of the related email address is, could this lookup itself be the problem? Let me show on a screenshot images of the job and the encircled tMap as well.

Re: My job stops executing after just a few rows, but there are thousands

that could definitely be the issue. You may be causing deadlocking
I would echo janhess' suggestion-- break the job into smaller parts. even if you need to write a staging table to store intermediate data-- your future self will thank you for simplifying the job(s).
Just as a general rule-of-thumb: When you need to update/insert to a lookup table, do that first and commit-- then use the lookup. Then instead of dealing with weird session/timing issues you can focus on getting the data itself right Smiley Wink
One Star

Re: My job stops executing after just a few rows, but there are thousands

Thanks, these are good tips. Smiley Happy
Is it possible to put a commit inbetween the "vertical levels" of my job so to speak, inbetween the tMaps? I am trying this now but getting this error message which tells me that I might have also shut down the main DB connection by doing the Commit - is this the case?
Exception in component tMysqlOutput_9
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ...

Re: My job stops executing after just a few rows, but there are thousands

There is a checkbox in the commit component to close the connection. I think its checked by default.
One Star

Re: My job stops executing after just a few rows, but there are thousands

Cheers!