JDBI Connection Closed - how to handle

[Mike Huniewicz]

I have a business class calling a DAO method multiple times. After around 15 minutes, that dies with a Connection closed error from Postgres.

! Caused by: org.postgresql.util.PSQLException: This connection has been closed.
! at org.postgresql.jdbc2.AbstractJdbc2Connection.checkClosed(AbstractJdbc2Connection.java:853) ~[hui-populator.jar:0.0.180]
! at org.postgresql.jdbc2.AbstractJdbc2Connection.rollback(AbstractJdbc2Connection.java:870) ~[hui-populator.jar:0.0.180]
! at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_51]
! at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_51]
! at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_51]
! at java.lang.reflect.Method.invoke(Method.java:497) ~[na:1.8.0_51]
! at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[hui-populator.jar:0.0.180]
! at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109) ~[hui-populator.jar:0.0.180]
! at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80) ~[hui-populator.jar:0.0.180]
! at com.sun.proxy.$Proxy39.rollback(Unknown Source) ~[na:na]
! at org.skife.jdbi.v2.tweak.transactions.LocalTransactionHandler.rollback(LocalTransactionHandler.java:90) ~[hui-populator.jar:0.0.180]
! ... 62 common frames omitted

This is the way the Dao class is instantiated:

final DBI jdbi = factory.build(environment, configuration.getHuiDbDatabase(), "postgres");

    final MyDao myDao = jdbi.onDemand(MyDao.class);

A and then this dao has a method annotated with @Transactional, which is called multiple times from the business class.

At some point the connection is closed. What should I do to handle that, so that I can keep calling the @Transactional method?

[Matthew Hall]

What JDBI version are you using?

---

This email was sent by a company owned by Financial Times Group Limited ("FT Group"), registered office at Number One Southwark Bridge, London SE1 9HL.  Registered in England and Wales with company number 879531. This e-mail may contain confidential information. If you are not the intended recipient, please notify the sender immediately, delete all copies and do not distribute it further.  It could also contain personal views which are not necessarily those of the FT Group.  We may monitor outgoing or incoming emails as permitted by law.

--
You received this message because you are subscribed to the Google Groups "jDBI" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Mike Huniewicz]

Hi, thanks for your response.

I'm using

<dependency>
    <groupId>io.dropwizard</groupId>
    <artifactId>dropwizard-jdbi</artifactId>
    <version>0.7.1</version>
</dependency>

[Matthew Hall]

Hi Mike,

dropwizard-jdbi uses org.jdbi:jdbi version 2.55 which is rather old. Try managing your transitive JDBI dependency to use the latest version and see if that helps. There've been a few fixes on resource leaks very recently which might make a difference.

<dependencyManagement>

  <dependencies>

    <dependency>

      <groupId>org.jdbi</groupId>

      <artifactId>jdbi</artifactId>

      <version>2.70</version>

    </dependency>

  </dependencies>

</dependencyManagement>

Hope this helps.

-Matthew

[Mike Huniewicz]

Hi Matt, just tried it now, did absolutely nothing, unfortunately.

[Matthew Hall]

Hi Mike,

At this point I'm not sure how to help you. If you can produce a unit test which reliably produces the problem, that would allow us to track down the problem. Maybe a stress test that uses onDemand SQL objects on a thread pool executor for some high number of iterations (e.g. thousands)?

In the meanwhile, I recommend using try-with-resources on DBI.open(MyDao.class), and steering away from onDemand() for now.

One idea just came to me: check your config.yml database config. In particular, look at initialSize, minSize, and maxSize settings. See if maxSize times the number of appserver instances running in production exceeds the max connection count of your database.

Cheers,

Matt

[Mike Huniewicz]

Seems like the problem is that the connection is closed (by Postgres or a firewall), but JDBI doesn't realise.

The other problem we're facing is that when we kill the Dropwizard app, JDBI doesn't close the connection either.

We've now set these parameters, forcing JDBI to close the connection before Postgres/Firewall closes it.

minIdleTime: 1 minute
maxConnectionAge: 1 minute
checkConnectionOnConnect: true
checkConnectionOnReturn: true
checkConnectionOnBorrow: true
logAbandonedConnections: true
logValidationErrors: true

I can't create a unit test at this moment, so I'm not sure if this is helpful in terms of fixing this issue.

--

Mike Huniewicz

BI Integration & API Developer

Technology BI & Data

Number One Southwark Bridge, London, SE1 9HL

[Steven Schlansker]

This is a long shot -- I notice that your connection closed happens in
a rollback() call. Is that always the case?

Maybe you've got something accidentally doing double-cleanup. For example
I can imagine code structured like this causing a problem:

Handle h = ...;

try {
try {
doSomeBusinessLogic();
} catch (BusinessLogicFailure) { h.close(); }
} finally {
h.rollback();
}

This could be further confused by the handling of @Transactional
annotations. Unfortunately, it's hard for us to pinpoint the problem
without some more clues -- a test case would be ideal, or even the
relevant DAO code would help, but right now we're flying blind.

