SocketTimeout and ConnectionTimeout

Hi,

We are facing a problem with the JBDC connection being drop silently and the
driver is not throwing a SQL Exception in a timely manner.

Going through the mailing-list and the internet, it seems the problem is not
so rare however the solution is still out of my reach.

What is happening is simple:

1. JDBC connection is open and then stays idle

2. A network component (Fortinet) beyond our control seems to be
dropping idle TCP connections.

3. Our webapp places a query and it takes 1min and 12 seconds (more or
less) for the SQL Exception to be caught.

We've reproduced the issue behaviour (on our environment ) by setting an
iptables rule blocking port 5432 after the DB connection was successfully
established. On the logs I can see the socket read (I think) timeout is
around 12 seconds and then there are 6 retries 10 secs apart. (see the
extracted log file from tomcat attached with EL log to finest)

I would like to have control over the number of retries and timeout.
Basically, I would like the receive an SQL Connection Timeout Exception
within 5-10 seconds.

We are using tomcat 8 and latest JDCB driver (9.4.1209 JDBC42) and JPA.

Our schema is tiny and the data in it very very small.

This is my tomcat's context.xml (for troubleshooting proposes) .

<Resource auth="Container"

driverClassName="org.postgresql.Driver"

maxTotal="1"

maxIdle="1"

maxWaitMillis="3000"

name="jdbc/postgresqlconnection"

type="javax.sql.DataSource"

url="jdbc:postgresql://127.0.0.1:5432/onbbp?
connectionTimeout=2&amp;socketTimeout=2"

factory="org.apache.tomcat.dbcp.dbcp2.BasicDataSourceFactory"

username="user"

password="pass"

/>

This is my apps's persistence.xml

<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_2_0.xsd"

version="2.0" xmlns="http://java.sun.com/xml/ns/persistence">

<persistence-unit name="bp-persistence"
transaction-type="RESOURCE_LOCAL">

<provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>

<non-jta-data-source>java:comp/env/jdbc/postgresqlconnection</non-jta-data-s
ource>

<class>com.all.onbbp.database.entities.BpUser</class>

<class>com. all.onbbp.database.entities.BusinessPartner</class>

<class>com. all.onbbp.database.entities.Enterprise</class>

<class>com. all.onbbp.database.entities.Property</class>

<class>com. all.onbbp.database.entities.LokiCookie</class>

<class>com. all.onbbp.database.entities.Report</class>

<class>com. all.onbbp.database.entities.Languages</class>

<class>com. all.onbbp.database.entities.EmailTemplates</class>

<class>com. all.onbbp.database.entities.BpMutex</class>

<class>com. all.onbbp.database.entities.UserSession</class>

<shared-cache-mode>NONE</shared-cache-mode>

<properties>

<property name="javax.persistence.validation.mode"
value="AUTO" />

<property name="eclipselink.logging.level" value="FINEST"/>

</properties>

</persistence-unit>

</persistence>

Btw the, this is how the EntityManager is initialized

public class StartupListener implements ServletContextListener {

.

private static EntityManagerFactory entityManagerFactory;

@Override

public void contextInitialized(ServletContextEvent sce) {

try {

try {

entityManagerFactory =
Persistence.createEntityManagerFactory("bp-persistence");

} catch (Throwable ex) {

throw new
BPException(BPErrorCode.INTERNAL_SERVER_ERROR_DB_ERROR);

}

Does anyone has an idea? Why does the connectionTimeout to the database take
so long to return (more than 12 seconds)?

Many thanks in advance for any reply

Alexandre