3

My webapp is running on Tomcat 5.5, I declared the datasource in web.xml:

<resource-ref>
    <res-ref-name>jdbc/OrdiniWebDS</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
    <res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>

In context.xml (tomcat conf):

<Resource 
  auth="Container" 
  driverClassName="com.mysql.jdbc.Driver" 
  maxActive="100" 
  maxIdle="30" 
  maxWait="10000" 
  name="jdbc/OrdiniWebDS" 
  password="[mypassword]" 
  type="javax.sql.DataSource" 
  url="jdbc:mysql://[myHost:port]/ordiniweb" 
  username="[myusername]"
 />

The database is a MySql 5.0. Everything works well except that sometimes, after several hours of "unuse", at first access I got this Exception:

com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception: 

** BEGIN NESTED EXCEPTION ** 

java.io.EOFException

STACKTRACE:

java.io.EOFException
 at com.mysql.jdbc.MysqlIO.readFully(MysqlIO.java:1956)
 at com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2368)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2867)
 at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
 at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
 at com.mysql.jdbc.Connection.execSQL(Connection.java:3255)
 at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1293)
 at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1428)
 at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
 at com.blasetti.ordiniweb.dao.OrdiniDAO.caricaOrdine(OrdiniDAO.java:263)
...
** END NESTED EXCEPTION **



Last packet sent to the server was 0 ms ago.
 com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2579)
 com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2867)
 com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1616)
 com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1708)
 com.mysql.jdbc.Connection.execSQL(Connection.java:3255)
 com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1293)
 com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1428)
 org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
 com.blasetti.ordiniweb.dao.OrdiniDAO.caricaOrdine(OrdiniDAO.java:263)
...

I need to refresh and it works well again. Any idea? Thanks.

mauretto
  • 3,183
  • 3
  • 27
  • 28

1 Answers1

9

MySQL drops unused connections after a while because it assumes that the other side forgot to close it.

What you need to do is to configure a check that Tomcat should apply before it reuses a pooled connection. To do this, add ?autoReconnect=true&useUnicode=true&characterEncoding=utf8 to the end of the URL and add validationQuery="Select 1" to the Resource element:

<Resource 
  auth="Container" 
  driverClassName="com.mysql.jdbc.Driver" 
  maxActive="100" 
  maxIdle="30" 
  maxWait="10000" 
  name="jdbc/OrdiniWebDS" 
  password="[mypassword]" 
  type="javax.sql.DataSource" 
  url="jdbc:mysql://[myHost:port]/ordiniweb?autoReconnect=true&useUnicode=true&characterEncoding=utf8" 
  username="[myusername]"
  validationQuery="Select 1"
 />

[EDIT] This page gives more details: Configuring a MySQL Datasource in Apache Tomcat

vishless
  • 829
  • 1
  • 5
  • 28
Aaron Digulla
  • 321,842
  • 108
  • 597
  • 820
  • Sounds interesting, I'll try for sure. Reading the page you linked I think I don't need of 'useUnicode' and 'characterEncoding' params. Thanks, I'll update you. – mauretto Dec 14 '10 at 08:33
  • Thanks Aaron, your answer helped a lot, can you please also suggest how to do it in case of hibernate – Yashpal Singla Dec 11 '12 at 07:33
  • @YashpalSingla: You will need to connect to your database using a connection pool like c3p0 or dbcp. – Aaron Digulla Dec 17 '12 at 11:35
  • @AaronDigulla thanks a ton! you sir have saved my day. I had similar problem with DBCP and Hibernate. autoReconnect=true saved me. – Yogesh Jun 26 '14 at 05:28
  • 1
    `?autoReconnect=true&useUnicode=true&characterEncoding=utf8` ... Please note that if you want *real* UTF8 (which is [max. 4 bytes](https://stijndewitt.wordpress.com/2014/08/09/max-bytes-in-a-utf-8-char/) instead of *mysql* 'UTF8' which is [max. 3 bytes](https://dev.mysql.com/doc/refman/5.5/en/charset-unicode-utf8mb4.html) and can't encode all of Unicode) you would need to specify `?autoReconnect=true&useUnicode=true&characterEncoding=utf8mb4` – Stijn de Witt Jun 15 '15 at 09:59
  • @AaronDigulla, so wiull hibernate.autoReconnect =True work with c3p0.....i am also facing same issue my sql throws a communicate link error when it wake from sleep...i am using c3p0 driver – ronit May 24 '21 at 09:21