topic Re: exception in component in Talend Studio

exception in component

Anonymous — Fri, 14 Jun 2013 02:52:51 GMT

Hi,
After few hours of running my job, an error message appeared as below :
Exception in component tMysqlOutput_1
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.
at sun.reflect.GeneratedConstructorAccessor3.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1014)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:988)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:974)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
at com.mysql.jdbc.StatementImpl.checkClosed(StatementImpl.java:465)
at com.mysql.jdbc.PreparedStatement.clearBatch(PreparedStatement.java:1143)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1471)
I did not have any idea regarding the error. Can anyone help me to clarify the matter.
Thanks in advance.
fadh

Re: exception in component

Anonymous — Fri, 14 Jun 2013 03:12:13 GMT

Hi,
I suspect something wrong with your MySQL server. As we known that, the "wait_timeout" is 8 hours in MySQL server by default. That's mean if the DB connection is last for 8 hours without operation, MySQL server will close this connection automatically. You can search this issue on MySQL official website.
Best regards
Sabrina

Re: exception in component

Anonymous — Fri, 14 Jun 2013 03:20:41 GMT

Hi Sabrina,
Thanks for the reply. Yes, the job did not finish even after 8 hours. The job that I run currently have 30 millions over rows. Do talend has any component which can deploy the job faster?
Thanks,
fadh

Re: exception in component

Anonymous — Fri, 14 Jun 2013 03:31:28 GMT

Hi,
What's the rate of your job? the work flow? which component are you using? Any screenshots will be appreciated.
Best regards
Sabrina

Re: exception in component

Anonymous — Fri, 14 Jun 2013 03:36:29 GMT

Hi,
I am using tMySqlInput and tMysqlOutput.
Below is the screenshot of the job.
thanks,
fadh

Re: exception in component

Anonymous — Fri, 14 Jun 2013 03:46:25 GMT

Hi,
I think the rate of "255.93 rows/s" is not normal. Did you set any sql query in tMySqlInput component? Migrate source table to target one?
Best regards
Sabrina

Re: exception in component

Anonymous — Fri, 14 Jun 2013 03:51:57 GMT

Hi,
Yes I set sql query statement in tMysqlInput component.
Thanks
fadh

Re: exception in component

Anonymous — Fri, 14 Jun 2013 04:02:20 GMT

Hi,
did you mean that I cannot set sql query in tMysqlInput component?
Thanks,
fadh

Re: exception in component

Anonymous — Fri, 14 Jun 2013 04:07:14 GMT

Hi,
You have to set a sql query in tMysqlInput. I meant it may be a complicated one, but it don't affect too much.
For a huge of data, did you check out the option "enable stream on tMysqlInput" and " Extend Insert on tMysqlOutput" which improve job performance.
Best regards
Sabrina

Re: exception in component

Anonymous — Fri, 14 Jun 2013 04:11:03 GMT

Hi,
Yes, I have already check out the option "enable stream on tMysqlInput" and " Extend Insert on tMysqlOutput" before I executed the job.
Thanks,
fadh

Re: exception in component

Anonymous — Fri, 14 Jun 2013 08:26:25 GMT

Hi,
Due to it is a huge of data, so it is great if your MySQL server support for bulk. Talend provide bulk components tMysqlBulkExec, tMysqlOutputBulk and tMysqlOutputBulkExec.
Or you can change MySQL timeout configuration.
Best regards
Sabrina

Re: exception in component

Anonymous — Sat, 14 May 2016 19:47:22 GMT

Please let me know how to increase time-out or is there any way so that my connection will not timeout even thought my Job is idle for more than 8 hour:
Exception in component tMysqlOutput_1
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:526)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.Util.getInstance(Util.java:384)

Re: exception in component

Anonymous — Mon, 16 May 2016 08:08:17 GMT

Hi praveen13,
Could you please try to investigate the MySQL server variable "wait_timeout" and increase it to a high value, rather than the default of 8 hours, open the configuration file mysq.ini/mysql.cnv and modify the following two parameters or add the following parameters if they do not exist.
wait_timeout=31536000
interactive_timeout=31536000
Let me know if it is OK with you.

Best regards
Sabrina

Re: exception in component

bhagyarekha — Tue, 01 Nov 2016 11:12:42 GMT

Hi xdshi,
Even am facing the same exception.But, i received this kind of error after 40 min of my job started.
may i know other chances of getting this kind of error.
Thanks in advance
Regards,
Rekha

Re: exception in component

Anonymous — Wed, 02 Nov 2016 08:24:58 GMT

Hi Rekha,
Could you please show us your current job setting screenshot?
Best regards
Sabrina

Re: exception in component

bhagyarekha — Thu, 03 Nov 2016 09:46:39 GMT

hi Xdshi,
please find the below settings for tmysqloutput

Re: exception in component

talendtester — Thu, 03 Nov 2016 16:26:41 GMT

Instead of tMysqlOutput use Instead of tMysqlOutputBulk.
I would guess you would see a speed improvement if you write everything to a delimited file before loading to the DB.