Loading

How to deal with closed connections in database pool

Date de publication: Mar 2, 2024
Résolution

SYMPTOM

You have a mule application which connects to a database. From time to time, you see an exception on the mule logs, that is similar to the following one:
  
[2017-03-17 18:04:17.883] ERROR   org.mule.exception.DefaultMessagingExceptionStrategy [[nvm-fetch-records].nvm-fetch-recordsFlow.stage1.02]: Exception(s) were found for route(s):
Route 0:
********************************************************************************
Message               : The connection is closed. (com.microsoft.sqlserver.jdbc.SQLServerException).
Element               : /nvm-fetch-recordsFlow/processors/5/main-flow-processing/subprocessors/3/nvm-process-get-data-save-db/subprocessors/1/0/1/agent-status-subflow/subprocessors/6/1 @ nvm-fetch-records:null:null
--------------------------------------------------------------------------------
Exception stack is:
The connection is closed. (com.microsoft.sqlserver.jdbc.SQLServerException). (org.mule.api.MessagingException)
  com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
  com.microsoft.sqlserver.jdbc.SQLServerConnection.checkClosed(SQLServerConnection.java:389)
  com.microsoft.sqlserver.jdbc.SQLServerStatement.checkClosed(SQLServerStatement.java:985)
  com.microsoft.sqlserver.jdbc.SQLServerStatement.clearBatch(SQLServerStatement.java:1670)
  org.mule.module.db.internal.domain.executor.BulkUpdateExecutor.execute(BulkUpdateExecutor.java:58)
 

Another stack trace error example could be:

[2020-12-22 16:23:19.367] INFO com.mchange.v2.c3p0.impl.NewPooledConnection [C3P0PooledConnectionPoolManager[identityToken->1bqp8gpae122d6bhdjnftp|30e703df]-HelperThread-#0]: [c3p0] Exceptions occurred while trying to close a PooledConnection's resources normally.
[2020-12-22 16:23:19.367] INFO com.mchange.v2.c3p0.impl.NewPooledConnection [C3P0PooledConnectionPoolManager[identityToken->1bqp8gpae122d6bhdjnftp|30e703df]-HelperThread-#0]: [c3p0] NewPooledConnection close Exception.
java.sql.SQLRecoverableException: IO Error: Broken pipe
at oracle.jdbc.driver.T4CConnection.logoff(T4CConnection.java:949)
at oracle.jdbc.driver.PhysicalConnection.close(PhysicalConnection.java:2125)
at com.mchange.v2.c3p0.impl.NewPooledConnection.close(NewPooledConnection.java:642)
at com.mchange.v2.c3p0.impl.NewPooledConnection.closeMaybeCheckedOut(NewPooledConnection.java:255)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.destroyResource(C3P0PooledConnectionPool.java:622)
at com.mchange.v2.resourcepool.BasicResourcePool$1DestroyResourceTask.run(BasicResourcePool.java:1076)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:696)
Caused by: java.io.IOException: Broken pipe
at sun.nio.ch.FileDispatcherImpl.write0(Native Method)
at sun.nio.ch.SocketDispatcher.write(SocketDispatcher.java:47)
at sun.nio.ch.IOUtil.writeFromNativeBuffer(IOUtil.java:93)
at sun.nio.ch.IOUtil.write(IOUtil.java:65)
at sun.nio.ch.SocketChannelImpl.write(SocketChannelImpl.java:468)
at oracle.net.nt.TimeoutSocketChannel.write(TimeoutSocketChannel.java:207)
at oracle.net.ns.NIOPacket.writeToSocketChannel(NIOPacket.java:308)
at oracle.net.ns.NIONSDataChannel.writeDataToSocketChannel(NIONSDataChannel.java:182)
at oracle.net.ns.NIONSDataChannel.writeDataToSocketChannel(NIONSDataChannel.java:133)
at oracle.jdbc.driver.T4CMAREngineNIO.flush(T4CMAREngineNIO.java:741)
at oracle.jdbc.driver.T4CMAREngineNIO.prepareForUnmarshall(T4CMAREngineNIO.java:754)
at oracle.jdbc.driver.T4CMAREngineNIO.unmarshalUB1(T4CMAREngineNIO.java:427)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:394)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:255)
at oracle.jdbc.driver.T4C7Ocommoncall.doOLOGOFF(T4C7Ocommoncall.java:62)


