it happens almost once a day when mysql says 'Too many connections'. I've been searching for the solution on google for a while, but no help. I'd appreciate it if you would help me! Thanks!
I'm using DBUtils in my program with a database connection pool configured in server.xml of a Tomcat 7. (I have two instances of tomcat 7 with the same configuration, but I keep only one of them running, I'll shut down the current one before I start the other one.)
Here is How I get the connection, from the DB class:
private static Logger log = Logger.getLogger(DB.class.getName());
private static DataSource ds = null;
private static Context initCtx = null;
static {
try {
initCtx = new InitialContext();
ds = (DataSource) initCtx.lookup("java:comp/env/jdbc/ens");
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
log.error(e);
}
}
public static Connection getConnection() throws SQLException {
Connection con = ds.getConnection();
return con;
}
public static QueryRunner getRunner() {
return new QueryRunner();
}
Here is how I do queries with the method selectList:
protected Connection conn;
protected void getConnection() throws SQLException {
this.conn = DB.getConnection();
}
protected void close() {
DbUtils.closeQuietly(this.conn);
}
protected <T> List<T> selectList(String sql, Class<T> c, Page p,
Object... params) throws SQLException {
try {
this.getConnection();
QueryRunner runner = DB.getRunner();
sql = sql + this.getLimitClause(p);
log.debug("selectList - " +sql);
return runner.query(this.conn, sql,
new BeanListHandler<T>(c), params);
} catch (SQLException e) {
// TODO: handle exception
throw e;
} finally {
this.close();
}
}
Here is the configuration in server.xml:
<Resource name="jdbc/ens"
auth="Container"
type="javax.sql.DataSource"
factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
testWhileIdle="true"
testOnBorrow="true"
testOnReturn="false"
validationQuery="SELECT 1"
validationInterval="30000"
timeBetweenEvictionRunsMillis="30000"
maxActive="270"
minIdle="10"
maxWait="10000"
initialSize="20"
removeAbandonedTimeout="60"
removeAbandoned="true"
logAbandoned="true"
minEvictableIdleTimeMillis="30000"
jmxEnabled="true"
jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
username="root"
password="root"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost/chineselearning?useUnicode=true&characterEncoding=utf-8"/>
the max connections of mysql is set to 300 in my.ini
max_connections=300
here is the result of 'show processlist' from mysql when it says 'too many connections'
1 govoluble localhost:1033 chineselearning Sleep 56
2 govoluble localhost:1035 chineselearning Sleep 56
3 govoluble localhost:1036 chineselearning Sleep 56
4 govoluble localhost:1037 chineselearning Sleep 56
5 govoluble localhost:1038 chineselearning Sleep 56
6 govoluble localhost:1039 chineselearning Sleep 56
7 govoluble localhost:1040 chineselearning Sleep 56
8 govoluble localhost:1041 chineselearning Sleep 56
9 govoluble localhost:1042 chineselearning Sleep 56
10 govoluble localhost:1043 chineselearning Sleep 56
946 govoluble localhost:3146 chineselearning Sleep 3569
947 govoluble localhost:3147 chineselearning Sleep 3569
948 govoluble localhost:3163 chineselearning Sleep 2694
1186 govoluble localhost:1869 chineselearning Query 94 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1217 govoluble localhost:1930 chineselearning Sleep 5
1218 govoluble localhost:1931 chineselearning Query 121 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1219 govoluble localhost:1932 chineselearning Sleep 5
1220 govoluble localhost:1933 chineselearning Query 121 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1221 govoluble localhost:1934 chineselearning Query 121 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1222 govoluble localhost:1935 chineselearning Query 121 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1224 govoluble localhost:1937 chineselearning Query 121 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1225 govoluble localhost:1938 chineselearning Sleep 21
1840 govoluble localhost:2768 chineselearning Sleep 23
1841 govoluble localhost:2770 chineselearning Sleep 23
1842 govoluble localhost:2772 chineselearning Sleep 23
1843 govoluble localhost:2773 chineselearning Query 32 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1844 govoluble localhost:2774 chineselearning Query 32 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1845 govoluble localhost:2775 chineselearning Query 14 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1846 govoluble localhost:2776 chineselearning Query 32 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1847 govoluble localhost:2777 chineselearning Query 0 Sending data select count(1) from message where recipient=348 and hasRead=0 and recipientdeleted = 0 and system=0 1848 govoluble localhost:2778 chineselearning Query 32 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1849 govoluble localhost:2779 chineselearning Query 32 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1850 govoluble localhost:2780 chineselearning Query 12 Locked update entry set watch=watch+1 where id=563 1851 govoluble localhost:2782 chineselearning Sleep 23
1852 govoluble localhost:2784 chineselearning Sleep 23
1853 govoluble localhost:2786 chineselearning Sleep 0
1854 govoluble localhost:2788 chineselearning Sleep 23
1855 govoluble localhost:2790 chineselearning Query 23 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1856 govoluble localhost:2792 chineselearning Query 15 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1857 govoluble localhost:2794 chineselearning Sleep 23
1858 govoluble localhost:2796 chineselearning Query 23 Copying to tmp table select e.* from entry as e left join correction as c on e.id = c.entry where e.type=1 group by e.id 1859 govoluble localhost:2809 chineselearning Query 0 show processlist
