Shulou(Shulou.com)06/02 Report--

It is believed that many inexperienced people do not know what to do to prevent the problem of database idle connection disconnection caused by firewall. therefore, this paper summarizes the causes and solutions of the problem. I hope you can solve this problem through this article.

ERROR [com.alibaba.druid.util.JdbcUtils]-close connection errorjava.sql.SQLRecoverableException: IO Error: Broken pipe at oracle.jdbc.driver.T4CConnection.logoff (T4CConnection.java:556) at oracle.jdbc.driver.PhysicalConnection.close (PhysicalConnection.java:3984) at com.alibaba.druid.filter.FilterChainImpl.connection_close (FilterChainImpl.java:167) at com.alibaba.druid.filter.stat.StatFilter.connection_close (StatFilter.java:254) at com.alibaba.druid .filter.FilterChainImpl.connection _ close (FilterChainImpl.java:163) at com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl.close (ConnectionProxyImpl.java:115) at com.alibaba.druid.util.JdbcUtils.close (JdbcUtils.java:79) at com.alibaba.druid.pool.DruidDataSource.discardConnection (DruidDataSource.java:965) at com.alibaba.druid.pool.DruidDataSource.getConnectionDirect (DruidDataSource.java:932) at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect (FilterChainImpl. Java:4534) at com.alibaba.druid.filter.stat.StatFilter.dataSource_getConnection (StatFilter.java:661) at com.alibaba.druid.filter.FilterChainImpl.dataSource_connect (FilterChainImpl.java:4530) at com.alibaba.druid.pool.DruidDataSource.getConnection (DruidDataSource.java:884) at com.alibaba.druid.pool.DruidDataSource.getConnection (DruidDataSource.java:876) at com.alibaba.druid.pool.DruidDataSource.getConnection (DruidDataSource.java:92)

Java.net.SocketException: what does Broken pipe mean?

In fact, the tcp connection to the database is disconnected for some reason, resulting in a "pipe rupture". In general, the database connection pool maintains a long connection with the database, eliminating the process of establishing a connection when needed and using it directly, but why are these idle connections disconnected? Disconnected by who?

Why is the database TCP connection disconnected?

Later, in the process of discussion with colleagues, I learned that a similar situation had happened to other project teams, and what they had in common with this project was that the services were all in the DMZ area and could be accessed through the external network, while the database was in the internal network, so it needed to go through a firewall to access the database. So I went to my colleagues responsible for maintaining the network and the firewall to understand that the firewall had a TCP timeout, which is currently set to half an hour. Its meaning is that for all TCP connections that pass through the firewall, if there is no activity within half an hour, it will be removed by the firewall, which will lead to a break in the connection. When a connection is dismantled, no data is sent to both ends of the connection to notify that the connection has been dismantled.

Solution 1. Adjust the connection cut-off time of the firewall

This is a temporary solution, such as adjusting the connection timeout of the firewall to 8 hours, which can avoid the disconnection of idle connections as far as possible, but cannot be avoided completely, because it is impossible to predict how long the connection will be idle. If your system is not always accessed, then the connection will be cut off sooner or later because of idleness, resulting in some unpredictable problems, and increasing the timeout is just a relief.

2. Tcp keepalive function

In fact, the keepalive of tcp is used to maintain the tcp connection. Its principle is simply that if a TCP connection is inactive within a specified period of time, it will send a probe packet to the peer of the connection to check whether the peer of the connection still exists. If the peer still does not respond to the probe within a certain period of time, it will send the probe packet again. After several times, the connection is considered invalid. Close the local connection.

Tcp keepalive is not enabled by default. You can set tcp keepalive to true when developing the program, so that the probe will be started if the tcp connection does not transmit any data messages within a certain period of time. This time is generally specified by the operating system. The Linux system can be modified by setting net.ipv4.tcp_keepalive_time. The default is 7200 seconds, that is, 2 hours. Of course, you can also set this time for the current socket when programming, but it seems that only keepalive=true can be set in Java's Socket API, but tcp_keepalive_time cannot be set.

When tcp keepalive is set, as long as the tcp probe packet is sent for less than the connection timeout of the firewall, the firewall will check that there is still data transmission in the connection and will not disconnect the connection.

Database tcp connections created with JDBC do not have keepalive set, which can be verified on the database client (that is, the application side) through the netstat or ss command of Linux

Use the command netstat-ano or ss-ano, where the parameter o shows the timer timer. The timer timer can keep the connection alive when the connection is established.

The netstat command displays off for tcp connections that do not have keepalive turned on: off (0.00UniPax 0)

The ss command does not display the timer timer for tcp connections without keepalive

3. DCD of Oracle database

Oracle provides a mechanism similar to tcp keepalive, that is, DCD (Dead Conneciton Detection). Add the following line to the $ORACLE_HOME/network/admin/sqlnet.ora file:

Sqlnet.expire_time=NNN

Here NNN is the number of minutes. When the session IDLE time exceeds this specified time, the Oracle database will check whether the peer (that is, the client) of the session is still valid. Avoid the persistent existence of the session due to the abnormal exit of the client.

Similarly, if the DCD takes less time than the firewall to cut off the idle connection, the connection can be maintained all the time.

4. The program executes the query from time to time

The above methods either make use of the keepalive feature of tcp connection, or use the idle connection detection on the database side, and we can also actively do this kind of heartbeat detection in our program.

Druid database connection pool starts from 1.0.28. The druid.keepAlive attribute is added and is disabled by default.

After druid.keepAlive is turned on, when the connection pool is idle, if the number of connections within the number of minIdle in the pool is idle, and the idle time exceeds minEvictableIdleTimeMillis, the keepAlive operation will be performed, that is, the query SQL specified by druid.validationQuery will be executed, which is generally select * from dual. As long as the minEvictableIdleTimeMillis setting is less than the firewall disconnection time, you can ensure that live detection will be done automatically when the connection is idle, and will not be cut off by the firewall.

After reading the above, have you mastered the method to prevent the problem of database idle connection disconnection caused by firewall? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.