One Star

exception in component

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
16 REPLIES
Moderator

Re: exception in component

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: exception in component

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
Moderator

Re: exception in component

Hi,
What's the rate of your job? the work flow? which component are you using? Any screenshots will be appreciated.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: exception in component

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

Re: exception in component

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: exception in component

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

Re: exception in component

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

Re: exception in component

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: exception in component

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
Moderator

Re: exception in component

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: exception in component

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)
Moderator

Re: exception in component

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seven Stars

Re: exception in component

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
Moderator

Re: exception in component

Hi Rekha,
Could you please show us your current job setting screenshot?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Seven Stars

Re: exception in component

hi Xdshi,
please find the below settings for tmysqloutput
Nine Stars

Re: exception in component

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.