Broken pipe error when trying to connect to a MySQL database.

Five Stars

Broken pipe error when trying to connect to a MySQL database.

Hi,
We started having a broken pipe issue this past Saturday with a job that gets run via a quartz scheduler during the early morning of each day. Below are some details on this.
The job consists of a native java class that calls Talend based jobs. Each of these Talend based jobs are getting the broken pipe error. Curious enough, when I run the same java class that gets submitted via the scheduler, no errors get thrown and the jobs run successfully. Also, this job stream runs in Tomcat.
Is there something I should do within the Talend jobs to see if a connection is available first? As you will see in the following jdbc parameters used for the tMysqlConnection component, the autoreconnect option is set to on.
jdbc parameters -> "noDatetimeStringSync=true&connectTimeout=0&socketTimeout=0&autoReconnect=true"
Below is the stacktrace from this morning's job failure.
Any input is greatly appreciated. Thank you.
Regards,
Tom
############################################################
Exception in component tMysqlConnection_1
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 344,265,715 milliseconds ago. The last packet sent successfully to the server was 344,265,792 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
at sun.reflect.GeneratedConstructorAccessor840.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1117)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3871)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2484)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2788)
at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5282)
at umaxbalancing_etl.csxdemand_masterservice_0_1.CSXDemand_MasterService.tMysqlConnection_1Process(CSXDemand_MasterService.java:2381)
at umaxbalancing_etl.csxdemand_masterservice_0_1.CSXDemand_MasterService.tFileList_2Process(CSXDemand_MasterService.java:2284)
at umaxbalancing_etl.csxdemand_masterservice_0_1.CSXDemand_MasterService.tFileExist_1Process(CSXDemand_MasterService.java:1146)
at umaxbalancing_etl.csxdemand_masterservice_0_1.CSXDemand_MasterService.tJava_2Process(CSXDemand_MasterService.java:1054)
at umaxbalancing_etl.csxdemand_masterservice_0_1.CSXDemand_MasterService.tJava_1Process(CSXDemand_MasterService.java:972)
at umaxbalancing_etl.csxdemand_masterservice_0_1.CSXDemand_MasterService.runJobInTOS(CSXDemand_MasterService.java:4370)
at umaxbalancing_etl.csxdemand_masterservice_0_1.CSXDemand_MasterService.runJob(CSXDemand_MasterService.java:4079)
at com.rez1.dws.mid.etl.engine.extract.talend.RunTalendDailyJobs.runCSXDemandExtract(RunTalendDailyJobs.java:45)
at com.rez1.dws.mid.etl.engine.extract.talend.RunTalendDailyJobs.dailyJobs(RunTalendDailyJobs.java:28)
at com.rez1.dws.mid.etl.engine.master.service.DWMasterETLServiceImpl.processTalendDailyJobs(DWMasterETLServiceImpl.java:401)
at com.rez1.dws.mid.etl.engine.master.service.DWMasterETLServiceImpl$$FastClassByCGLIB$$13fc5e62.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:91)
at com.rez1.common.processmonitor.ProcessMonitor.handleNestedInvocation(ProcessMonitor.java:440)
at com.rez1.common.processmonitor.ProcessMonitor.handleJoinPoint(ProcessMonitor.java:404)
at com.rez1.op.core.performance.aspect.OperationalProgressMonitorAspect.operationalMonitor(OperationalProgressMonitorAspect.java:66)
at sun.reflect.GeneratedMethodAccessor36.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:161)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:91)
at com.rez1.dws.mid.etl.engine.aspect.DWMasterETLExceptionMonitorAspect.monitorForExceptions(DWMasterETLExceptionMonitorAspect.java:44)
at sun.reflect.GeneratedMethodAccessor57.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at com.rez1.dws.mid.etl.engine.master.service.DWMasterETLServiceImpl$$EnhancerByCGLIB$$ca0f48f4.processTalendDailyJobs(<generated>)
at com.rez1.apipublish.dws.facade.DataWarehouseFacadeImpl.talendDailyJob(DataWarehouseFacadeImpl.java:169)
at com.rez1.apipublish.dws.facade.DataWarehouseFacadeImpl$$FastClassByCGLIB$$fcc756cf.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:149)
at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:688)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:91)
at com.rez1.common.processmonitor.ProcessMonitor.handleFirstInvocation(ProcessMonitor.java:369)
at com.rez1.common.processmonitor.ProcessMonitor.handleJoinPoint(ProcessMonitor.java:402)
at com.rez1.op.core.performance.aspect.OperationalProgressMonitorAspect.operationalMonitor(OperationalProgressMonitorAspect.java:66)
at sun.reflect.GeneratedMethodAccessor36.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:161)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:89)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:621)
at com.rez1.apipublish.dws.facade.DataWarehouseFacadeImpl$$EnhancerByCGLIB$$521e5519.talendDailyJob(<generated>)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:309)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
at org.springframework.remoting.support.RemoteInvocationTraceInterceptor.invoke(RemoteInvocationTraceInterceptor.java:77)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
at $Proxy75.talendDailyJob(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
at org.springframework.remoting.support.RemoteInvocation.invoke(RemoteInvocation.java:205)
at org.springframework.remoting.support.DefaultRemoteInvocationExecutor.invoke(DefaultRemoteInvocationExecutor.java:38)
at org.springframework.remoting.support.RemoteInvocationBasedExporter.invoke(RemoteInvocationBasedExporter.java:78)
at org.springframework.remoting.support.RemoteInvocationBasedExporter.invokeAndCreateResult(RemoteInvocationBasedExporter.java:114)
at org.springframework.remoting.httpinvoker.HttpInvokerServiceExporter.handleRequest(HttpInvokerServiceExporter.java:73)
at org.springframework.web.servlet.mvc.HttpRequestHandlerAdapter.handle(HttpRequestHandlerAdapter.java:49)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:790)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719)
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644)
at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:852)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:636)
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite0(Native Method)
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3852)
Community Manager

Re: Broken pipe error when trying to connect to a MySQL database.

Hi tgrady
The parameter autoReconnect=true is not recommended, see Mysql official document. Alternatively, as suggested, 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
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Five Stars

Re: Broken pipe error when trying to connect to a MySQL database.

Hi Shong,
Thank you for your feedback. I too came across information in regards to the autoReconnect parameter and thanks for sharing that. I was able to fix my issue (see below) and am going to shy away from extending the waitTimeout variable - we do not want to keep connections around for that long.
I was able to address the broken pipe error... There is native java code (within a tJava component) used in a job that reads a table for a row count. I incorrectly assumed that the connection would close correctly after the job ran. Adding the commit and close for the connection should have been included in my initial code. It should be
noted that the commit on the connection is required even though only a read is taking place. My closing the
connection without having a commit did not address the issue, it was only by adding the commit did the problem go away. In hindsight this makes sense.

// code snippet from the tJava component using native java to read a table.
java.sql.Connection tableConnection = null;
tableConnection = (java.sql.Connection) globalMap.get("conn_tMysqlConnection_1");
Statement selectStatement = tableConnection.createStatement();
// Execute statement to get the row count from the table.
ResultSet resultSet = selectStatement.executeQuery("select count(*) from tableA");
// Position the result set to allow for access of its information.
resultSet.next();
// Get the row count from the result set.
context.numberOfRows = resultSet.getInt(1);
resultSet.close();
selectStatement.close();
tableConnection.commit();
tableConnection.close();