Re: SocketTimeout and ConnectionTimeout
От | Dave Cramer |
---|---|
Тема | Re: SocketTimeout and ConnectionTimeout |
Дата | |
Msg-id | CADK3HHKuV5u78FqWAr86Qeq_k-+5O-orzNo80yL-vGx1HS=wsw@mail.gmail.com обсуждение исходный текст |
Ответ на | SocketTimeout and ConnectionTimeout ("Alexandre Brito" <alexandre.brito@wit-software.com>) |
Список | 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
В списке pgsql-jdbc по дате отправления:
Следующее
От: Adam RauchДата:
Сообщение: Simple queries with JDBC escaped scalar functions result in exceptions