Question about closing a glassfish jdbc connection pool connection

Hello,
I have a database handler called glassfishDB, it handles database queries for a servlet I wrote. Anyways, Ive been noticing that after awhile the servlet stops responding, so started to debug and came up with this:

----Log File Rotated---
RAR5117 : Failed to obtain/create connection from connection pool [ tracking ]. Reason : In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections.
RAR5114 : Error allocating connection : [Error in allocating a connection. Cause: In-use connections equal max-pool-size and expired max-wait-time. Cannot allocate more connections.]
Database Close Error: java.lang.NullPointerException
StandardWrapperValve[xmlDataServices]: PWC1406: Servlet.service() for servlet xmlDataServices threw exception
java.lang.NullPointerException
        at xmlDataServices.getServerID(xmlDataServices.java:170)
        at xmlDataServices.processRequest(xmlDataServices.java:46)
        at xmlDataServices.doPost(xmlDataServices.java:331)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:738)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
        at org.apache.catalina.core.ApplicationFilterChain.servletService(ApplicationFilterChain.java:411)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:317)

which obviously indicates that the servlet cant run because it cant get a database connection. So I went back to my servlet and glassfishDB class, and started poking around. In the servlet, all the db calls are closed when they are done being used - so that was one issue out of the way, the only other thing I can come up with is that I am not closing the connections down properly. Can someone please tell me how to fix this error? The code for my glassfishDB is below and here is an example of its usage:

glassfishDB db = new glassfishDB("jdbc/tracking_pool");
db.executeQuery("select * from sometable");
db.close();

or the same thing but with an update you dont need to close, as it closes automatically:

db.executeUpdate("update sometable set somecolumn = 'somevalue'");


Worth 500 points.

Thanks,
Rick
/*
 * Database.java
 *
 * Created on November 1, 2006, 1:28 AM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */
 
 
 
//Database Connectivity - Requires ODBC
import java.util.Hashtable;
import javax.sql.*;
import java.sql.*;
import javax.naming.*;
 
public class glassfishDB {
	Statement stmt;
	ResultSet rs;
	ResultSetMetaData meta;
	Connection con;
	long numberofrows = -1;
        int errorState = 0;
        String errorMessage = "";
        String poolname = "";
        
	glassfishDB(String poolName)
	{
            this.poolname = poolName;
	}
 
        public static DataSource getDataSource(String dsName) throws NamingException 
        {
            InitialContext context = null;
            context = new InitialContext();
            DataSource dataSource = (DataSource) context.lookup("jdbc/" + dsName);
            return dataSource;
        }
        
	void executeQuery(String query)
	{
            try 
            {
                DataSource ds = getDataSource(poolname);
		con = ds.getConnection(); 
                stmt = con.createStatement();
                // Do db operations.
                // Do not close driver connection
		rs = stmt.executeQuery(query);
		rs.setFetchSize(1); 
		meta = rs.getMetaData();
	    }
	    catch (Exception e)
	    {
                //System.out.println("Database Connection Error:" + e.toString());
                errorMessage = e.toString();
                errorState = 1;
	    }
	}
 
	void executeUpdate(String query)
	{
            try 
            {
                DataSource ds = getDataSource(poolname);
                con = ds.getConnection();
                stmt = con.createStatement();
		stmt.executeUpdate(query);
                con.close();
            }
	    catch (Exception e)
	    {
		//System.out.println("Database Connection Error:" + e.toString());
                errorMessage = e.toString();
                errorState = 2;
            }
	}
 
	long getRowCount()
	{
		try
		{
			int start = rs.getRow();
			rs.last();
			int last = rs.getRow();
 
			rs.first();
 
			numberofrows = last - start;
		}
		catch(Exception e)
		{
			//System.out.println("Database getRowCount() Error: " + e.toString());
                        errorMessage = e.toString();
                        errorState = 3;
		}
          return numberofrows;
	}
 
        synchronized Hashtable getNextRecord()
	{
		Hashtable ht = new Hashtable();
 
		//Assign the next record to the hashtable
		try
		{
 
			if (rs.next())
			{
				////System.out.println("Data Present");
				//loop over the variable names and assign them and their values to the hashtable
				for (int col = 1; col <= meta.getColumnCount(); col++) {
				   String varName = meta.getColumnName(col).trim();
				   ////System.out.println("ColNAME " +varName);
				   String tdata = rs.getString(col);
 
				   if (tdata == null)
				   	  tdata = "";
 
				   ht.put(varName, tdata);
				}
			}
			else
			{
				ht = null;
			}
		}
		catch (Exception e)
		{
			//System.out.println("Database Read Error: " + e.toString());
                        errorMessage = e.toString();
                        errorState = 4;
		}
 
		return ht;
	}
 
