Unable to get connection from apache DBCP connection pooling after mysql connection timeout

Question

This is quite weird situation and i think its most common when handling mysql connection object. The below is the scenario

I get a connection object using apache DBCP connection pooling before executing sql statements. Everything works fine until the mysql connection timeout (8 hrs) occurs. So after this connection pooling does not return me any connection object, which makes any sql operation wait until the next restart of the tomcat server. Without DBCP we may get communicationException saying that no packets sent or received. That's the reason i've opted for apache DBCP thinking that it will manage the connection pooling. Below is my code

import javax.sql.DataSource;
import org.apache.log4j.Logger;
import org.apache.commons.dbcp.BasicDataSource;

DataSource dataSource
BasicDataSource ds = new BasicDataSource();
ds.setDriverClassName(driver);
ds.setUsername(username);
ds.setPassword(password);
ds.setUrl(url + "/" + dbname);
dataSource = ds;
logger.info("Getting connection from DBCP connection pool...");
con = dataSource.getConnection();
logger.info("MYSQL DB Connection Creation Successful...");

I've noticed that apache DBCP is using singleton pattern to create connection so new connection object is not created everytime i request. Kindly suggest me a way out and clarify me if i am doing something wrong. And i am not for the following solutions

Increasing mysql timeout (bcoz mysql is common and i don't think increasing will be a good solution)
Restarting tomcat server when problem occurs ( not a pretty solution as we can not be there always and no scripting too... :)

Kindly suggest me a way out. Thanks

I get this line printed "Getting connection from DBCP connection pool.." but not this line "MYSQL DB Connection Creation Successful...". No error message gets printed..!! — Vivek
– Vivek, Commented Dec 20, 2013 at 9:31

Ashish Jagtap · Accepted Answer · 2013-12-20 09:19:43Z

I am not getting the setting for DBCP connectionn pool In your code. Just try below code for connection pooling as describe in the documentation.

PoolProperties p = new PoolProperties();
          p.setUrl("jdbc:mysql://localhost:3306/mysql");
          p.setDriverClassName("com.mysql.jdbc.Driver");
          p.setUsername("root");
          p.setPassword("password");
          p.setJmxEnabled(true);
          p.setTestWhileIdle(false);
          p.setTestOnBorrow(true);
          p.setValidationQuery("SELECT 1");
          p.setTestOnReturn(false);
          p.setValidationInterval(30000);
          p.setTimeBetweenEvictionRunsMillis(30000);
          p.setMaxActive(100);
          p.setInitialSize(10);
          p.setMaxWait(10000);
          p.setRemoveAbandonedTimeout(60);
          p.setMinEvictableIdleTimeMillis(30000);
          p.setMinIdle(10);
          p.setLogAbandoned(true);
          p.setRemoveAbandoned(true);
          p.setJdbcInterceptors(
            "org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;"+
            "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");
          DataSource datasource = new DataSource();
          datasource.setPoolProperties(p);

          Connection con = null;
          try {
            con = datasource.getConnection();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("select * from user");
            int cnt = 1;
            while (rs.next()) {
                System.out.println((cnt++)+". Host:" +rs.getString("Host")+
                  " User:"+rs.getString("User")+" Password:"+rs.getString("Password"));
            }
            rs.close();
            st.close();
          } finally {
            if (con!=null) try {con.close();}catch (Exception ignore) {}
          }

also read information about each attribute in this document. hope this will solve your problem

aviad · Accepted Answer · 2013-12-20 09:03:43Z

0

Need to look at the exception . However, just a shot in the dark: If you see something like

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException

You might want to wrap it with try/catch and in the catch block reconnect and rerun your query. Check MySQL docs for handling stalled connection.

BTW: this is really easy with Spring - you can configure the dataSource to revive stalled connections.

answered Dec 20, 2013 at 9:03

aviad

8,2789 gold badges55 silver badges100 bronze badges

Collectives™ on Stack Overflow

Unable to get connection from apache DBCP connection pooling after mysql connection timeout

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related