使用DBCP的Tomcat配置 [英] Tomcat Configuration using DBCP

问题描述

经过一段时间(几个小时)后，我们从DBCP得到了CommunicationsException.错误消息(位于异常"中)在此问题的末尾-但我看不到任何配置文件中定义的wait_timeout. (我们应该看哪里?在tomcat/conf目录之外的某个地方?).

We are getting a CommunicationsException (from DBCP) after iding for a while (a few hours). The error message (in the Exception) is at the end of this question - but I dont see wait_timeout defined in any of the configuration files. (Where should we look? Somewhere out of the tomcat/conf directory?).

其次，如异常所建议，将"Connector/J连接属性'autoReconnect = true'"放在哪里?这是在Tomcat中设置的conf/context.xml文件中的资源定义:

Secondly, as suggested by the Exception, where does one put the "Connector/J connection property 'autoReconnect=true'"? Here is the resource definition in the file conf/context.xml in tomcat set up:

<Resource name="jdbc/TomcatResourceName" auth="Container" type="javax.sql.DataSource"
           maxActive="100" maxIdle="30" maxWait="10000"
           removeAbandoned="true" removeAbandonedTimeout="60" logAbandoned="true"
           username="xxxx" password="yyyy"
           driverClassName="com.mysql.jdbc.Driver"
           url="jdbc:mysql://127.0.0.1:3306/dbname?autoReconnect=true"/>

第三，为什么JVM等待直到对executeQuery()的调用引发异常?如果连接超时，则getConnection方法应该抛出Exception，不是吗?这是我正在谈论的源代码部分:

Thirdly, why does the JVM wait till the call to executeQuery() to throw the Exception? If the connection has timed out, the getConnection method should throw the Exception, shouldn't it? This is the section of the source code I am talking about:

        try {
                conn = getConnection (true);
                stmt = conn.createStatement (ResultSet.TYPE_SCROLL_INSENSITIVE,
                                                ResultSet.CONCUR_READ_ONLY);
                rset = stmt.executeQuery (bQuery);
                while (rset.next()) {
                     ....

最后，这是堆栈跟踪的第一行...

Finally, here are the 1st few lines of the Stack trace...

com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 84,160,724 milliseconds ago.  The last packet sent successfully to the server was 84,160,848 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:532)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1074)
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3291)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1938)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2642)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2571)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1451)
at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)

这些是我们中的一些人认为忘记dbcp的原因，它可能过于依赖IDE配置和高级功能，以至于DriverManager.getConnection(...)可能更可靠".对此有何评论?谢谢您的见解，-MS

These are the reasons some of us are thinking "forget dbcp, it may be so dependent on IDE configurations and under-the-hood magic that DriverManager.getConnection(...) may be more reliable". Any comments on that? Thank you for your insights, - MS

推荐答案

由于DBCP保持对即将到来的连接请求的返回的mysql连接保持打开状态，因此它们成为

Since DBCP keeps returned mysql connections open for upcoming connection requests, they fall victims to the MySQL Server timeout.

DBCP具有许多可以提供帮助的功能(可以从Tomcat 5.5 IIRC开始使用).

DBCP has a number of features that can help (can be used starting with Tomcat 5.5 IIRC).

validationQuery="SELECT 1"
testOnBorrow="true"

在将连接返回给执行借阅"方法的Web应用之前，验证将确保连接有效.该标志当然会启用此功能.

The validation makes sure that a connection is valid before returning it to a webapp executing the 'borrow' method. The flag of course, enables this feature.

如果超时(我相信是8个小时)已经过去并且连接已终止，则将测试新的连接(如果不再存在，则将创建该连接)并将其提供给Web应用.

If the timeout (8 hours I believe) is elapsed and the connection is dead, then a new connection is tested (if there are none anymore, it is created) and provided to the webapp.

其他可能的方法:

1. 在资源设置中使用testWhileIdle="true" DBCP还可以在检测到有效请求之前检查空闲连接.

1. use the testWhileIdle="true" DBCP in your resource settings to also check idle connections before an effective request is detected.

使用'connectionProperties'加强您的MySQL连接(例如autoReconnect/autoReconnectForPools=true)

Use the 'connectionProperties' to harden your MySQL connection (e.g. autoReconnect/autoReconnectForPools=true)

这篇关于使用DBCP的Tomcat配置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！