MySQL - Using Hibernate to connect to the MySQL database, the MySQL connection times out and is disconnected

One of the recent headaches is that the server will have an Exception about the database connection at an uncertain time. The general Exception is as follows:

org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08S01  
org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was43200 milliseconds ago.The last packet sent successfully
 to the server was 43200 milliseconds ago, which 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 'autoReconnect=true' to avoid this problem.  
org.hibernate.event.def.AbstractFlushingEventListener - Could not synchronize database state with session  
org.hibernate.exception.JDBCConnectionException: Could not execute JDBC batch update  
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Connection.close() has already been called. Invalid operation in this state.  
org.hibernate.util.JDBCExceptionReporter - SQL Error:0, SQLState: 08003  
org.hibernate.util.JDBCExceptionReporter - No operations allowed after connection closed. Connection was implicitly closed due to underlying exception/error:  
  
** BEGIN NESTED EXCEPTION **  
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException

Let me first talk about the general reason for this Exception:

In the configuration of MySQL, there is a parameter called "wait_timeout". The general meaning of this parameter is this: when a client connects to the MySQL database, if the client does not If you disconnect it yourself without doing any operation, the MySQL database will keep the connection for "wait_timeout" for so long (the unit is s, the default is 28800s, which is 8 hours). After this time is exceeded, in order to save resources, the MySQL database will The connection will be disconnected on the database side; of course, during this process, if the client performs any operations on this connection, the MySQL database will restart calculating the time.

It seems that the reason why the above Exception occurred is because the connection between my server and the MySQL database exceeded the "wait_timeout" time, and the MySQL server disconnected it, but when my program uses this connection again I didn’t make any judgment, so I just hung up.

So how to solve this problem?

In the process of thinking of solutions, I found several problems that confused me:

The first question: Our server had considered this matter during the design process , so the main thread of the server has a scheduled check mechanism, which sends a "select 1" to the database every half hour to ensure that the connection is active. Why does this check mechanism not work?

Second question: From the above Exception, we can get this information:

The last packet sent successfully to the server was 43200 milliseconds ago, which is longer than the server configured value of 'wait_timeout'.

This information is very clear. The last packet successfully sent to the Server was 43200 milliseconds ago. But 43200 milliseconds is only 43.2 seconds, which means that our server only communicated with the MySQL server 43.2 seconds ago. How could the problem of exceeding "wait_timeout" occur? Moreover, the configuration of the MySQL database is indeed 28800 seconds (8 hours). Is this another situation?

I have been googling online for a long time, and there are a lot of discussions about this issue, but I have never found a method that I think is effective. I can only think about it slowly by combining the results of google.

First of all, the solution on the MySQL database side is very simple, which is to extend the value of "wait_timeout". I have seen some people directly extend it to one year, and some people say that the maximum value is 21 days. Even if the value is set to a larger value, MySQL will only recognize 21 days (I did not specifically check this in the MySQL documentation). ). But this is a temporary solution rather than a permanent solution. Even if it can be used for a year, there will still be interruptions. The server needs to be online 24/7.

Since there is no good method on the MySQL database side, the next step is to do it from the program side.

Let’s first talk about the general structure of the program: two threads, one thread is responsible for querying and the check mechanism mentioned above, and the other thread is responsible for regularly updating the database. Using hibernate, the configuration is very simple. The most basic, there is no configuration about the connection pool and cache, just like the following:

<session-factory>  
    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>  
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>  
    <property name="hibernate.connection.useUnicode">true</property>  
    <property name="hibernate.connection.characterEncoding">UTF-8</property>  
    <property name="hibernate.show_sql">true</property>  
    <!-- 以下就全是mapping了，省略 -->  
</session-factory>

The update process in the program is roughly like this:

session = org.hibernate.SessionFactory.openSession();  
transaction = session.beginTransaction();  
session.update(something);  
transaction.commit();  
session.close();

Here, everything about The connection and closing of the database Connection are all in Hibernate, so it is impossible not to dig into the source code of Hibernate.

Before mining the hibernate source code, the goal must be clear: what to mine?

In fact, my goal is very clear. Since the disconnection is done by the MySQL database, the problem with our program is that we do not call Connection.close() after using the connection, so it will be retained. A long connection is there. So, when did Hibernate open this connection, and when did it call Connection.close()?

The next step is to dig into the source code of Hibernate. . .

I won’t talk about the boring process, but let’s talk about what was discovered:

