java 如何避免过时的 MySQL/Hibernate 连接 (MySQLNonTransientConnectionException)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/721923/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to avoid stale MySQL/Hibernate connections (MySQLNonTransientConnectionException)
提问by Gabe Johnson
I have a Java webapp using Hibernate and MySQL. If the site isn't used for a few days, the MySQL connection goes stale, and I am met with the following exception:
我有一个使用 Hibernate 和 MySQL 的 Java webapp。如果该站点几天未使用，则 MySQL 连接将失效，并且会遇到以下异常：
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.
From experience using raw JDBC it is possible to configure the connection to attempt to recover from errors or stale connections, but I don't know how to do this using Hibernate. I am not explicitly calling close() anywhere (but I am betting Hibernate does somewhere deep down in its guts).
根据使用原始 JDBC 的经验，可以将连接配置为尝试从错误或过时的连接中恢复，但我不知道如何使用 Hibernate 执行此操作。我没有在任何地方明确调用 close() （但我打赌 Hibernate 在其内心深处的某个地方）。
Does anybody know what I should do?
有人知道我应该做什么吗？
回答by CoverosGene
What connection pool are you using?
你用的是什么连接池？
The Hibernate suggestionis not to use the built-in pooling, but use the application server datasource or something like Commons DBCPor C3PO.
在Hibernate的建议是不要用内置的池，但使用应用程序服务器的数据源或喜欢的东西下议院DBCP或C3PO。
回答by Kris
I had that problem. Using connection pooling (c3p0) made it go away. It is also a good idea in general to use some connection pooling.
我有那个问题。使用连接池 (c3p0) 使其消失。通常使用一些连接池也是一个好主意。
回答by Gabe Johnson
Thanks for the advice. For posterity's sake, I changed the hibernate.cfg.xml from the following:
感谢您的建议。为了后代的缘故，我从以下更改了 hibernate.cfg.xml：
<property name="connection.url">jdbc:mysql://localhost/FooDB</property>
<property name="connection.username">root</property>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="connection.password">secret</property>
<property name="transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
... to the following:
...到以下内容：
<property name="connection.datasource">java:/comp/env/jdbc/FooDB</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
This also required adding a standard 'context' entry in my web app's context.xml:
这还需要在我的网络应用程序的 context.xml 中添加一个标准的“上下文”条目：
<Resource name="jdbc/FooDB"
auth="Container"
type="javax.sql.DataSource"
maxActive="100"
maxIdle="30"
maxWait="10000"
username="root"
password="secret"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/ss?autoReconnect=true" />
回答by ashweta
We had a similar problem of hibernate mysql connection getting timed out. So we tried C3P0, with the following configuration:
我们有一个类似的问题，即休眠 mysql 连接超时。所以我们尝试了 C3P0，配置如下：
<property name=c3p0.acquire_increment>1</property>
<property name=c3p0.idle_test_period>3600</property>
<property name=c3p0.max_statements>0</property>
<property name=c3p0.min_size>1</property>
<property name=c3p0.timeout>3605</property>
<property name=hibernate.c3p0.preferredTestQuery>select 1;</property>
Hibernate connection_pool size was set to 1.
Hibernate connection_pool 大小设置为 1。
This made the timeout problem go away. But we started facing another problem. Long waits. We have a service (servlet running on jboss), which receives something like 5-6 requests per second. Every request needs to connect to mysql through hibernate. Most of our requests do selects, with an insert/update every 5th-6th request. Normally the request serve time for us is 2-3ms for select and 40-50ms for insert/update. But, after using the above C3P0 configuration, we saw that every request completing after an update was taking almost 4-5 minutes! From our logs, it seemed that randomly a select request will get stuck and will be able to complete only after an update request was received and served.
这使得超时问题消失了。但我们开始面临另一个问题。漫长的等待。我们有一个服务（在 jboss 上运行的 servlet），它每秒接收 5-6 个请求。每个请求都需要通过hibernate连接到mysql。我们的大多数请求都会选择，每 5-6 个请求插入/更新一次。通常我们的请求服务时间是 2-3 毫秒（选择）和 40-50 毫秒（插入/更新）。但是，在使用上述 C3P0 配置后，我们看到每个请求在更新后完成几乎需要 4-5 分钟！从我们的日志中，似乎随机选择请求会卡住，并且只有在收到并提供更新请求后才能完成。
Above problem goes away if we remove the C3P0 config. Can somebody suggest what we could be doing wrong?
如果我们删除 C3P0 配置，上述问题就会消失。有人可以建议我们可能做错了什么吗？
Here is the complete hibernate config for reference:
这是完整的休眠配置以供参考：
<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="connection.driver_class">com.mysql.jdbc.Driver</property>
<property name="connection.url">jdbc:mysql://xxx.xxx.xxx</property>
<property name="connection.username">xxx</property>
<property name="connection.password">xxx</property>
<property name="connection.pool_size">1</property>
<property name="dialect">org.hibernate.dialect.MySQLDialect</property>
<property name="current_session_context_class">thread</property>
<property name="hibernate.cache.use_query_cache">false</property>
<property name="hibernate.cache.use_second_level_cache">false</property>
<property name="show_sql">true</property>
<!-- Transaction isolation 2 = READ_COMMITTED -->
<property name="connection.isolation">2</property>
<property name="connection.autocommit">true</property>
<!-- configuration pool via c3p0-->
<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">3600</property> <!-- seconds -->
<property name="c3p0.max_size">1</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.min_size">1</property>
<property name="c3p0.timeout">3605</property> <!-- seconds -->
<property name="hibernate.c3p0.preferredTestQuery">select 1;</property>
</session-factory>
</hibernate-configuration>
回答by prabu
<property name="c3p0.acquire_increment">1</property>
<property name="c3p0.idle_test_period">120</property> <!-- seconds -->
<property name="c3p0.max_size">100</property>
<property name="c3p0.max_statements">0</property>
<property name="c3p0.min_size">10</property>
<property name="c3p0.timeout">180</property> <!-- seconds -->
override these settings on your config file. It ll helps to you.
在您的配置文件中覆盖这些设置。它会对你有所帮助。
