Hibernate、C3P0、Mysql——管道破裂

Hibernate, C3P0, Mysql -- Broken Pipe

提问by Mark

MySQL seems to have an 8 hour time out on its connections. I'm running multiple WARs in Tomcat utilizing Hibernate for ORM. After 8 hours (i.e. overnight), I get broken pipes when it picks up an idle connection.

MySQL 的连接似乎有 8 小时的超时时间。我正在使用 Hibernate for ORM 在 Tomcat 中运行多个 WAR。8 小时后（即一夜之间），当它获得空闲连接时，我的管道损坏了。

I've already traced through the code and made doubly sure I commit or rollback all transactions.

我已经对代码进行了跟踪，并加倍确保我提交或回滚了所有事务。

Here is my hibernate.cfg.xml

这是我的 hibernate.cfg.xml

<?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="hibernate.bytecode.use_reflection_optimizer">false</property>
    <property name="hibernate.connection.driver_class">org.gjt.mm.mysql.Driver</property>
    <property name="hibernate.connection.password"></property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost/test</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>
    <property name="hibernate.transaction.factory_class">org.hibernate.transaction.JDBCTransactionFactory</property>
    <property name="hibernate.current_session_context_class">thread</property>
    <!--property name="hibernate.show_sql">true</property>
    <property name="hibernate.format_sql">true</property-->

    <property name="c3p0.min_size">3</property>
    <property name="c3p0.max_size">5</property>
    <property name="c3p0.timeout">1800</property>
    <property name="c3p0.preferredTestQuery">SELECT 1</property>
    <property name="c3p0.testConnectionOnCheckout">true</property>
    <property name="c3p0.idle_test_period">100</property> <!-- seconds -->

    <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>
    <property name="cache.use_query_cache">false</property>
    <property name="cache.use_minimal_puts">false</property>
    <property name="max_fetch_depth">10</property>

    <property name="hibernate.hbm2ddl.auto">update</property>

    <!-- classes removed -->

</session-factory>

The parameter I thought would have fixed it was the c3p0.idle_test_period-- It defaults to 0. However, we still have the Broken Pipe issue after 8 hours of running. While there are multiple posts index via Google, none arrive at a satisfactory answer.

我认为可以修复它的参数是c3p0.idle_test_period-- 它默认为 0。但是，在运行 8 小时后，我们仍然遇到管道损坏问题。虽然通过谷歌有多个帖子索引，但没有一个得到满意的答案。

回答by Mark

