java MySql 连接过多

MySql too many connections

提问by MichaelMcCabe

I hate to bring up a question which is widely asked on the web, but I cant seem to solve it.

我讨厌提出一个在网络上被广泛提出的问题，但我似乎无法解决它。

I started a project a while back and after a month of testing, I hit a "Too many connections" error. I looked into it, and "Solved" it by increasing the max_connections. This then worked.

不久前我开始了一个项目，经过一个月的测试，我遇到了“连接过多”的错误。我查看了它，并通过增加 max_connections 来“解决”它。这然后奏效了。

Since then more and more people started to use it, and it hit again. When I am the only user on the site, i type "show processlist" and it comes up with about 50 connections which are still open (saying "Sleep" in the command). Now, I dont know enough to speculate why these are open, but in my code I tripple checked and every connection I open, I close.

从那以后，越来越多的人开始使用它，它再次流行起来。当我是站点上的唯一用户时，我输入“show processlist”，它会出现大约 50 个仍然打开的连接（在命令中说“Sleep”）。现在，我不知道为什么这些是打开的，但是在我的代码中，我检查了三次，并且我打开的每个连接都关闭了。

ie.

IE。

public int getSiteIdFromName(String name, String company)throws DataAccessException,java.sql.SQLException{

Connection conn = this.getSession().connection();
Statement smt = conn.createStatement();
ResultSet rs=null;
String query="SELECT id FROM site WHERE name='"+name+"' and company_id='"+company+"'";

rs=smt.executeQuery(query);
rs.next();

int id=rs.getInt("id");

rs.close();
smt.close();
conn.close();
return id;
}

Every time I do something else on the site, another load of connections are opened and not closed. Is there something wrong with my code? and if not, what could be the problem?

每次我在网站上做其他事情时，都会打开而不是关闭另一批连接。我的代码有问题吗？如果没有，可能是什么问题？

回答by BalusC

With your approach, the connection will never be closed if any exception is been thrown beforethe conn.close()is called. You need to acquire it (and the statement and resultset) in a tryblock and close it in the finallyblock. Any code in finallywill alwaysbe executed regardless of an exception is been thrown or not. With this you can ensurethat the expensive resources will be closed.

你的方法，连接将永远不会被关闭，如果任何异常被抛出了之前的conn.close()被调用。您需要在块中获取它（以及语句和结果集）并在try块中关闭它finally。无论是否抛出异常，其中的任何代码都finally将始终执行。有了这个，您可以确保关闭昂贵的资源。

Here's a rewrite:

这是一个重写：

public int getSiteIdFromName(String name, String company) throws DataAccessException, java.sql.SQLException {
    Connection conn = null;
    Statement smt = null;
    ResultSet rs = null;
    int id = 0;
    try {
        conn = this.getSession().connection();
        smt = conn.createStatement();
        String query = "SELECT id FROM site WHERE name='" + name + "' and company_id='" + company + "'";
        rs = smt.executeQuery(query);
        rs.next();
        id = rs.getInt("id");
    } finally {
        if (rs != null) try { rs.close(); } catch (SQLException logOrIgnore) {}
        if (smt != null) try { smt.close(); } catch (SQLException logOrIgnore) {}
        if (conn != null) try { conn.close(); } catch (SQLException logOrIgnore) {}
    }
    return id;
}

That said, this code is sensitive to SQL injectionattacks. Use a PreparedStatementinstead of Statement.

也就是说，这段代码对SQL 注入攻击很敏感。使用PreparedStatement代替Statement。

See also:

另见：

• Sun Exceptions tutorial: The Finally block
• Sun JDBC tutorial: introduction
• Sun JDBC tutorial: how to use PreparedStatement
• DAO tutorial: how to use basic JDBC code properly

• Sun Exceptions 教程：Finally 块
• Sun JDBC教程：介绍
• Sun JDBC 教程：如何使用 PreparedStatement
• DAO教程：如何正确使用基本的JDBC代码

回答by Tahir Akhtar

One possible flow in which this code can leak connection is:

