Обсуждение: 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&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
Вложения
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" 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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& 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
在 2016年8月25日,19:22,Alexandre Brito <alexandre.brito@wit-software.com> 写道:<connectioTimeout_takes_too_long.txt>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 idle2. 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"xsi:schemaLocation="http://java.sun.com/xml/ns/persistencehttp://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 initializedpublic class StartupListener implements ServletContextListener {…private static EntityManagerFactory entityManagerFactory;@Overridepublic 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 replyAlexandre
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.zhang@outlook.com]
Sent: 30 August 2016 16:23
To: Alexandre Brito <alexandre.brito@wit-software.com>
Cc: pgsql-jdbc@postgresql.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.brito@wit-software.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"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistencehttp://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
<connectioTimeout_takes_too_long.txt>
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
在 2016年8月30日,23:31,Alexandre Brito <alexandre.brito@wit-software.com> 写道: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).
EL is a ORM framework, not part of JDBC driver, right?http://stackoverflow.com/questions/5289075/how-to- modify-eclipselink-jpa-2-0- connection-retry-behavior 在 2016年8月30日,23:31,Alexandre Brito <alexandre.brito@wit-software.com> 写道: 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).
Hello,
In that case the EL is shipped with tomcat 8 and apologies for assuming it was within the driver.
I’ll look at this from the EL implementation and try to figure it out.
Thank you for your support
Regards
Alex
From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer
Sent: 30 August 2016 17:02
To: Jingzhi Zhang <jingzhi.zhang@outlook.com>
Cc: Alexandre Brito <alexandre.brito@wit-software.com>; List <pgsql-jdbc@postgresql.org>
Subject: Re: [JDBC] SocketTimeout and ConnectionTimeout
It is not part of the driver...
Dave Cramer
davec@postgresintl.com
On 30 August 2016 at 11:54, Jingzhi Zhang <jingzhi.zhang@outlook.com> wrote:
EL is a ORM framework, not part of JDBC driver, right?
在 2016年8月30日,23:31,Alexandre Brito <alexandre.brito@wit-software.com> 写道:
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).
在 2016年8月25日,19:22,Alexandre Brito <alexandre.brito@wit-software.com> 写道:<connectioTimeout_takes_too_long.txt>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 idle2. 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"xsi:schemaLocation="http://java.sun.com/xml/ns/persistencehttp://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 initializedpublic class StartupListener implements ServletContextListener {…private static EntityManagerFactory entityManagerFactory;@Overridepublic 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 replyAlexandre
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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.zhang@outlook.com]
Sent: 30 August 2016 16:23
To: Alexandre Brito <alexandre.brito@wit-software.com>
Cc: pgsql-jdbc@postgresql.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.brito@wit-software.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"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistencehttp://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
<connectioTimeout_takes_too_long.txt>
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
在 2016年8月30日,23:31,Alexandre Brito <alexandre.brito@wit-software.com> 写道: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).
EL is a ORM framework, not part of JDBC driver, right?http://stackoverflow.com/questions/5289075/how-to- modify-eclipselink-jpa-2-0- connection-retry-behavior 在 2016年8月30日,23:31,Alexandre Brito <alexandre.brito@wit-software.com> 写道: 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).
Hello,
In that case the EL is shipped with tomcat 8 and apologies for assuming it was within the driver.
I’ll look at this from the EL implementation and try to figure it out.
Thank you for your support
Regards
Alex
From: davecramer@gmail.com [mailto:davecramer@gmail.com] On Behalf Of Dave Cramer
Sent: 30 August 2016 17:02
To: Jingzhi Zhang <jingzhi.zhang@outlook.com>
Cc: Alexandre Brito <alexandre.brito@wit-software.com>; List <pgsql-jdbc@postgresql.org>
Subject: Re: [JDBC] SocketTimeout and ConnectionTimeout
It is not part of the driver...
Dave Cramer
davec@postgresintl.com
On 30 August 2016 at 11:54, Jingzhi Zhang <jingzhi.zhang@outlook.com> wrote:
EL is a ORM framework, not part of JDBC driver, right?
在 2016年8月30日,23:31,Alexandre Brito <alexandre.brito@wit-software.com> 写道:
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).