This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

CommunicationsException tomcat6 and mysql

 
Michael Oberleitner
Greenhorn
Posts: 16
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello

I have a servlet application running on tomcat 6 and Ubuntu 10.04.1 LTS and mysql. Most of the time the application runs fine, but sometimes I get the following error when users try to login:

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 68.902.473 milliseconds ago. The last packet sent successfully to the server was 68.902.474 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.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:532)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1118)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3321)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1940)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2113)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2562)
at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:4956)
at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:366)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setAutoCommit(PoolingDataSource.java:328)
at com.inzeit.servlet.LoginServlet.checkCredentials(LoginServlet.java:48)
at com.inzeit.servlet.LoginServlet.doGet(LoginServlet.java:83)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
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:859)
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:3302)
... 22 more

This is the code:

private void checkCredentials(HttpServletRequest request, HttpServletResponse response)
{
String query = "SELECT role, user.id, name, firstName, email FROM user, employees, addresses WHERE userName = ? " +
"AND employees.id = user.employeeID AND employees.addressID = addresses.ID";

String loginName = request.getHeader("userName");
String pw = request.getHeader("passWord");
Date now = new Date();
try {
Context context = new InitialContext();
DataSource dataSource = (DataSource) context.lookup("java:comp/env/jdbc/mysqlDB");//Here the exception gets thrown
Connection connection = dataSource.getConnection();
connection.setAutoCommit(false);
PreparedStatement stmt = connection.prepareStatement(query);
stmt.setString(1, loginName);
ResultSet rslt = stmt.executeQuery();
ObjectOutputStream outstream = new ObjectOutputStream(response.getOutputStream());
while(rslt.next())
{
Editor editor = new Editor(rslt.getInt(2), loginName, rslt.getString(1), rslt.getString(3), rslt.getString(4), rslt.getString(5));
outstream.writeObject(editor);
}
outstream.close();
rslt.close();
stmt.close();
connection.close();
} catch (NamingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}

The exception occurs most of the time early in the morning when the application was not used for a longer time.
The users have to reconnect and after several times it works again. I checked the /var/log/error.log which was empty and the my.cnf from mysql and context.xml of tomcat, but I don't know which parameter to change.
If someone had similar problems and knows how to fix it i would appreciate.

Kind regards,
Changkowski
 
Michael Oberleitner
Greenhorn
Posts: 16
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I changed wait_timeout in mysql from 2800 to 28000 now and I will see if this helps for this problem.
When someone has other possible solutions or ideas i would be happy to here it.

Kind regards
 
Karthik Shiraly
Bartender
Pie
Posts: 1059
24
Android C++ Java Linux PHP Python
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
When mysql sees no traffic on its connections, it closes them. The timeout change will help but it's not the best solution - it's just delaying the problem.
To solve the problem, add "testOnBorrow=true" and "validationQuery=Select 1" as attributes to your JDBC pool resource. If there's been a communication shutdown due to timeout, these attributes ensure that DBCP reestablishes connections with DB automatically. Example here
 
Michael Oberleitner
Greenhorn
Posts: 16
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot Karthik Shiraly

I will try this soon.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic