Problem with MySql Connections in servlet application

[John Schretz]

I am having a problem in tomcat, i am getting the below connection error.
I have my max connections set to 300 and when i check the connections in MYSql admin it only shows 27 connections.

I am using connection pooling. I have supplied the code below that i use in my application that pertains to this issue.

Is there a better way to handle the connections than what i am doing.

This is all in a simple servlet/jsp application

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
	at sun.reflect.GeneratedConstructorAccessor43.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.Util.getInstance(Util.java:381)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:926)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2253)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:718)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
	at sun.reflect.GeneratedConstructorAccessor36.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
	at org.apache.tomcat.dbcp.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
	at org.apache.tomcat.dbcp.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:294)
	at org.apache.tomcat.dbcp.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:974)
	at org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:96)
	at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880)
	at data.ConnectionPool.getConnection(ConnectionPool.java:38)
	at data.VendorDB.selectVendHeader(VendorDB.java:120)
	at category.DisplayProducts.doGet(DisplayProducts.java:155)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
	at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:190)
	at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:283)
	at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:767)
	at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:697)
	at org.apache.jk.common.ChannelSocket$SocketConnection.runIt(ChannelSocket.java:889)
	at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:690)
	at java.lang.Thread.run(Thread.java:619)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: User root already has more than 'max_user_connections' active connections
	at sun.reflect.GeneratedConstructorAccessor52.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.Util.getInstance(Util.java:381)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3491)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3423)
	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:910)
	at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3923)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1273)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2181)
	... 33 more

Connection pool

package data;

import java.sql.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;

public class ConnectionPool
{
    private static ConnectionPool pool = null;
    private static DataSource dataSource = null;
 
    public synchronized static ConnectionPool getInstance()
    {
        if (pool == null)
        {
            pool = new ConnectionPool();
        }
        return pool;
    }

    private ConnectionPool()
    {
        try
        {
            InitialContext ic = new InitialContext();
            dataSource = (DataSource) ic.lookup("java:/comp/env/jdbc/DBNAME");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
    }

    public Connection getConnection()
    {
        try
        {
            return dataSource.getConnection();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
            return null;
        }
    }
    
    public void freeConnection(Connection c)
    {
        try
        {
            c.close();
        }
        catch (SQLException sqle)
        {
            sqle.printStackTrace();
        }
        catch (NullPointerException npe){
        	npe.printStackTrace();
        }
    }
}

all my data classes look like this

package adminData;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.StringTokenizer;

import data.ConnectionPool;
import data.UtilDB;

import adminEcom.AdminUser;

import ecom.User;

public class AdminDB {

	
	public static AdminUser selectAdmin(String USERNAME, String passwd)
    {
    	ConnectionPool pool = ConnectionPool.getInstance();
    	Connection connection = pool.getConnection();
    	PreparedStatement ps = null;
    	ResultSet rs = null;

    	String query = "SELECT * FROM ADMIN_USER " +
    	"WHERE USERNAME = ? and PASSWD = ?";
    	try
    	{
    		ps = connection.prepareStatement(query);
    		ps.setString(1, USERNAME);
    		ps.setString(2, passwd);
    		rs = ps.executeQuery();
    		AdminUser adminUser = null;
    		if (rs.next())
    		{
    			adminUser = new AdminUser();
    			adminUser.setRole(rs.getString("ADMIN_ROLE"));
    			adminUser.setUsername(rs.getString("USERNAME"));
    			adminUser.setPasswd(rs.getString("PASSWD"));
    		}
    		return adminUser;
    	}
    	catch (SQLException e){
    		e.printStackTrace();
    		return null;
    	}        
    	finally
    	{
    		UtilDB.closeResultSet(rs);
    		UtilDB.closePreparedStatement(ps);
    		pool.freeConnection(connection);
    	}
    }
	
}

Utility class

package data;

import java.sql.*;

public class UtilDB {
    public static void closeStatement(Statement s)
    {
        try
        {
            if (s != null)
                s.close();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
    }
    
    public static void closePreparedStatement(Statement ps)
    {
        try
        {
            if (ps != null)
                ps.close();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
    }

    public static void closeResultSet(ResultSet rs)
    {
        try
        {
            if (rs != null)
                rs.close();
        }
        catch(SQLException e)
        {
            e.printStackTrace();
        }
    }
}

tomcat configuration

<Resource name="jdbc/DBNAME" 
auth="Container" 
type="javax.sql.DataSource"
maxActive="300" 
maxIdle="30" 
maxWait="10000" 
testOnBorrow="true" 
validationQuery="select 1"         
username="root" 
password="password" 
driverClassName="com.mysql.jdbc.Driver" 
url="jdbc:mysql://127.0.0.1:3306/DBNAME?autoReconnect=true"/> 

[amit punekar]

Hi,
Just curious to know if there is any reason you are not using commons DBCP connection pool ?
Regards,
Amit

[John Schretz]

amit punekar wrote:Hi,
Just curious to know if there is any reason you are not using commons DBCP connection pool ?
Regards,
Amit

I never knew of this. What are the advantages of it?

[amit punekar]

Hi,
Advantages as in you get basic connection pool ready that can be easily configured for Tomcat.
I had written one entry on configuring the connection pool in tomcat.
Here is the entry, hope that helps you.

Regds,
Amit