Hi,
I'd like to get in touch with a MySQL expert about the following problem.
In SGMP (MEP) 1.1, we are getting the following error on attempting to
query a MySQL database
from a webservice (musicdb-ws) in GlassFish V2.1. It works initially,
but if you try the
query again after a long interval (over night), you get the error.
Afterward
the only way to get the report working again is to bounce GlassFish V2.1.
> PWC1406: Servlet.service() for servlet ServletAdaptor threw exception
> java.lang.RuntimeException: 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:1905) at
> com.mysql.jdbc.MysqlIO.reuseAndReadPacket(MysqlIO.java:2351) at
> com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2862) at
> com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571) at
> com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at
> com.mysql.jdbc.Connection.execSQL(Connection.java:2988) at
> com.mysql.jdbc.Connection.execSQL(Connection.java:2917) at
> com.mysql.jdbc.Statement.executeQuery(Statement.java:824) at
> com.sun.mep.ws.musicdb.report.MusicAlbumReportResource.runQuery(MusicAlbumReportResource.java:114)
> at
> com.sun.mep.ws.musicdb.report.MusicAlbumReportResource.getImage(MusicAlbumReportResource.java:76)
> at
This is a snippet of code from the application where the error is thrown
> sqlConnection =
> ConnectionPool.getInstance().getConnection("");
> stmt = sqlConnection.createStatement();
> ResultSet rs = stmt.executeQuery(query);
Attached is the util class that manages the SQL connection pool.
In GlassFish the default settings are in effect for the JDBC connection
pool used by the webservice.
Given MySQL's wait_timeout parameter is 8 hours (28800 secs), I tried
dynamically resetting it to a short interval (300 seconds) to see if the
problem would happen sooner, but the report query still worked after a
couple of hours.
Tom
/*
* Copyright 2008-2009 Sun Microsystems, Inc. All rights reserved.
*
* Use is subject to license terms.
*/
package com.sun.mep.ws.musicdb.util;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;
import javax.sql.DataSource;
import java.sql.Connection;
import javax.naming.Context;
import javax.naming.InitialContext;
/**
* Connection pool that holds SQL Connection objects. This
* class follows the singleton pattern and uses a concurrent
* hash map to keep track of connections. This pool uses
* the default (update) concurrency level defined by the
* <code>ConcurrentHashMap</code> class.
*
* @author Santiago.PericasGeertsen_at_sun.com
*/
public class ConnectionPool {
private static final String MUSICDB_JNDI_DATASOURCE = "jdbc/musicdb";
private static final String DB_USER_NAME = "musicdbuser";
private static final String DB_USER_PASS = "musicdbpass";
private static ConnectionPool INSTANCE = new ConnectionPool();
/**
* Singleton pattern method to return the instance.
*/
public static synchronized ConnectionPool getInstance() {
return INSTANCE;
}
/**
* Map between session IDs and connections.
*/
private final Map<String, Connection> pool;
/**
* Constructor that allocates concurrent map.
*/
protected ConnectionPool() {
pool = new ConcurrentHashMap<String, Connection>();
createConnection(""); // for testing using default session ID
}
/**
* Creates a new connection and stores it in the pool.
*/
public Connection createConnection(String sessionId) {
try {
Context jndiContext = new InitialContext();
DataSource ds =
(DataSource) jndiContext.lookup(MUSICDB_JNDI_DATASOURCE);
Connection sqlConnection =
(Connection) ds.getConnection(DB_USER_NAME, DB_USER_PASS);
pool.put(sessionId, sqlConnection);
return sqlConnection;
}
catch (Exception ex) {
throw new RuntimeException(ex);
}
}
/**
* Gets a previously created connection.
*/
public Connection getConnection(String sessionId) {
try {
Connection c = pool.get(sessionId);
return c.isClosed() ? createConnection(sessionId) : c;
}
catch (Exception ex) {
throw new RuntimeException(ex);
}
}
/**
* Destroys and existing connection by closing it and removing
* it from the pool.
*/
public void destroyConnection(String sessionId) {
try {
Connection sqlConnection = pool.get(sessionId);
if (sqlConnection != null) {
sqlConnection.close();
pool.remove(sqlConnection);
}
}
catch (Exception ex) {
throw new RuntimeException(ex);
}
}
}