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

This article mainly introduces "how to solve the problem of database idle connection disconnection caused by firewall in Druid-". In daily operation, it is believed that many people have doubts about how to solve the problem of database idle connection disconnection caused by firewall in Druid-. The editor consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful to answer the question of "how to solve the problem of database idle connection disconnection caused by Druid- firewall". Next, please follow the editor to study!

Problem description

A new project of the company is online and is in the trial stage. Although the project is accessible from the external network, it is deployed in the DMZ area, but the trial phase only gives a small number of employees' addresses and accounts (including some leaders), so the number of visits is very small, but the project is still very important.

During the trial run phase, an exception will be reported in the project application log from time to time, especially when it is first used in the morning and used again after a period of free time. The specific exceptions are as follows:

ERROR [com.alibaba.druid.util.JdbcUtils]-close connection error

Java.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)

At org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin (DataSourceTransactionManager.java:205)

At org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction (AbstractPlatformTransactionManager.java:373)

At org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary (TransactionAspectSupport.java:420)

At org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction (TransactionAspectSupport.java:257)

At org.springframework.transaction.interceptor.TransactionInterceptor.invoke (TransactionInterceptor.java:95)

At org.springframework.aop.framework.ReflectiveMethodInvocation.proceed (ReflectiveMethodInvocation.java:179)

At org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke (ExposeInvocationInterceptor.java:92)

At org.springframework.aop.framework.ReflectiveMethodInvocation.proceed (ReflectiveMethodInvocation.java:179)

At org.springframework.aop.framework.CglibAopProxyDynamicAdvisedInterceptor.intercept (CglibAopProxy.java:644) atxxx.xx.modules.deposit.api.service.DepositApiService

EnhancerBySpringCGLIB

$59c8f6e2.doRecharge ()

At xxx.xx.modules.deposit.FundDepositController.rechargeConfirm (FundDepositController.java:125)

.

Caused by: java.net.SocketException: Broken pipe

At java.net.SocketOutputStream.socketWrite0 (Native Method)

At java.net.SocketOutputStream.socketWrite (SocketOutputStream.java:113)

At java.net.SocketOutputStream.write (SocketOutputStream.java:159)

At oracle.net.ns.DataPacket.send (DataPacket.java:210)

At oracle.net.ns.NetOutputStream.flush (NetOutputStream.java:230)

At oracle.net.ns.NetInputStream.getNextPacket (NetInputStream.java:312)

At oracle.net.ns.NetInputStream.read (NetInputStream.java:260)

At oracle.net.ns.NetInputStream.read (NetInputStream.java:185)

At oracle.net.ns.NetInputStream.read (NetInputStream.java:102)

At oracle.jdbc.driver.T4CSocketInputStreamWrapper.readNextPacket (T4CSocketInputStreamWrapper.java:124)

At oracle.jdbc.driver.T4CSocketInputStreamWrapper.read (T4CSocketInputStreamWrapper.java:80)

At oracle.jdbc.driver.T4CMAREngine.unmarshalUB1 (T4CMAREngine.java:1137)

At oracle.jdbc.driver.T4CTTIfun.receive (T4CTTIfun.java:290)

At oracle.jdbc.driver.T4CTTIfun.doRPC (T4CTTIfun.java:192)

At oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF (T4C7Ocommoncall.java:61)

At oracle.jdbc.driver.T4CConnection.logoff (T4CConnection.java:543)

... 69 more

As can be seen from the exception information, the problem occurs when the Druid database connection pool reports SocketException: Broken pipe when closing the physical database connection, but why does Druid close the database connection and close the data connection to SocketException when it is in use? How much impact does this anomaly have on the system? The following step-by-step analysis.

Step-by-step analysis of the problem 1. Java.net.SocketException: how does the Broken pipe exception occur? What's the impact?

Yes Druid is used in the project to connect to the database, but why try to close the database connection after the system is idle for a period of time, and throw java.net.SocketException: Broken pipe when it is closed?

From the exception stack information, or look at the Druid source code, we can see that the exception occurs when obtaining a connection from the database connection pool, which is used for subsequent database operations. When executing to the DruidDataSource.getConnectionDirect (maxWaitMillis) method, the following logic is used:

