SocketTimeout and ConnectionTimeout - Mailing list pgsql-jdbc

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-source>

        <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