Tomcat / MySQL / Java servlet应用程序在几小时不活动后获得500错误（使用JNDI连接池）(Tomcat/MySQL/Java servlet application ge

Tomcat / MySQL / Java servlet应用程序在几小时不活动后获得500错误（使用JNDI连接池）(Tomcat/MySQL/Java servlet application getting 500 error after some hours of inactivity (using JNDI Connection Pool))

我编写了一个Java Servlet / JSP Web应用程序，它使用MySQL来保存登录身份验证。 Web容器是Tomcat 7.0.47，我正在使用JNDI连接池来实现数据库连接。 我已经在我的Tomcat的context.xml中仔细配置了<Resource...>标签，并且在有大量活动的那天，应用程序的登录功能永远不会失败。 但是在隔夜休息之后，第一次登录尝试经常会在浏览器中抛出500错误。 在同一天的第二次尝试和后续尝试中，登录成功。

我试过了：

1：将MySQL服务器更新到最新版本（在Windows Server 2008中运行）

2：更新mysql-connector-java-5.1.27-bin.jar（最新编写时）

3：从构建路径中删除mysql-connector-java-5.1.27-bin.jar文件（因为我已将它包含在Tomcat lib目录中）

4：在Tomcat context.xml中为标记添加了closeMethod = "close"属性

5：向Tomcat context.xml中的标记添加了属性minIdle = "1"

6：我在context.xml中配置了removeAbandoned = "true"

7：将我的所有应用程序代码更改为Java 7的try-with-resources，以保证关闭应用程序代码中的所有资源

（4,5和6只是在黑暗中真正的谷歌搜索后，他们没有工作）

每天早上我都是第一个登录我的应用程序的人：我得到500，然后在一天的剩余时间里，一切都很好。 我已经看了好几个星期了，但是我没有想法！

I have written a Java Servlet/JSP web app that uses MySQL to hold the login authentication. The web container is Tomcat 7.0.47, and I'm using JNDI connection pooling for the database connectivity. I've carefully configured the <Resource...> tags in my Tomcat's context.xml and during the day when there is plenty of activity the application's login functionality never fails. But after an overnight break, the very first login attempt very often throws a 500 error in the browser. On the second attempt and subsequent attempts in the same day, the login succeeds.

I've tried:

1: updating the MySQL server to the latest version (running in Windows Server 2008)

2: Updating the mysql-connector-java-5.1.27-bin.jar (the latest at time of writing)

3: removing the mysql-connector-java-5.1.27-bin.jar file from the build path (as I've already included it in the Tomcat lib directory)

4: Added the attribute closeMethod = "close" to the tag in Tomcat context.xml

5: Added attribute minIdle = "1" to the tag in Tomcat context.xml

6: I have removeAbandoned = "true" configured in context.xml

7: Changing all my application code to Java 7's try-with-resources to guarantee closing of all resources in the application code

(4, 5 and 6 are just post-Googling stabs in the dark really, and they didn't work)

Every morning I'm the first to log in to my application: I get the 500, then for the rest of the day, everything is fine. I've been looking at this for weeks now but am out of ideas!

最满意答案

尝试将以下两个属性添加到数据源配置中。

testOnBorrow="true" validationQuery="SELECT 1"

第一个， testOnBorrow告诉数据源在将连接交还给应用程序之前验证连接。 第二个， validationQuery是用于验证连接的SQL。 请注意， SELECT 1的SQL是MySQL数据库的有效值。

这应该有希望阻止数据源将dud连接交还给应用程序。 更新后的<Resource>标记应如下所示：

<Resource name="jdbc/login" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" minIdle = "1" maxWait="10000" removeAbandoned="true" logAbandoned="true" username="notRealUser" password="notRealPassword" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://xxx.xxx.xxx.xxx:3306/login" testOnBorrow="true" validationQuery="SELECT 1"/>

Try adding the following two attributes to your datasource configuration.

testOnBorrow="true" validationQuery="SELECT 1"

The first, testOnBorrow tells the datasource to validate the connection before handing it back to the application. The second, validationQuery is the SQL which is used to validate the connection. Note the SQL of SELECT 1 is a valid value for MySQL databases.

That should hopefully prevent the datasource from handing back a dud connection to the application. The updated <Resource> tag should then look like the following:

<Resource name="jdbc/login" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" minIdle = "1" maxWait="10000" removeAbandoned="true" logAbandoned="true" username="notRealUser" password="notRealPassword" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql://xxx.xxx.xxx.xxx:3306/login" testOnBorrow="true" validationQuery="SELECT 1"/>