And yet another error case could be: 
10:02:07.339     02/22/2021     Worker-0     [MuleRuntime].io.39: [mulesoft-client-login-proc-api-uat].post:\clientLogin\email-address-exists:api-prc-mulesoft-client-login-config.BLOCKING @48e02b0d     ERROR
event:eb6ba8a0-74c6-11eb-acb1-06cf9f54dfa0 
********************************************************************************
Message               : Connection timed out (Write failed).
Element               : post-email-address-exists-main-sub-Flow/processors/2/route/0/processors/0 @ mulesoft-client-login-proc-api-uat:resources/post-email-address-exists.xml:44 (Call to CheckEmailAddressExists Stored procedure)
--------------------------------------------------------------------------------
Root Exception stack trace:
java.net.SocketException: Connection timed out (Write failed)
	at java.net.SocketOutputStream.socketWrite0(Native Method)
	at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:111)
	at java.net.SocketOutputStream.write(SocketOutputStream.java:155)
	at com.microsoft.sqlserver.jdbc.TDSChannel.write(IOBuffer.java:1988)
	at com.microsoft.sqlserver.jdbc.TDSWriter.flush(IOBuffer.java:4096)
	at com.microsoft.sqlserver.jdbc.TDSWriter.writePacket(IOBuffer.java:3998)
	at com.microsoft.sqlserver.jdbc.TDSWriter.endMessage(IOBuffer.java:3128)
	at com.microsoft.sqlserver.jdbc.TDSCommand.startResponse(IOBuffer.java:7602)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:576)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:508)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:493)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:172)
	at org.mule.extension.db.internal.domain.autogeneratedkey.NoAutoGenerateKeysStrategy.execute(NoAutoGenerateKeysStrategy.java:36)

 
After this your mule flow stops processing, and probably your business processes get impacted. This issue happens likely when there is little or no-load after some burst processing has been in place.

CAUSE

Such closed connections (commonly known as "infected" connections) may be caused by the database, which closes a connection due to a "idle connection time" in place. When a connection is detected as not being used for such time, the connection is automatically closed at the server side, causing mule to see a "stale" client connection. 

The connection can be closed by some firewall that sees no traffic and drops the channel due to some rule in place. 

The keep-alive may not be configured at the client side.

The connection may be dropped by the OS on which the Mule Runtime is installed. In Linux the settings that would impact this would be tcp_keepalive_time, tcp_keepalive_intvl and tcp_keepalive_probes settings. Based on the default values documented here if the TCP connection is idle for more than 2 hours, the OS will send 9 probes every 75 seconds. If the connection is idle, the TCP connection will be dropped. This will drop the database connection.

SOLUTION

To resolve this issue, it is necessary to configure a datasource for JDBC that accepts a connection checking mechanism. Before being used, a connection is tested for its validity and not used if it has been closed already.

An implementation of either c3p0 or Apache Commons DBCP is recommended. 

  • USING c3p0

c3p0 implemented JDBC connection pools, are configurable. c3p0 allows you to set those configurable parameters by declaring a bean.

By default c3p0, comes with some functionality disabled to avoid impacting target databases. One of such functionalities is related to the capability of what is called the connection-health-checking. 

Connection health checking checks the DB physical connection before a logical connections is given to the application. This connection probe, basically checks the following:

- The TCP/IP connection with database is still active.
- Remote DB is reachable through this connection.
- DB connection is responding in time ( this is usually done via a SELECT 1 FROM DUAL sentence if Oracle, for example).

If some of the checks gives a wrong result, the connection itself is marked as "unusable", a brand new physical connection is created into the pool, and the connection request operation is repeated once again. All this process is transparent for the application, which is still waiting for a connection.

c3p0 allows two type of JDBC connection probes:

- Test connections on check-out: checks that the physical connection associated with a logical one is healthy before on connection request (the described case and most common). This applies to connections that are requested from the pool.

- Test connections on check-in: checks that a returned logical connection has its related physical connection in healthy state. This applies to connections that are being returned to the pool.

Both of these options are disabled by default on c3p0, since connection check operation can turn out to be expensive on some databases, depending on the DB response time and on the current load being experienced.

To solve your issue, add the following options to your c3p0 configuration, as shown below in the example XML:
<spring:bean id="employeeDataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close">
    <spring:property name="driverClass" value="${jdbc.driverClassName}" />
    <spring:property name="jdbcUrl" value="${jdbc.employee_db_url}" />
    <spring:property name="user" value="${jdbc.username}" />
    <spring:property name="password" value="${jdbc.password}" />
    <spring:property name="maxPoolSize" value="${jdbc.maxPoolSize}" />
    <spring:property name="minPoolSize" value="${jdbc.minPoolSize}" />
    <spring:property name="maxStatements" value="${jdbc.maxStatements}" />
    <spring:property name="testConnectionOnCheckout" value="true" />
    <spring:property name="testConnectionOnCheckin" value="true" />
</spring:bean>
 

  • USING APACHE COMMONS DBCP

The principles are the same as for C3p0 above, but In this case the configuration attribute to be used is "testOnBorrow", which needs to be set to true. Here is an example configuration:
<spring:bean id="mssql" class="org.apache.commons.dbcp.BasicDataSource">
    <spring:property name="driverClassName" value="${jdbc.driverClassName}" />
    <spring:property name="url" value="${jdbc.url}" />
    <spring:property name="username" value="${jdbc.username}" />
    <spring:property name="password" value="${jdbc.password}" />
    <spring:property name="testOnBorrow" value="true" />
</spring:bean>
Numéro d’article de la base de connaissances

001123735

 
Chargement
Salesforce Help | Article