One other thought, you could enable your connection pool's "test connection
on renew" feature or other assorted safety belts, in the hopes that it might
catch invalid Connection usage.

> This email was sent by a company owned by Financial Times Group Limited ("FT Group"), registered office at Number One Southwark Bridge, London SE1 9HL. Registered in England and Wales with company number 879531. This e-mail may contain confidential information. If you are not the intended recipient, please notify the sender immediately, delete all copies and do not distribute it further. It could also contain personal views which are not necessarily those of the FT Group. We may monitor outgoing or incoming emails as permitted by law.
>
>
> --
> You received this message because you are subscribed to the Google Groups "jDBI" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
> This email was sent by a company owned by Financial Times Group Limited ("FT Group"), registered office at Number One Southwark Bridge, London SE1 9HL. Registered in England and Wales with company number 879531. This e-mail may contain confidential information. If you are not the intended recipient, please notify the sender immediately, delete all copies and do not distribute it further. It could also contain personal views which are not necessarily those of the FT Group. We may monitor outgoing or incoming emails as permitted by law.
>
>
> --
> You received this message because you are subscribed to the Google Groups "jDBI" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
>

> This email was sent by a company owned by Financial Times Group Limited ("FT Group"), registered office at Number One Southwark Bridge, London SE1 9HL. Registered in England and Wales with company number 879531. This e-mail may contain confidential information. If you are not the intended recipient, please notify the sender immediately, delete all copies and do not distribute it further. It could also contain personal views which are not necessarily those of the FT Group. We may monitor outgoing or incoming emails as permitted by law.
>
>
> --
> You received this message because you are subscribed to the Google Groups "jDBI" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
>
> --
> You received this message because you are subscribed to the Google Groups "jDBI" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to jdbi+uns...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>
>
>
> --
> Mike Huniewicz
> BI Integration & API Developer
> Technology BI & Data
>
> Number One Southwark Bridge, London, SE1 9HL
>
>
>
>
>
>

[Mike Huniewicz]

For now I have this set:

checkConnectionOnConnect: true
checkConnectionOnReturn: true
checkConnectionOnBorrow: true

The code to read isn't happening in a try catch, but the code to write to another database indeed might.

Someone on my team suggested that in our PROD environments the query may indeed be killed remotely sooner that in INT, which explains the behaviour.

Is there a way to handle it gracefully? That's essentially the question. [FYI: It's copying over a rather large data set (33m rows).]

---

This email was sent by a company owned by Financial Times Group Limited ("FT Group"), registered office at Number One Southwark Bridge, London SE1 9HL.  Registered in England and Wales with company number 879531. This e-mail may contain confidential information. If you are not the intended recipient, please notify the sender immediately, delete all copies and do not distribute it further.  It could also contain personal views which are not necessarily those of the FT Group.  We may monitor outgoing or incoming emails as permitted by law.

[Steven Schlansker]

The thrown exception is indeed the "graceful" way.

Unfortunately in this case jdbi's exception wrapping
hides the cause a bit, but what you will probably have
to do at the catch site is unwrap first the SQLException
and then examine the SQLState for the "connection reset" state.

I'm not aware of any less obnoxious way to do this, unfortunately,
short of rewriting your SQL to use a stored procedure which tests
some sort of exit flag in the database or other such nonsense...

[Mike Huniewicz]

I mean we can more or less safely say it's something on our side closing the connection by design, as the connection (query) is indeed long-running. But can I still reuse the iterator somehow ("reconnect it"?).

---

This email was sent by a company owned by Financial Times Group Limited ("FT Group"), registered office at Number One Southwark Bridge, London SE1 9HL.  Registered in England and Wales with company number 879531. This e-mail may contain confidential information. If you are not the intended recipient, please notify the sender immediately, delete all copies and do not distribute it further.  It could also contain personal views which are not necessarily those of the FT Group.  We may monitor outgoing or incoming emails as permitted by law.

[Steven Schlansker]

Not a chance. A Connection is stateful, and once it is closed
you have released numerous resources both on the server and client side.
Any transactions in flight have been rolled back and discarded.

Your only option once it is closed is to open a new one and start over.

[Mike Huniewicz]

Thanks, that's useful to know.
Mike

[Brian McCallister]

I realize you are in Dropwizard, so don't have a lot of flexibility, but if you *can* I'd recommend using a connection pool which can make use of the driver's liveness check (which is *much* more efficient than a check query) and have the DataSource check connection health on borrow (if you get spurious closes periodically with a low utilization rate, or on return if you cycle through all your connections rapidly. HikariCP does this really well.

-Brian

[Mário Costa]

I've actually came across the same issue, in Dropwizard, hoped to find a solution here. But had already accounted for one, the on Brian, stated.
Consider using https://commons.apache.org/proper/commons-dbcp/, Apache Commons DBCP, which is caompatible and you can load-it as a bean from the yaml config, via Dropwizard.
Is is more grateful dealing with database connection issues ...
Cheers,
mc

[Mike Huniewicz]

Hi Mario, have you seen this though? http://people.apache.org/~fhanik/jdbc-pool/jdbc-pool.html I might experiment with it, still.