即使所有连接都正确关闭，MAX_USER_CONNECTIONS也会出现问题

I not very good in java. I have made a website for a client but am continuously getting an error like Server connection failure during transaction. Due to underlying exception: 'com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: User root already has more than 'max_user_connections' active connections'. The website hosting that I am using provides only 10 max_user_connections. But if I continuously use that site, I get this error because of continuously hits on the webserver.

我在java中不太好。我为客户创建了一个网站,但在交易过程中不断收到服务器连接失败等错误。由于底层异常:'com.mysql.jdbc.exceptions.MySQLSyntaxErrorException:用户root已经超过'max_user_connections'活动连接'。我正在使用的网站托管仅提供10个max_user_connections。但是如果我继续使用该网站,我会因为网络服务器不断点击而收到此错误。

What can be the reason behind this? Am I not closing the connections right? I have closed all the connections using con.close().

这背后的原因是什么?我没有关闭连接吗?我使用con.close()关闭了所有连接。

Please help

Regards Apurv

To open the connection I have used

打开我用过的连接

Connection con=null;
Statement st=null;
Class.forName("com.mysql.jdbc.Driver").newInstance();
String useq="root";
String paq="manager";
String url="jdbc:mysql://localhost:3306/jayna?autoReconnect=true";
con=DriverManager.getConnection(url,useq,paq);
st=con.createStatement();

To close the connection I have used

关闭我用过的连接

if(rs!=null){
rs.close();
}
if(st!=null){
st.close();
}
if(con!=null){
con.close();
}

I haven't used a database pool but what can be the use of that when I am closing each of the connection properly??

我没有使用数据库池,但当我正确关闭每个连接时,可以使用什么?

5 个解决方案

#1

This seems like a case of connection leak.

这似乎是连接泄漏的情况。

1. Are you sure, you have closed all the connections?

你确定,你已经关闭了所有连接?

2. Conneciton.close() throws IOException. Check if it is successfully able to close connections.

Conneciton.close()抛出IOException。检查是否能够成功关闭连接。

3. Use netstat or other tools to find whether connections are really closed or are in WAITING state or something.

使用netstat或其他工具来查找连接是否真正关闭或处于等待状态等。

#2

I think you should increase number of connections. Because if there are 10 slow query and you get 11 requests to your site 11 request couldn't be processed and you get this error. So:

我认为你应该增加连接数。因为如果有10个慢查询,并且您收到11个请求到您的站点11请求无法处理,您会收到此错误。所以:

1. Try increase a number of connections.

尝试增加一些连接。

2. Try to find slow queries(using slow query log) and optimize them

尝试查找慢查询(使用慢查询日志)并优化它们

#3

Connection pooling is a technique to provide a set of ready-to-use connections, one of the advantages being that you save the creation/opening time on each call. Another advantage is that the connection pooler can help detect abandoned connections, ie connections that the application forgot to close.

连接池是一种提供一组即用型连接的技术,其中一个优点是可以节省每次调用的创建/打开时间。另一个优点是连接池可以帮助检测废弃的连接,即应用程序忘记关闭的连接。

There's a standard connection pool in Tomcat, for 5.x version Tomcats look here for some info, for version 6 look here and for version 7 info can be found here. Its removeAbandoned and logabandoned features can help you determine whether your app really forgets to close connections, or 10 just isn't enough - see Andrej's suggestion, you should profile your queries.

在Tomcat中有一个标准的连接池,对于5.x版本的Tomcats在这里查看一些信息,对于版本6看这里和版本7信息可以在这里找到。它的removeAbandoned和logabandoned功能可以帮助您确定您的应用是否真的忘记关闭连接,或者10只是不够 - 请参阅Andrej的建议,您应该对您的查询进行分析。

#4

As others have said, you either are not getting connections closed or your application simply needs more than 10 concurrent connections under some load conditions.

正如其他人所说的那样,你要么没有关闭连接,要么你的应用程序在某些负载条件下只需要10个以上的并发连接。

1. If the error always occurs on the 11th request, it's likely you're never getting the connections closed.

如果错误始终发生在第11个请求上,则可能是您从未关闭连接。

2. If it occurs sometime later, unpredictably, and goes away on its own, it's more likely 10 simply isn't sufficient for certain load scenarios.

如果它发生在某个时间之后,不可预测,并且自行消失,那么对于某些负载情况来说,它更可能仅仅是不够的。

3. If it occurs later, unpredictably, but never goes away on its own, it's possible you're failing to close connections only in specific cases that aren't hit every time.

如果它发生得太晚,不可预测,但永远不会自行消失,那么你可能无法在每次都没有被击中的特定情况下关闭连接。

If 10 is too small for some load scenarios (option 2), you should both check your queries and code logic to ensure you're not holding connections way longer than necessary and you should probably try to move to a Connection Pool, as others have suggested. Among other things, creating new Connections from scratch has more overhead than reusing them from a pool, so that could be causing individual accesses to take much longer than necessary.

如果10对于某些负载情况而言太小(选项2),您应该检查您的查询和代码逻辑,以确保您没有超过必要的连接时间,并且您可能应该尝试移动到连接池,因为其他人有建议。除此之外,从头开始创建新的连接比从池中重用它们需要更多的开销,因此可能导致单个访问花费的时间比必要的长。

#5

This problem can be solved by using a Singleton class structure for initializing connection objects. Using the Singleton pattern, whenever a connection object is initialized, rather than creating a new object, it will look for existing instance of connection object and use that one, if it exists.

通过使用Singleton类结构初始化连接对象可以解决此问题。使用Singleton模式,无论何时初始化连接对象,而不是创建新对象,它都将查找连接对象的现有实例并使用该实例(如果存在)。