环境说明:linux,tomcat,mysql
异常:com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException:Too many connections
原因分析:
mysql默认最大连接数设置为100;然而,当服务器访问量大时,客户端连接数超过默认连接数时则会频繁出现上述错误。
解决办法:包括下面两个方面,我总结解决办法后写篇文章贡献出来,免得再走弯路。在此也感谢网友们的无私奉献精神,帮助我解决了好多问题。
一、调整、放大mysql的最大连接数设置为1000
1、修改配置文件方法(第一种方法)
#cd/etc
#vimy.cnf
添加max_connections=1000
如下:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_connections=1000
#Defaulttousingoldpasswordformatforcompatibilitywithmysql3.x
#clients(thoseusingthemysqlclient10compatibilitypackage).
old_passwords=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
2、用命令修改参数(第二种方法)
客户端登录:mysql-uusername-ppassword
设置新的最大连接数为1000:mysql>setGLOBALmax_connections=1000
显示当前运行的Query:mysql>showprocesslist
显示当前状态:mysql>showstatus
退出客户端:mysql>exit
查看当前最大连接数:
mysqladmin-uusername-ppasswordvariables|grep"max_con"
|max_connections|1000
|max_connect_errors|10
二、编写代码时候严格使用如下结构,记得释放数据库连接资源,并且保证在出错时候也释放数据库连接资源。
try
{...}
catch
{...}
finally
{
rs.close();
stmt.close();
conn.close();
conn=null;
}
原始错误信息:errorlog
rootcause
com.mysql.jdbc.exceptions.MySQLNonTransientConnectionException:Toomanyconnections
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:921)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:812)
com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3284)
com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1182)
com.mysql.jdbc.Connection.createNewIO(Connection.java:2644)
com.mysql.jdbc.Connection.<init>(Connection.java:1531)
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)
java.sql.DriverManager.getConnection(DriverManager.java:582)
java.sql.DriverManager.getConnection(DriverManager.java:207)
cn.com.talented.connection.MysqlConnection.getConnection(MysqlConnection.java:25)
com.wtszld.dao.PriceDAO.listObject(PriceDAO.java:127)
org.apache.jsp.index_jsp._jspService(index_jsp.java:114)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:384)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:320)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
noteThefullstacktraceoftherootcauseisavailableintheApacheTomcat/6.0.10logs.
参考文章:
http://www.128kj.com/article/article17/89D1EB86685E991CE30CC2F2D7BC4327.htm?id=655