Changing testOnBorrow = "true" has not changed the situation with the
connection pool failing -can anyone suggest anything?
Thanks.
Martin.
MartinOShea wrote:
>
> 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-tp25677820p25693459.html
Sent from the Tomcat - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