If (! validate) {if (LOG.isDebugEnabled ()) {LOG.debug ("skip not validate connection.");} Connection realConnection = poolalbeConnection.getConnection (); discardConnection (realConnection); continue }} else {Connection realConnection = poolalbeConnection.getConnection (); / / if the connection has been closed, get an if (realConnection.isClosed ()) {discardConnection (null) from the pool; / / pass in null to avoid repeatedly shutting down continue } / / testWhileIdle is true, that is, you need to check the connection if (isTestWhileIdle ()) {/ / connection idle time (current time-last ActiveTime) long idleMillis = System.currentTimeMillis ()-poolalbeConnection.getConnectionHolder () .getLastActiveTimeMillis () / / connection idle time > timeBetweenEvictionRunsMillis, check the connection if (idleMillis > = this.getTimeBetweenEvictionRunsMillis ()) {boolean validate = testConnectionInternal (poolalbeConnection.getConnection ()) / / connection check failed. Print log, discard the connection, and get another connection if (! validate) {if (LOG.isDebugEnabled ()) {LOG.debug ("skip not validate connection.") } discardConnection (realConnection); continue;} / / if connection timeout recovery if (isRemoveAbandoned ()) {StackTraceElement [] stackTrace = Thread.currentThread () .getStackTrace () PoolalbeConnection.setConnectStackTrace (stackTrace); poolalbeConnection.setConnectedTimeNano (); / / set the current time to ConnectedTime poolalbeConnection.setTraceEnable (true); synchronized (activeConnections) {activeConnections.put (poolalbeConnection, PRESENT); / / put the connection into activeConnections Map}} if (! this.isDefaultAutoCommit ()) {poolalbeConnection.setAutoCommit (false) } return poolalbeConnection;}}

To put it simply, when you get a database connection from Druid, you can do test, which contains the logic of testOnBorrow (check when loaned) and testWhileIdle (check when idle). This project is in the configuration file.

TestOnBorrow = false

TestWhileIdle = true

TimeBetweenEvictionRunsMillis = 60000 (60s)

Therefore, it will only be detected when it is used again after the connection is idle for 60s, which is actually executing a SQL. If it fails to execute SQL, it will call JdbcUtils.close (realConnection) to close the connection and throw a SocketException exception when closing the connection. But in fact, this exception will not have much impact on programs that want to get Connection to execute SQL queries, because the exception is caught in the JdbcUtils.close () method, prints the log, and does not throw it.

Public static void close (Connection x) {if (x = = null) {return;} try {x.close ();} catch (Exception e) {LOG.debug ("close connection error", e);}}

So 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?

2. Why is the database TCP connection disconnected?

At first, I was puzzled, thinking that it was because the Oracle database actively disconnected? For some reason, such as too many connections from the server to the database? Obviously not, this project is still in the trial run stage, not many people use it, and observing the connection pool monitoring of Druid, only a few connections are generally established.

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.

The reason for the disconnection of the database has been found, so this is a typical problem encountered in a scenario where the connection between an application and a database needs to go through a firewall in a different network. How to maintain a certain number of long-term connections between the application and the database even if they are relatively idle is urgent to be solved.

3. The impact of the firewall cutting off the database connection

The database session is executing a time-consuming SQL

Before severing the connection, the Oracle session corresponding to the connection is executing a very time-consuming SQL, such as a stored procedure in which no data is output to the client, so that if the TCP connection has been interrupted by the firewall after the SQL execution is completed, the TCP connection will obviously be interrupted, and the session will be interrupted. But the client doesn't know it yet and will always be waiting for the server to return the result.

If the client does not have a connection recovery mechanism for this kind of time-consuming SQL, the client connection will be waiting all the time. If the client keeps executing this time-consuming SQL, the client will accumulate more and more waiting connections.

The removeAbandoned-related configuration and logic of Druid connection pool is designed to solve this kind of connection recycling setting.

Database session idle

The Oracle session is idle until the connection is severed. When the client submits the SQL to the Oracle server after the firewall is interrupted, the client detects a connection break because the TCP connection has been interrupted, then the client will report an error such as ORA-03113/ORA-03114, and then the session will be interrupted. But on the Oracle server side, the session is always in a state of waiting for client messages.

As for Druid, which has the detection mechanism of testOnBorrow and testWhileIdle, and the connection pool can be re-established if the detection fails, the idle connections cut off by the firewall will be rebuilt continuously later, while on the database server side, there will be more and more connections, that is, the number of sessions will become more and more, even exceeding the maximum number of data connections.

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.

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.

At this point, the study on "how to solve the problem of database idle connection disconnection caused by Druid- firewall" is over. I hope to be able to solve everyone's doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.