commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nathan Ewing" <Nathan.Ew...@cbhk.com>
Subject RE: DBCP & mysql hanging after a period of time
Date Wed, 31 Mar 2004 18:42:00 GMT
What is the best way to change the DEFAULT_WHEN_EXHAUSTED_ACTION? (of
course this will only prolong the problem until it fills up the queue)

I don't think my code is causing the connections to be lost, which means
the root of the problem is out of my control, or is somewhere else (The
connections are closing in mysql, but not in dbcp).

Will AbandonedObjectPool allow me to close out connections that DBCP is
losing, or is there another way?

	Thanks,
	Nathan


-----Original Message-----
From: Dirk Verbeeck [mailto:dirk.verbeeck@pandora.be] 
Sent: Wednesday, March 31, 2004 1:33 PM
To: Jakarta Commons Users List
Subject: Re: DBCP & mysql hanging after a period of time

If numActive=8 and maxActive=8 then the connection pool has handed out 
all the connection it has created and the pool isn't allowed to create 
  more. The default pool action in this situation is:
DEFAULT_WHEN_EXHAUSTED_ACTION = WHEN_EXHAUSTED_BLOCK

So clearly your app has abandoned (lost) some of the connections it 
was given (simply not closed or missing error handling or threading 
error). Another possibility is some network error causing disconnects 
and/or long delays.

When you "leave the site" is the site/connection not used (idle)?
This would point to a network connection timeout. (firewall?)
(try using a validationquery to verify this)

Otherwise you need to trace the getconnection/close methods in 
PoolingDataSource (just add some logging).
Or use AbandonedObjectPool to log abandoned connections.

-- Dirk


Nathan Ewing wrote:

> I've been developing a website for about 6 months now, and throughout
> the development DBCP has been giving me trouble.  If I leave the site
> for a while, randomly if I come back to it, when it tries to access
the
> database it just hangs there forever (there is no discernable pattern
to
> this).  I'm certain I am closing all my connections, statements, and
> whatnot during each operation.  After it starts hanging, all future
> accesses to the database hang forever as well, until I reset my
> webserver.  I've tried everything I can think of, and checked every
> resource on the web I could find, for the last six months looking.
This
> is my last resort before I am forced to write my own connection
pooling
> software (which I really don't want to do).  I'm using version 3.0.11
of
> the mysql driver and version 1.1 of dbcp.
> 
> I tried to look into the memory during the hanging operation, and it
> appears that maxActive=8 and numActive=8, so I'm guessing that the
pool
> is not correctly freeing up connections.  When I check the mysql admin
> the number of processes seems to be 0.  It seems like DBCP is
collecting
> phantom connections or something.  I'm really not sure.
> 
> Please help!
> 
> Below is the offending code, the program is hanging when I do
> datasource.getConnection():
> 
> 
> import java.sql.DriverManager;
> import java.sql.Connection;
> import javax.sql.DataSource;
> 
> import java.io.PrintWriter;
> 
> import org.apache.commons.pool.ObjectPool;
> import org.apache.commons.pool.impl.GenericObjectPool;
> import org.apache.commons.dbcp.ConnectionFactory;
> import org.apache.commons.dbcp.PoolingDataSource;
> import org.apache.commons.dbcp.PoolableConnectionFactory;
> import org.apache.commons.dbcp.DriverManagerConnectionFactory;
> 
> public class ConnectionManager {
> 	private static final ConnectionManager INSTANCE = new
> ConnectionManager();
> 	
> 	//Name of SQL Driver to load
> 	private static final String DRIVERNAME =
> "com.mysql.jdbc.Driver";
> 	private static final String CONNECTSTRING =
> "jdbc:mysql:///website:admin:mypassword?autoReconnect=true";
> 	
> 	//The actual pool of Database Connections
> 	private static DataSource dataSource;
> 	private static ObjectPool connectionPool;
> 	
> 	// Private constructor supresses 
> 	// default public constructor
> 	private ConnectionManager( ) {
> 		// Load the Database SQL Driver
> 		try{
> 			Class.forName(DRIVERNAME);
> 		} catch (java.lang.ClassNotFoundException e)
> {System.out.println ("Database SQL Driver not found!");}
> 		
> 		try {
> 			//
> 			// First, we'll need a ObjectPool that serves as
> the
> 			// actual pool of connections.
> 			//
> 			// We'll use a GenericObjectPool instance,
> although
> 			// any ObjectPool implementation will suffice.
> 			//
> 			connectionPool = new GenericObjectPool(null);
> 			
> 			//
> 			// Next, we'll create a ConnectionFactory that
> the
> 			// pool will use to create Connections.
> 			// We'll use the DriverManagerConnectionFactory,
> 			// using the connect string passed in the
> command line
> 			// arguments.
> 			//
> 			ConnectionFactory connectionFactory = new
> DriverManagerConnectionFactory(CONNECTSTRING,null);
> 			
> 			//
> 			// Now we'll create the
> PoolableConnectionFactory, which wraps
> 			// the "real" Connections created by the
> ConnectionFactory with
> 			// the classes that implement the pooling
> functionality.
> 			//
> 			PoolableConnectionFactory
> poolableConnectionFactory = new
>
PoolableConnectionFactory(connectionFactory,connectionPool,null,null,fal
> se,true);
> 			
> 			//
> 			// Finally, we create the PoolingDriver itself,
> 			// passing in the object pool we created.
> 			// We then save the datasource object to the
> class for later use in getting connections
> 			//
> 			dataSource = new
> PoolingDataSource(connectionPool);
> 			//System.out.println("Idle: " +
> connectionPool.getNumIdle() + " - Active: " +
> connectionPool.getNumActive());
> 			
> 			} catch (Exception e){}
> 		
> 		
> 		
> 		}
> 	//Returns and Instance of the ConnectionManager object (not
> really necessary)
> 	public static ConnectionManager getInstance( ) {
> 		return INSTANCE;
> 		}
> 	
> 	//Returns a database connection object
> 	public static Connection getConnection(){
> 		Connection conn = null;
> 		try{
> 			conn = dataSource.getConnection();	
> 			} catch(java.sql.SQLException e){
> 			    System.err.print (e.getStackTrace());
> 			}
> 		
> 		//System.out.println("Idle: " +
> connectionPool.getNumIdle() + " - Active: " +
> connectionPool.getNumActive());
> 		return conn;
> 		
> 		}
>  }
> 
> 
> 
> 
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
> For additional commands, e-mail: commons-user-help@jakarta.apache.org
> 
> 
> 
> 



---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org




---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org


Mime
View raw message