Hibernate (I forgot to mention it, the Hibernate version we used is 3.3.2) has the above configuration Below, there will be a default connection pool named: DriverManagerConnectionProvider; this is an extremely simple connection pool, and 20 connections will be reserved in the pool by default. These connections are not created when Hibernate is initialized at the beginning, but It is created when you need to use the connection and is added to the pool after use. There is a method called closeConnection(Connection conn). This method is very NB. It directly puts the incoming connection into the pool without any processing. The connection pool inside this class is actually an ArrayList. Each time it is obtained, the first connection of the ArrayList is removed. After use, it is directly added to the end of the ArrayList using the add method.

When our program is updated, Hibernate will get a connection Connection through DriverManagerConnectionProvider. After using it, when calling session.close(), Hibernate will call the closeConnection method of DriverManagerConnectionProvider (the NB method mentioned above) , at this time, the connection will be directly placed in the ArrayList of DriverManagerConnectionProvider, and there is no place to call the close method of Connection from beginning to end.

At this point, the problem is obvious.

第一，我们的那个”select 1“的check机制和我们服务器程序中更新的逻辑是两个线程，check机制工作时，它会向DriverManagerConnectionProvider获取一个连接，而此时更新逻辑工作时，它会向DriverManagerConnectionProvider获取另外一个连接，两个逻辑工作完之后都会将自己获得的连接放回DriverManagerConnectionProvider的池中，而且是放到那个池的末尾。这样，check机制再想check这两个连接就需要运气了，因为更新逻辑更新完之后就把连接放回池中了，而更新逻辑是定时的，check机制也是定时的，两个定时机制如果总是能错开，那么check机制check的永远都是两个中的一个连接，另外一个就麻烦了。这也就是为什么check机制不好使的原因。

第二，关于Exception信息中那个43200毫秒的问题也就能说明白了，check机制check的总是一个连接，而另外一个过期的连接被更新线程拿跑了，并且在check机制之后没多久就有更新发生，43200毫秒恐怕就是它们之间的间隔吧。

到这里问题分析清楚了，怎么解决呢？

最容易想到的方案，也是网上说的最多的方案，就是延长MySQL端”wait_timeout“的时间。我说了，治标不治本，我觉得不爽，不用。

第二个看到最多的就是用”autoReconnect = true"这个方案，郁闷的是MySQL 5之后的数据库把这个功能给去了，说会有副作用（也没具体说有啥副作用，我也懒得查），我们用的Hibernate 3.3.2这个版本也没有autoReconnect这个功能了。

第三个说的最多的就是使用c3p0池了，况且Hibernate官网的文档中也提到，默认的那个连接池非常的屎，仅供测试使用，推荐使用c3p0（让我郁闷的是我连c3p0的官网都没找到，只在sourceForge上有个项目主页）。好吧，我就决定用c3p0来搞定这个问题。

用c3p0解决这个Exception问题
首先很明了，只要是池它就肯定有这个问题，除非在放入池之前就把连接关闭，那池还顶个屁用。所以我参考的博客里说到，最好的方式就是在获取连接时check一下，看看该连接是否还有效，即该Connection是否已经被MySQL数据库那边给关了，如果关了就重连一个。因此，按照这个思路，我修正了Hibernate的配置文件，问题得到了解决：

<session-factory>  
    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>  
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>  
    <property name="hibernate.connection.useUnicode">true</property>  
    <property name="hibernate.connection.characterEncoding">UTF-8</property>  
    <property name="hibernate.show_sql">true</property>  
    <!-- c3p0在我们使用的Hibernate版本中自带，不用下载，直接使用 -->  
    <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>  
    <property name="hibernate.c3p0.min_size">5</property>  
    <property name="hibernate.c3p0.max_size">20</property>  
    <property name="hibernate.c3p0.timeout">1800</property>  
    <property name="hibernate.c3p0.max_statements">50</property>  
    <!-- 下面这句很重要，后面有解释 -->  
    <property name="hibernate.c3p0.testConnectionOnCheckout">true</property>  
    <!-- 以下就全是mapping了，省略 -->  
</session-factory>

上面配置中最重要的就是hibernate.c3p0.testConnectionOnCheckout这个属性，它保证了我们前面说的每次取出连接时会检查该连接是否被关闭了。不过这个属性会对性能有一些损耗，引用我参考的博客上得话：程序能用是第一，之后才是它的性能（又不是不能容忍）。

当然，c3p0自带类似于select 1这样的check机制，但是就像我说的，除非你将check机制的间隔时间把握的非常好，否则，问题是没有解决的。

好了，至此，困扰我的问题解决完了。希望上面的这些整理可以为我以后碰到类似的问题留个思路，也可以为正在被此问题困扰的人提供一丝帮助

以上就是 MySQL之—— 使用Hibernate连接MySQL数据库，MySQL连接超时断开的问题的内容，更多相关内容请关注PHP中文网（www.php.cn）！