topic Re: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException in Talend Studio

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

_AnonymousUser — Sat, 16 Nov 2024 10:48:04 GMT

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.

Re: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

Anonymous — Tue, 16 Feb 2016 23:01:53 GMT

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.

Re: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

_AnonymousUser — Mon, 22 Feb 2016 14:30:44 GMT

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

Re: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

Anonymous — Mon, 22 Feb 2016 14:42:02 GMT

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.

Re: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

_AnonymousUser — Mon, 22 Feb 2016 18:47:03 GMT

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" ?

Re: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

_AnonymousUser — Tue, 23 Feb 2016 14:12:51 GMT

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 ?

Re: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

_AnonymousUser — Wed, 24 Feb 2016 07:24:26 GMT

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?