Hi Tom,
* Application code seems to be pooling the connections. Since you
retrieve the connections from Appserver's connection pool you need not
pool the connections.
* For the failures, do you have connection validation for the
jdbc-connection-pool enabled ? You can enable validation, set
"validation-type" to be table, "validation-table-name" to an existing
table. This will ensure that stale connections are removed.
http://blogs.sun.com/JagadishPrasath/entry/connection_validation_in_glassfish_jdbc
Thanks,
-Jagadish
On Fri, 2009-02-27 at 14:17 -0500, Tom Amiro wrote:
> 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
> plain text document attachment (ConnectionPool.java)
> /*
> * 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);
> }
> }
>
> }
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: dev-unsubscribe_at_glassfish.dev.java.net
> For additional commands, e-mail: dev-help_at_glassfish.dev.java.net