From: | "Alexandre Brito" <alexandre(dot)brito(at)wit-software(dot)com> |
---|---|
To: | "'Jingzhi Zhang'" <jingzhi(dot)zhang(at)outlook(dot)com> |
Cc: | <pgsql-jdbc(at)postgresql(dot)org> |
Subject: | Re: SocketTimeout and ConnectionTimeout |
Date: | 2016-08-30 15:31:28 |
Message-ID: | 027f01d202d3$93dfabf0$bb9f03d0$@wit-software.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-jdbc |
Hello,
Hm, ok. Do you know how to control the retries behaviour from the EL?
Please bear in mind that we are using the latest JDBC driver available at
postgresql.org (which includes EL).
Regards,
Alex
From: Jingzhi Zhang [mailto:jingzhi(dot)zhang(at)outlook(dot)com]
Sent: 30 August 2016 16:23
To: Alexandre Brito <alexandre(dot)brito(at)wit-software(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] 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&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-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
<connectioTimeout_takes_too_long.txt>
--
Sent via pgsql-jdbc mailing list ( <mailto:pgsql-jdbc(at)postgresql(dot)org>
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
From | Date | Subject | |
---|---|---|---|
Next Message | Vladimir Sitnikov | 2016-08-30 15:47:57 | 9.4.1210 release |
Previous Message | Jingzhi Zhang | 2016-08-30 15:23:03 | Re: SocketTimeout and ConnectionTimeout |