Re: SocketTimeout and ConnectionTimeout

We don't see the connection timeout until the O/S times out which can be a
while.

Dave Cramer

davec(at)postgresintl(dot)com
www.postgresintl.com

On 25 August 2016 at 07:22, Alexandre Brito <
alexandre(dot)brito(at)wit-software(dot)com> wrote:

> 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
>
>
>
>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>