So it turns out I was missing a key line that enabled c3p0 (the c3p0 parameters I was tweaking were having no effect because Hibernate was using it's built in connection pool -- which it appropriately warns is not suitable for production). In hibernate 2.x, setting the hibernate.c3p0.max_sizeproperty enabled c3p0 connection pooling. However, in 3.x you must specify the following property --

所以事实证明我错过了启用 c3p0 的关键行（我正在调整的 c3p0 参数没有影响，因为 Hibernate 使用它内置的连接池——它适当地警告不适合生产）。在 hibernate 2.x 中，设置hibernate.c3p0.max_size属性启用 c3p0 连接池。但是，在 3.x 中，您必须指定以下属性——

<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>

Additionally, here are my final configuration parameters --

另外，这是我的最终配置参数——

<property name="hibernate.c3p0.min_size">3</property>
<property name="hibernate.c3p0.max_size">5</property>
<property name="hibernate.c3p0.timeout">1800</property>
<property name="hibernate.c3p0.idle_test_period">100</property> <!-- seconds -->

It's rather unfortunate that both Hibernate and c3p0 have abysmal documentation in this regard.

不幸的是，Hibernate 和 c3p0 在这方面都有糟糕的文档。

回答by Matt Solnit

There are two things going on here. You should read this articlefor more details, but the take-aways are:

这里有两件事正在发生。您应该阅读本文以了解更多详细信息，但要点是：

1. You can adjust the MySQL wait_timeoutsetting to something larger than 8 hours, if desired.
2. The Hibernate settings should include "hibernate." before the "c3p0", e.g. hibernate.c3p0.idle_test_periodinstead of just c3p0.idle_test_period

1. 如果需要，您可以将 MySQL wait_timeout设置调整为大于 8 小时。
2. 休眠设置应包括“休眠”。在“c3p0”之前，例如hibernate.c3p0.idle_test_period而不是仅仅c3p0.idle_test_period

回答by despot

This is a solution when you have a broken pipe because of combination of tomcat's wait_timeout=28800 sec(8h) and maxIdleTime=0 in c3p0:

这是因为 c3p0 中 tomcat 的 wait_timeout=28800 sec(8h) 和 maxIdleTime=0 的组合而导致管道损坏时的解决方案：

I've changed the local tomcat wait_timeout via my.ini file to 120sec (2 min). And I placed the following:
maxIdleTime=100
idleConnectionTestPeriod=0 (same as default/as if it didn't exist)
other:
acquireIncrement=2
minPoolSize=2
maxPoolSize=5
maxIdleTimeExcessConnections=10

我已通过 my.ini 文件将本地 tomcat wait_timeout 更改为 120 秒（2 分钟）。我放置了以下内容：
maxIdleTime=100
idleConnectionTestPeriod=0（与默认值相同/就好像它不存在一样）
其他：
acquireIncrement=2
minPoolSize=2
maxPoolSize=5
maxIdleTimeExcessConnections=10

I had no problems with this setup.

我对这个设置没有任何问题。

I didn't need to use idleConnectionTestPeriod!

我不需要使用 idleConnectionTestPeriod！

If tomcat's wait_timeout is 28800 sec, and maxIdleTime is 25200, it means that c3p0 will close the idle connection in 3600sec (1h) earlier, before tomcat throws a "broken pipe" exception. Isn't that right?!

如果tomcat的wait_timeout为28800秒，maxIdleTime为25200，说明c3p0会提前3600秒（1小时）关闭空闲连接，然后tomcat抛出“断管”异常。是不是这样？！

As you can see I have no issues with providing only maxIdleTime.

如您所见，我对仅提供 maxIdleTime 没有任何问题。

Unfortunately, these:
maxIdleTime
idleConnectionTestPeriod
configuring_connection_testing
testConnectionOnCheckin
don't explain too much the corner cases.

不幸的是，这些：
maxIdleTime
idleConnectionTestPeriod
configuring_connection_testing
testConnectionOnCheckin
不会解释太多的极端案例。

And, btw, here is how to open the tomcat's my.ini file with Notepad++: http://drupal.org/node/32715#comment-4907440

顺便说一句，这里是如何用 Notepad++ 打开 tomcat 的 my.ini 文件：http: //drupal.org/node/32715#comment-4907440

Cheers,
Despot

干杯，
暴君

回答by chaitanya89

I was getting the same problem and it took time to figure out the solution.

我遇到了同样的问题，需要时间来找出解决方案。

I use Hibernate 4.0.1 and mysql 5.1(no spring framework) and I was facing the issue. First make sure that you configured the c3p0 jars properly which are essential.

我使用 Hibernate 4.0.1 和 mysql 5.1（没有 spring 框架），我遇到了这个问题。首先确保您正确配置了 c3p0 jar，这是必不可少的。

I used these properties in hibernate.cfg.xml

我在 hibernate.cfg.xml 中使用了这些属性

<property name="hibernate.c3p0.validate">true</property>
<property name="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property>
<property name="hibernate.c3p0.min_size">5</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.preferredTestQuery">SELECT 1;</property>
<property name="hibernate.c3p0.testConnectionOnCheckout">true</property>
<property name="hibernate.c3p0.idle_test_period">10</property>
<property name="hibernate.c3p0.acquireRetryAttempts">5</property>
<property name="hibernate.c3p0.acquireRetryDelay">200</property>
<property name="hibernate.c3p0.timeout">40</property>

But it's of no use 'cause C3p0 was still taking the default properties not the properties which I set in hibernate.cfg.xml, You can check it in logs. So, I searched many websites for right solution and finally I came up with this. remove the C3p0 properties in cfg.xml and create c3p0-config.xml in the root path(along with cfg.xml) and set properties as follows.

但它没有用，因为 C3p0 仍然采用默认属性，而不是我在 hibernate.cfg.xml 中设置的属性，您可以在日志中检查它。所以，我在很多网站上搜索了正确的解决方案，最后我想出了这个。删除 cfg.xml 中的 C3p0 属性并在根路径中创建 c3p0-config.xml（连同 cfg.xml）并按如下方式设置属性。

<c3p0-config>
<default-config> 
<property name="automaticTestTable">con_test</property>
<property name="checkoutTimeout">40</property> 
<property name="idleConnectionTestPeriod">10</property> 
<property name="initialPoolSize">10</property>
<property name="maxPoolSize">20</property> 
<property name="minPoolSize">5</property> 
<property name="maxStatements">50</property>
<property name="preferredTestQuery">SELECT 1;</property>
<property name="acquireRetryAttempts">5</property>
<property name="acquireRetryDelay">200</property>
<property name="maxIdleTime">30</property>
</default-config>
</c3p0-config>

but if you run, ORM takes the jdbc connection but not C3p0 connection pool 'cause we should add these properties in hibernate.cfg.xml

但是如果你运行，ORM 会使用 jdbc 连接而不是 C3p0 连接池，因为我们应该在 hibernate.cfg.xml 中添加这些属性

<property name="hibernate.c3p0.validate">true</property>

<property name="hibernate.connection.provider_class">org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider</property>

now everything works fine(At least it worked fine for me) and the issue is solved.

现在一切正常（至少对我来说很好）并且问题解决了。

check the following for references.

检查以下内容以获取参考。

http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testing

http://www.mchange.com/projects/c3p0/index.html#configuring_connection_testing

https://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool

https://community.jboss.org/wiki/HowToConfigureTheC3P0ConnectionPool

I hope this solves your problem.

我希望这能解决你的问题。

回答by Imtiaz Mirza

I've several problem - - C3P0ConnectionProvider was not found - I solve it by using the hibernate c3p0 version

我有几个问题 - - 没有找到 C3P0ConnectionProvider - 我使用 hibernate c3p0 版本解决了它

        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-entitymanager</artifactId>
        <version>3.5.6-Final</version>
    </dependency>
          <!-- c3p0 -->
    <dependency>
        <groupId>org.hibernate</groupId>
        <artifactId>hibernate-c3p0</artifactId>
        <version>3.3.1.GA</version>
    </dependency>

- I have that wait_timeout issue on mysql. First I set /etc/my.cnf wait_timeout=10 then I changed the Idle time out value to lower than the wait_timeout value which < 10 That solved my problem.

- 我在 mysql 上遇到了 wait_timeout 问题。首先我设置 /etc/my.cnf wait_timeout=10 然后我将空闲超时值更改为低于 wait_timeout 值 < 10 这解决了我的问题。

    <property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" /> <property name="hibernate.c3p0.acquire_increment" value="1" />
        <property name="hibernate.c3p0.idle_test_period"  value="28690"/>
        <property name="hibernate.c3p0.timeout" value="1800" />
        <property name="hibernate.c3p0.max_size" value="5" />
        <property name="hibernate.c3p0.min_size" value="3" />
        <property name="hibernate.c3p0.max_statement" value="50" />
        <property name="hibernate.c3p0.preferredTestQuery" value="select 1;"/>