	void close()
	{
		try
                {
                    con.close();
                }
                catch (Exception e)
                {
                    System.out.print("Database Close Error: " + e.toString() + "\n\n");
                }
        }
 
}

Open in new window

richardsimnettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrcoffee365Commented:
Have you tried closing the connection after an update?

For normal JDBC connections, on update the resultset is closed (in a sense) but the database connection itself exists and has to be closed.
0
richardsimnettAuthor Commented:
yes I do close after the update in the executeUpdate statement... my thought is that maybe Im not closing the conenction properly in the close() function  - but I cant seem to put my finger on what exactly is wrong.

Thanks,
Rick
0
mrcoffee365Commented:
It seems almost certain that you're not closing the connections properly.  What is the code you have for closing a db connection?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

richardsimnettAuthor Commented:
void close()
        {
                try
                {
                    con.close();
                }
                catch (Exception e)
                {
                    System.out.print("Database Close Error: " + e.toString() + "\n\n");
                }
        }
0
richardsimnettAuthor Commented:
I read somewhere to only close the con, never anything else, or it will result in the connection in the connection pool being closed, which negates the positive effect of using a pool in the first place.
0
mrcoffee365Commented:
con.close() looks good.  We always close the resultset, too, but closing the connection should be fine.  Do you see a lot of Database Close Error messages in your logs?  That would indicate that the connection couldn't be closed for some reason.

Is it possible that your connection pool is defined for more connections than your db accepts?  How many connections do you have in your pool, and how many does your db allow?
0
richardsimnettAuthor Commented:
there are no close errors reported at all in any of the logs, the glassfish logs are clear as well until I hit the connection pool errors... the connection pool is configured to allow a minimum of 8 connections and a maximum of 32 concurrent. The mysql server is set to allow a maximum of 512 concurrent connections. So the connections shouldnt be an issue, its just the fact that Im not properly freeing the connection pool connections somewhere...

Im going to try closing the result set and seeing what happens over time. Ill let you know in about 45 minutes if that worked (it typically takes that long for the servlet to start failing).

Thanks,
Rick
0
mrcoffee365Commented:
There was a bug last year in glassfish around closing nested get connection calls:
https://glassfish.dev.java.net/issues/show_bug.cgi?id=3279

It's been fixed, but is it possible that your version of glassfish doesn't have this fix?  You can see the version of glassfish with the fix at the bottom of the bug page.
0
mrcoffee365Commented:
Also, do you have any exceptions in your code that might bounce you out of your normal db.close() scope?  If so, then the connection will not be closed.  You should put the close in a finally clause, so that it always executes.  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
richardsimnettAuthor Commented:
ok, still the same error when I close the rs in addition to the con. I looked at the bug you showed, and to be honest, I just installed my copy of glassfish last month, it was bundled with netbeans. Plus I get the same error on our production servers, which I know to be completely up to date, so I dont think it is the issue.

Thanks,
Rick

0
richardsimnettAuthor Commented:
checked the versions here is what is on both: glassfish-v2-b58g

should be up to par, as b56 was the fixed version
0
richardsimnettAuthor Commented:
ok... its fixed. Im not sure why the exception is being thrown yet, but you were right somewhere that I execute the executeUpdate() function an exception is being generated that was not being checked for, and because of it the close statement never executed... when I mvoed the close() call into a finally statment it stopped messing up.

Thanks for the help! I wouldnt have figured this out withour your suggestions.

Thanks again!
0
mrcoffee365Commented:
In fact, now that you've confirmed the glassfish version, and you think that there are enough MySQL connections for your app, then I'd start debugging the connections and the closes.  Write to a log with every getConnection and with every close, and see if they match up.  It might help you track down where you are not closing connections, or where exceptions are occurring to prevent your close() from being called.
0
richardsimnettAuthor Commented:
Yeah, thats what Im actually doing... Its stupid errors, XML data paths that werent updated after some changes and totally overlooked, are causing invalid queries to be created, which throw an exception, and were causing the connections to not close properly on update. I think I got it now.

Thanks again!
0
mrcoffee365Commented:
Great -- congrats on tracking that down.  When I posted that last comment, I didn't realize that you'd already closed the question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.