Tomcat MySQL connection pooling error java.io.EOFException
Posted by: Martin O'Shea
Date: September 30, 2009 03:54AM

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. 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.



Edited 1 time(s). Last edit at 09/30/2009 03:55AM by Martin O'Shea.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.