此代码可能泄漏连接的一种可能流程是：

1. Stmt.executeQuery() results empty resultset
2. You do not check whether rs.next() returns true or false
3. rs.getInt("id") throws exception as there is not current row in resultset
4. conn.close() is skipped

1. stmt.executeQuery() 结果为空结果集
2. 您不检查 rs.next() 是否返回 true 或 false
3. rs.getInt("id") 抛出异常，因为结果集中没有当前行
4. conn.close() 被跳过

Do the following:

请执行下列操作：

1. Make rs.getInt() conditional on rs.next()
2. Close the connection in finally block and do all the data access within try block

1. 使 rs.getInt() 以 rs.next() 为条件
2. 在 finally 块中关闭连接并在 try 块中进行所有数据访问

Edit:

编辑：

Also it's a good idea to log all exceptions somewhere so that you have a good starting point in your troubleshooting.

此外，在某处记录所有异常也是一个好主意，以便您在故障排除中有一个良好的起点。

回答by Navankur Chauhan

The temporary solution for this problem can be increasing the allowed maximum connections of the mysql.You can do this by the below mentioned either of the two ways.

这个问题的临时解决方案可以是增加mysql的允许最大连接数。您可以通过下面提到的两种方法之一来做到这一点。

First:

第一的：

Login into the mysql server and type the below given command.

登录到 mysql 服务器并键入以下给定的命令。

mysql> SET GLOBAL max_connections = 200;

This will increase the maximum connections.But this setting will change if the server is restarted.

这将增加最大连接数。但如果服务器重新启动，此设置将更改。

Second:

第二：

Edit the file /etc/mysql/my.cnf and increase the max_connection in this file.

编辑文件 /etc/mysql/my.cnf 并增加此文件中的 max_connection。

[mysqld]
local-infile=0
datadir=/var/lib/mysql
user=mysql
symbolic-links=0

max_connections = 100

Save the changes and type the following to restart mysqld:

保存更改并键入以下内容以重新启动 mysqld：

/etc/init.d/mysqld restart

But this problem , usually appears because of the open connections that are not properly close and are unavailable to use.

但是这个问题，通常是因为打开的连接没有正确关闭并且无法使用而出现的。

Just try looking into all of the resources that are accessing your data base and check if all the connections are properly handled.

只需尝试查看正在访问您的数据库的所有资源，并检查所有连接是否都得到了正确处理。

looking into the code it can be seen that you have not placed the code in the try and catch.

查看代码可以看出您没有将代码放在 try 和 catch 中。

According to your code , if the exception occurs in fetching the data your connection will not be closed and hence it will be a waste of resource.Hence as the coding standard suggests use the try and catch with finally to handle the connections.

根据您的代码，如果在获取数据时发生异常，您的连接将不会关闭，因此将浪费资源。因此，编码标准建议使用 try 和 catch with finally 来处理连接。

try{
//your code 
}
catch(Exception e){

//handle the exceptions here
}

finally{
        try{
            channel.close();
        } catch(Exception e){
            log.error("Error is "+e.getMessage(),e);
            e.printStackTrace();
        }
        try {
            connection.close();
        } catch (IOException e) {
            log.error("Error is "+e.getMessage(),e);
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        catch(Exception e){
            log.error("Error is "+e.getMessage(),e);
            e.printStackTrace();
        }
    }

回答by Tobias P.

If the code throws a DataAccessException or java.sql.SQLException the connection will not be closed resulting in many open sleeping connections ;) Make a try-finally-Block which will close the connection.

如果代码抛出 DataAccessException 或 java.sql.SQLException 连接将不会关闭，从而导致许多打开的休眠连接；) 创建一个 try-finally-Block 将关闭连接。

Connection conn = this.getSession().connection();
try {
  // all code
} finally {
  rs.close();
  smt.close();
  conn.close();
}

It's a trivial example but a little more complex because you have to check wich of these objects are really created and used.

这是一个微不足道的例子，但有点复杂，因为您必须检查这些对象中的哪些是真正创建和使用的。