Tomcat 6.X and MySQL connection pooling issue

[MartinOShea]

Hello

I wonder if anyone can advise me on this issue. I have a Tomcat 6.X Java /
JSP application which uses connection pooling to access a MySQL database
but, if the application is left for up to eight hours, one of the pages
fails to display the contents of a dataset upon loading. 

Looking into the logs, I find that I have the following:

ERROR|29 09 2009|08 42
19|http-8080-4|myDataSharer.database_access.Database_Metadata_DBA| - Error
getting types of columns of tabular Dataset 12

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
myDataSharer.database_access.Database_Metadata_DBA.getTabDSColumnTypes(Database_Metadata_DBA.java:364)
        at
myDataSharer.database_access.Dataset_DBA.getTabDSAsHTMLTable(Dataset_DBA.java:1266)

And so on for several hundred lines.

Now the page in question is meant to display data from one of a number of
tables in MySQL which vary in their column types. Each table forms a dataset
which is created from data extracted from RSS feeds or an Excel / CSV file
uploaded by a user, hence the varying table column types. 

So, in the absence of defined object types, what the application does is to
look up the table's column types in a query
(Database_Metadata_DBA.getTabDSColumnTypes) and then use the output from
this to build a HTML table 
(Dataset_DBA.getTabDSAsHTMLTable) which is returned to the servlet for
displaying in a JSP. Each database operation uses code similar to the
example below and these work perfectly normally. But I think what is
happening after eight hours of inactivity, is that the system is trying to
reuse a connection pool object that MySQL has closed down after its default
eight hour period.

But I can't seem to resolve this problem and I wonder if anyone can advise?

The relevant part of my application's context.xml file is:

name = "jdbc/myDataSharer"
    auth = "Container"
    maxActive = "100"
    maxIdle = "30"
    maxWait = "10000"
    username = "XXXX"
    password = "XXXXXXXXXX"
    driverClassName = "com.mysql.jdbc.Driver"
    url = "jdbc:mysql://XXXXXXXXXX:XXXX/myDataSharer?autoReconnect=true"
    logAbandoned = "true"
    minEvictableIdleTimeMillis = "30000"
    numTestsPerEvictionRun = "5"
    removeAbandoned = "true"
    removeAbandonedTimeout = "120"
    testOnBorrow = "false"
    testOnReturn = "false"
    testWhileIdle = "true"
    timeBetweenEvictionRunsMillis = "60000"
    type = "javax.sql.DataSource"
    validationQuery = "select now()" />

And my application uses servlets which generally have several database IO
operations each of the form :

public static Dataset getDataset(int DatasetNo) 
{    
ConnectionPool_DBA pool = ConnectionPool_DBA.getInstance();    
Connection connection = pool.getConnection();    
PreparedStatement ps = null;    
ResultSet rs = null;    
String query = ("SELECT * " +                   
"FROM Dataset " +                    
"WHERE DatasetNo = ?;");    
try {        
ps = connection.prepareStatement(query);        
ps.setInt(1, DatasetNo);        
rs = ps.executeQuery();        
if (rs.next()) 
{            
Dataset d = new Dataset();            
d.setDatasetNo(rs.getInt("DatasetNo"));            
d.setDatasetName(rs.getString("DatasetName"));            
...            
}            
return d;        
}        
else {            
return null;        
}    
}    
catch(Exception ex) 
{        
logger.error("Error getting Dataset " + DatasetNo + "\n", ex);                  
  
return null;    
}    
finally 
{        
DatabaseUtils.closeResultSet(rs);        
DatabaseUtils.closePreparedStatement(ps);        
pool.freeConnection(connection);    
}

Where class ConnectionPool_DBA is my own DBCP-based class. 

Apologies for the length of this message but any help would be much
appreciated. Is there a MySQL setting which should be changed at all?

Thanks

Martin O'Shea.
-- 
View this message in context: 
http://www.nabble.com/Tomcat-6.X-and-MySQL-connection-pooling-issue-tp25677820p25677820.html
Sent from the Tomcat - User mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org
For additional commands, e-mail: users-h...@tomcat.apache.org