[记录]JPA/Hibernate的MySQL长连接问题

 

问题：web app使用JPA配置persistence.xml连接MySQL做持久化，JPA使用的是Hibernate的jar包，web app在过了一夜之后不能正常工作，出现空指针异常SEVERE: Servlet.service() for servlet jsp threw exception java.lang.NullPointerException
出事的persistence.xml如下：

<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd"> <persistence-unit name="DemoUnit" transaction-type="RESOURCE_LOCAL"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <class>com.haojii.model.User</class> <exclude-unlisted-classes>true</exclude-unlisted-classes> <properties> <property name="hibernate.hbm2ddl.auto" value="update" /> <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" /> <property name="hibernate.connection.url" value="jdbc:mysql://localhost:3306/demo?zeroDateTimeBehavior=convertToNull&autoReconnect=true" /> <property name="hibernate.connection.username" value="root" /> <property name="hibernate.connection.password" value="" /> <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" /> <property name="hibernate.show_sql" value="false" /> <property name="hibernate.format_sql" value="true" /> </properties> </persistence-unit> </persistence> 

 

 

RootCause：

MySQL default的wait_timeout和interactive_timeout 时间为8小时，如果没有对JDBC Connection Pool设置的话，超过这个时间数据库连接池的连接就会被MySQL断开。

+-------------------------+---------------------+ | Variable_name | Value | +-------------------------+---------------------+ | protocol_version | 10 | | version | 5.1.37 | | version_comment | Source distribution | | version_compile_machine | ia32 | | version_compile_os | Win32 | +-------------------------+---------------------+ 5 rows in set (0.19 sec) mysql> show VARIABLES like '%timeout%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | connect_timeout | 10 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | innodb_rollback_on_timeout | OFF | | interactive_timeout | 28800 | | net_read_timeout | 30 | | net_write_timeout | 60 | | slave_net_timeout | 3600 | | table_lock_wait_timeout | 50 | | wait_timeout | 28800 | +----------------------------+-------+ 10 rows in set (0.00 sec) 

 

这里跟这个问题相关的两个参数是
wait_timeout : MySQL关闭没有活动的连接的等待时间（秒）
interactive_timeout：MySQL关闭有交互的连接的等待时间（秒）
这个情况下可以这么理解，JDBC连接池初始化了20个连接，其中15个还没有实际的交互，5个有数据库操作正在进行。
wait_timeout作用的是这15个连接，MySQL等待8小时发现这15个连接依旧没有交互的话，就关闭这些连接。
interactive_timeout作用的是5个有交互的连接，从最后一次交互结束算起MySQL等待8小时发现这5个连接没有继续有交互活动的话就关闭这个连接。
解决办法：在persistence.xml中增加配置connection pool的属性参数

<property name="connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" /> <property name="hibernate.c3p0.acquire_increment" value="4" /> <property name="hibernate.c3p0.idle_test_period" value="3000" /> <property name="hibernate.c3p0.max_size" value="100" /> <property name="hibernate.c3p0.max_statements" value="15" /> <property name="hibernate.c3p0.min_size" value="5" /> <property name="hibernate.c3p0.timeout" value="25200" /> <property name="hibernate.c3p0.preferredTestQuery" value="select 1;"/> 

 

c3p0是hibernate用到的开源的数据库连接池，下面是主要参数的解释

• hibernate.c3p0.min_size This is the minimum number of JDBC connections that C3P0 keeps ready at all times
• hibernate.c3p0.max_size This is the maximum number of connections in the pool. An exception is thrown at runtime if this number is exhausted.
• hibernate.c3p0.timeout You specify the timeout period (in this case, 300 seconds) after which an idle connection is removed from the pool).
• hibernate.c3p0.max_statements Maximum Number of statements that will be cached. Caching of prepared statements is essential for best performance with Hibernate.
• hibernate.c3p0.idle_test_periods This is the iddle time in seconds before a connection is automatically validated.

 

参考：
http://www.taobaodba.com/html/433_mysql_timeout_analyze.html
http://stackoverflow.com/questions/3123962/hibernate-and-mysql-timeout-problem-doesnt-work-with-c3p0
http://stackoverflow.com/questions/475893/which-are-the-required-c3p0-settings-for-hibernate-in-order-to-avoid-deadlocks