com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

[_AnonymousUser]

The issue is related to error “ com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed”
 In my job I am using a joblet in which I am using tMySQLOutput to write an entry into AUDIT_LOG table maintained in MySQL database.
 Overall the master job and intermediate subjobs calls the joblet for audit entries at different points within the JOB.
7 subjobs run in parallel (using tParallelize) and they all call the reusable Mysql joblet to insert entries into mysql database.
 
Everything runs fine with data set 1/2/3 data in development environment.
 Now the problem is when we are running the same code (talend jobs) in QA server it works fine and exit fine only for set 1/2 but not 3.

While running with set3 in QA environment , the job runs for 100-120 mins and then either ERROR out or just HANGS stating the error “ com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed”
 
Set         Size of input file data       Average Run Time of the job
1             20 MB                                 20 mins
2             50 MB                                 50 mins
3             250 MB                               120 mins
 
We have compared the wait_timeout parameters in both DEV and QA servers.
SHOW GLOBAL VARIABLES LIKE 'wait_timeout'; set to 28800 (8 hours)
 
1.       I am not sure if this issue is because of any settings differences between DEV/QA or we have a bug in our code.
2.       Also how to check or change MySQL related settings from on talend side (not MySQL DB level)
 
Any suggestions or help would be really helpful.
 
We have this job moving into PROD this week and because of this error everything is stand still.

[Anonymous]

Thats why I hate joblets. Try to redesign your job with normal embedded jobs and take care every job running in parallel uses its own database connection.

[_AnonymousUser]

Hi jlolling,
Thanks for getting back on this issue.
I am still having this issue.
I have tried writing using direct job components not via joblet but the problem only appears with larger dataset not with smaller dataset and that to in a specific server it seems , why ? Is there any JDBC parameters you can suggest I would try ?
When you said " take care every job running in parallel uses its own database connection" , what do you mean by own database connection.
My credentials are same , I am creating' tMySQLConn->tMySQLOutput->tMySQLClose'  in each subjobs , is that what you mean ?
This is issue is haunting me down day by day

[Anonymous]

Try googling it.
one suggestion is
MySQL implicitly closed the database connection because the connection has been inactive for too long (34,247,052 milliseconds ? 9.5 hours). If your program then fetches a bad connection from the connection-pool that causes the problem
Perhaps your connection timeout on QA is set low.
Are you using a shared db connection? Should probably not.

[_AnonymousUser]

Thanks janhess.
My process which takes time under 60 mins have no issues , comes out clean exit.
The ones run over 60-70 mins have this issue.
Checked both DEV and QA server for mysql parameters , they are same.
How can I Check " If your program then fetches a bad connection from the connection-pool that causes the problem" ?

[_AnonymousUser]

Hi jlloing,
I have 7 process running as inpendent subjob but controlled via tParalllize , 32GB RAM in linux box , 8 CORE CPU.
Any comments on deisign or risks ?

[_AnonymousUser]

I have found the root cause , not sure how to go around and fix it.
In DEV:
Job1->Job2->Job3 (Job1 calls Job2 calls Job3)
Job 3 : runs 5 jobs using tParallelize and then on sychronize all -> do some mongodb operation -> print some message on console and then finaly come out of Job 3 and go to Job 2 and prints success.
In QA
Job1->Job2->Job3 (Job1 calls Job2 calls Job3)
Job 3 : runs 5 jobs using tParallelize and then on sychronize all -> do some mongodb operation -> print some message on console and then **** DO NOT *** come out of Job 3 ****it just stays in last componenet of Job 3 doing NOTHING , HANGS******and 
after few hours starts erroring out with absurd mongo/mysql errors.
Any thoughts 
1.how to forcefully come out of a job/componenet gracefully?
2.how to forcefully come out of a job/componenet failing?