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

[Anonymous]

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, T

To see the whole post, download it here

[Anonymous]

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

[Anonymous]

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();