我有一些连接到 mysql 数据库的 jsp 页面，我间歇性地收到以下错误消息：
“从服务器成功接收的最后一个数据包是在 36,727,995 毫秒前。成功发送到服务器的最后一个数据包是在 36,733,072 毫秒前。比服务器配置的“wait_timeout”值在应用程序中使用之前应考虑过期和/或测试连接有效性，增加服务器配置的值。客户端超时，或使用 Connector/J 连接属性“autoReconnect=true”来避免此问题”
发生模式是相当随机的，但它往往在网站上没有太多活动之后最频繁地发生，例如早晨。
我在 stackoverflow 上发现这个线程提到了同样的错误：
Tomcat6 不能连接到MySql（驱动程序没有收到来自服务器的任何数据包）
，最终的解决方案是添加此行：
permission java.net.SocketPermission "127.0.0.1:3306", "connect";
到
$TOMCAT-CONFIG/policy.d/04webapps.policy
I'm running tomcat 7 on centOS 5.5 and I在任何地方都看不到policy.d目录或04webapps.policy文件。我是否需要手动创建这两个项目，或者是否有其他方法可以解决此问题？
更新：
下面是我如何在一个 jsp 文件中查询数据库的示例：
DBFunctions dbf = null;
boolean bException = false;
ArrayList<String[]> arrayListRows1 = new ArrayList<String[]>();
try
{
dbf = new DBFunctions();
dbf.db_run_query(query2);
while (dbf.rs.next()) {
String [] tmp = new String[6];
tmp[0] = dbf.rs.getString("fact_data.entity_id");
tmp[1] = dbf.rs.getString("date_begin");
tmp[2] = dbf.rs.getString("value");
tmp[3] = dbf.rs.getString("ticker");
tmp[4] = dbf.rs.getString("full_name");
tmp[5] = dbf.rs.getString("name");
arrayListRows1.add(tmp);
}
}
catch (SQLException sqle)
{
out.println(PopulateSpreadsheet.createGoogleError(strReqId,"sql_exception",sqle.getMessage(),"PF ERROR CODE 2eh2-1"));
bException = true;
}
finally
{
if (dbf !=null) dbf.closeConnection();
if (bException == true)
return;
}
DBFunctions.java 位于 Web 应用程序的 src/ 目录下，因此从技术上讲，它不是直接查询数据库的 jsp 页面。
我将返回并再检查一次，但我尝试以相同的方式对所有数据库查询进行编码，并在完成 ResultSet 后立即关闭连接。
关于如何最好地对连接池进行清单以定期检查连接状态有什么建议吗？
更新：我仍然遇到此错误的问题。我很欣赏将大部分编程逻辑从 jsp 页面移出并移入 servlet/库的建议，但我认为这不会解决这个特定问题。
其他人报告说看到了这个问题，这是因为 mysql 使 tomcat jdbc 连接池中的连接超时。对我来说很奇怪的是，tomcat 连接池没有采用某种保持活动的方式来避免这个问题。我认为这将是每个连接池设计的一部分。
I have some jsp pages that connect to a mysql database and I intermittently get the following error message:
"The last packet successfully received from the server was 36,727,995 milliseconds ago. The last packet sent successfully to the server was 36,733,072 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"
The occurrence pattern is pretty random but it tends to happen most frequently after there has not been much activity on the website, like first thing in the morning.
I found this thread on stackoverflow that mentions the same error:
Tomcat6 can't connect to MySql (The driver has not received any packets from the server)
and the final resolution was to add this line:
permission java.net.SocketPermission "127.0.0.1:3306", "connect";
to
$TOMCAT-CONFIG/policy.d/04webapps.policy
I'm running tomcat 7 on centOS 5.5 and I don't see a policy.d directory nor a 04webapps.policy file anywhere. Do I need to manually create both of these or is there another way I should be resolving this issue?
Update:
Here's an example of how I query the database in one of my jsp files:
DBFunctions dbf = null;
boolean bException = false;
ArrayList<String[]> arrayListRows1 = new ArrayList<String[]>();
try
{
dbf = new DBFunctions();
dbf.db_run_query(query2);
while (dbf.rs.next()) {
String [] tmp = new String[6];
tmp[0] = dbf.rs.getString("fact_data.entity_id");
tmp[1] = dbf.rs.getString("date_begin");
tmp[2] = dbf.rs.getString("value");
tmp[3] = dbf.rs.getString("ticker");
tmp[4] = dbf.rs.getString("full_name");
tmp[5] = dbf.rs.getString("name");
arrayListRows1.add(tmp);
}
}
catch (SQLException sqle)
{
out.println(PopulateSpreadsheet.createGoogleError(strReqId,"sql_exception",sqle.getMessage(),"PF ERROR CODE 2eh2-1"));
bException = true;
}
finally
{
if (dbf !=null) dbf.closeConnection();
if (bException == true)
return;
}
DBFunctions.java sits under the src/ directory of the web application, so it's not technically a jsp page that is directly querying the database.
I will go back and check one more time, but I tried to code all my database queries the same way with the connection being closed immediately after being done with the ResultSet.
Any recommendations on how best to do an inventory on the connection pool to periodically check the state of the connections?
Update: I'm still having issues with this error. I appreciate the suggestions on moving the bulk of my programming logic out of my jsp pages and into a servlet/library, but I don't think that will fix this particular issue.
Other people have reported seeing this issue and it's because mysql is timing out connections in the tomcat jdbc connection pool. It's odd to me that the tomcat connection pool doesn't employ some kind of keep alive to avoid this issue. I would think that would be a part of every connection pool design.
发布评论
评论（1）
我能够通过将以下属性添加到 context.xml 来解决该问题：
这些设置会导致连接从池中拉出时进行测试。
I was able to fix the problem by adding the following properties to
context.xml:These settings result in a connection being tested when it is pulled from the pool.