Re: SocketTimeout and ConnectionTimeout

In the log, there are only one [EL Warning], all other retries logging are [EL Info].
So it is EL controls the retries behavior(just as connection pool), not JDBC.

> 在 2016年8月25日，19:22，Alexandre Brito <alexandre(dot)brito(at)wit-software(dot)com <mailto:alexandre(dot)brito(at)wit-software(dot)com>> 写道：
>
> 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 <http://www.w3.org/2001/XMLSchema-instance>"
> xsi:schemaLocation="http://java.sun.com/xml/ns/persistence <http://java.sun.com/xml/ns/persistence>http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd <http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd>"
> version="2.0" xmlns="http://java.sun.com/xml/ns/persistence <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
>
> <connectioTimeout_takes_too_long.txt>
> --
> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org <mailto:pgsql-jdbc(at)postgresql(dot)org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc <http://www.postgresql.org/mailpref/pgsql-jdbc>