dropwizard-hibernate and mysql: connection timeouts

[Aaron Kimball]

Hi folks,

Dropwizard newbie here; hopefully someone else has encountered this error before.

I have a dropwizard REST server (dropwizard 0.7.0) using a MySQL database as the backend via dropwizard-hibernate.

If I leave the server running idle for several hours, when we come back to make a new request, it throws a com.mysql.jdbc.exceptions.jdbc4.CommunicationsException; "The last packet successfully received from the server was (many) milliseconds ago"

It seems as though leaving a hibernate Session object open for too long without any traffic will cause it to time out on the server side. Hibernate does not seem to release the current Session though.

Looking at http://docs.jboss.org/hibernate/stable/core.old/reference/en/html/transactions-connection-release.html, it suggests setting the connection release mode to after-transaction rather than on-close; I put "hibernate.transaction.auto_close_session: true" in the properties section of the dbConfiguration, but now it throws the following:

org.hibernate.SessionException: Session was already closed

Call stack: 

  org.hibernate.internal.SessionImpl.close(SessionImpl.java:355)

  io.dropwizard.hibernate.UnitOfWorkRequestDispatcher.dispatch(UnitOfWorkRequestDispatcher.java:50)

  io.dropwizard.jersey.guava.OptionalResourceMethodDispatchAdapter$OptionalRequestDispatcher.dispatch(OptionalResourceMethodDispatchAdapter.java:37)

  com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)

  com.sun.jersey.server.impl.uri.rules.ResourceObjectRule.accept(ResourceObjectRule.java:100)

  com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)

  com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)

  com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)

  com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)

  com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)

  com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)

  com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)

  com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:540)

  com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:715)

  javax.servlet.http.HttpServlet.service(HttpServlet.java:848)

...

According to this blog post: http://stackoverflow.com/questions/2378572/hibernate-session-is-closed it might be a problem with using sessionFactory.getCurrentSession() rather than openSession() in this case.

Has anyone using MySQL encountered this before? Is there a mysql config that helps with this.. or should I be doing something different with my session management in my resources or DAO classes? 

Is this a bug in the dropwizard-hibernate AbstractDAO class? Should I add some more logic to my DAO's currentSession() function? (and/or add a patch? ;)

Thanks,

- Aaron

[Aaron Kimball]

Also, I have the following in my production.yaml file:

database:

  driverClass: com.mysql.jdbc.Driver

  user: restuser

  password: xxxxxxx

  url: jdbc:mysql://dbservername.foo.com/restapp

  maxWaitForConnection: 5s

  minSize: 8

  maxSize: 32

  checkConnectionWhileIdle: false

  validationQuery: "SELECT 1"

I'm going to see if setting "checkConnectionWhileIdle: true" fixes it.

To reproduce the "Session was already closed" error above, it was because I tried adding the following:

database:

  ...

  properties:

    hibernate.transaction.auto_close_session: true

- Aaron

[Graham O'Regan]

Hi Aaron,

The problem seems to be with the connection pool rather than hibernate’s session management. Can you try the following config?

database:

  driverClass: ${database.driverClass}

  user: ${database.user}

  password: ${database.password}

  url: ${database.url}

  

  properties:

    charSet: UTF-8

    hibernate.dialect: org.hibernate.dialect.MySQL5InnoDBDialect

    # validate | update | create | create-drop

    hibernate.hbm2ddl.auto: validate

  maxWaitForConnection: 1s

  validationQuery: "/* Health Check */ SELECT 1"

  minSize: ${database.connections.min}

  maxSize: ${database.connections.max}

  checkConnectionWhileIdle: false

  checkConnectionOnReturn: true

  initialSize: ${database.connections.min}

Just to check, your application works correctly unless it has been left idle and you are relying on @UnitOfWork (using super.currentSession() in your DAOs) to manage the session instead of doing it yourself?

Graham

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

[Aaron Kimball]

Hi Graham,

Thanks for writing back. My DAO classes do not do anything particularly clever. My UserDao class is included for reference at the end. The application works correctly unless it has been left idle (e.g., overnight)

There is one place where I want to read a row and then update it (I'm tracking multiple sequences in one table). The DAO for that uses locking like so::

  private ZIdSeq findByIdForUpdate(Long id) {

    return (ZIdSeq) currentSession().load(getEntityClass(), id,

        new LockOptions(LockMode.PESSIMISTIC_WRITE));

  }

I use @UnitOfWork on all my resources. One resource that does a read-modify-write explicitly states that it should ignore the cache:  @UnitOfWork(flushMode=FlushMode.COMMIT, cacheMode=CacheMode.IGNORE)

I tried the configuration you specified and my application failed to boot with this error:

INFO  [2014-04-29 02:23:01,266] org.hibernate.annotations.common.Version: HCANN000001: Hibernate Commons Annotations {4.0.4.Final}

INFO  [2014-04-29 02:23:01,282] org.hibernate.Version: HHH000412: Hibernate Core {4.3.1.Final}

INFO  [2014-04-29 02:23:01,284] org.hibernate.cfg.Environment: HHH000206: hibernate.properties not found

INFO  [2014-04-29 02:23:01,286] org.hibernate.cfg.Environment: HHH000021: Bytecode provider name : javassist

INFO  [2014-04-29 02:23:01,344] io.dropwizard.hibernate.SessionFactoryFactory: Entity classes: [com.zymergen.zrest.models.User, com.zymergen.zrest.models.ZId, com.zymergen.zrest.models.ZIdSeq]

INFO  [2014-04-29 02:23:02,153] org.hibernate.dialect.Dialect: HHH000400: Using dialect: org.hibernate.dialect.MySQL5InnoDBDialect

INFO  [2014-04-29 02:23:02,508] org.hibernate.engine.transaction.internal.TransactionFactoryInitiator: HHH000399: Using default transaction strategy (direct JDBC transactions)

INFO  [2014-04-29 02:23:02,516] org.hibernate.hql.internal.ast.ASTQueryTranslatorFactory: HHH000397: Using ASTQueryTranslatorFactory

INFO  [2014-04-29 02:23:03,311] org.hibernate.tool.hbm2ddl.SchemaValidator: HHH000229: Running schema validator

INFO  [2014-04-29 02:23:03,316] org.hibernate.tool.hbm2ddl.SchemaValidator: HHH000102: Fetching database metadata

INFO  [2014-04-29 02:23:03,386] org.hibernate.tool.hbm2ddl.TableMetadata: HHH000261: Table found: zrest.users

INFO  [2014-04-29 02:23:03,388] org.hibernate.tool.hbm2ddl.TableMetadata: HHH000037: Columns: [id, enabled, first_name, username, is_admin, email, password_hash, create_time, last_name, password_salt]

Exception in thread "main" org.hibernate.HibernateException: Wrong column type in zrest.users for column password_salt. Found: char, expected: varchar(4)

        at org.hibernate.mapping.Table.validateColumns(Table.java:372)

        at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1336)

        at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:155)

        at org.hibernate.internal.SessionFactoryImpl.<init>(SessionFactoryImpl.java:524)

        at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1857)

        at io.dropwizard.hibernate.SessionFactoryFactory.buildSessionFactory(SessionFactoryFactory.java:85)

        at io.dropwizard.hibernate.SessionFactoryFactory.build(SessionFactoryFactory.java:40)

        at io.dropwizard.hibernate.SessionFactoryFactory.build(SessionFactoryFactory.java:30)

        at io.dropwizard.hibernate.HibernateBundle.run(HibernateBundle.java:38)

        at io.dropwizard.hibernate.HibernateBundle.run(HibernateBundle.java:13)

        at io.dropwizard.setup.Bootstrap.run(Bootstrap.java:166)

        at io.dropwizard.cli.EnvironmentCommand.run(EnvironmentCommand.java:41)

        at io.dropwizard.cli.ConfiguredCommand.run(ConfiguredCommand.java:76)

        at io.dropwizard.cli.Cli.run(Cli.java:70)

        at io.dropwizard.Application.run(Application.java:72)

        at com.zymergen.zrest.ZRestApplication.main(ZRestApplication.java:92)

I commented out this line:

    hibernate.hbm2ddl.auto: validate

... and it booted. I'll check back tomorrow to see if it fails with another timeout or if the checkConnectionOnReturn and etc. did the trick.

Thanks

- Aaron

* Appendix *

.. This is my UserDao. Pretty unoriginal:

public class UserDao extends AbstractDAO<User> {

  public UserDao(SessionFactory sessionFactory) {

    super(sessionFactory);

  }

  public User findById(Long id) {

    return get(id);

  }

  /** Find by the lowercase form of the username. */

  public User findByUsername(String username) {

    return uniqueResult(criteria().add(Restrictions.eq("username", username.toLowerCase())));

  }

  /** Find by the lowercase form of the email address. */

  public User findByEmail(String email) {

    return uniqueResult(criteria().add(Restrictions.eq("email", email.toLowerCase())));

  }

  /**

   * Create a User object in the database. If the user did not have an

   * id number, issue one here.

   *

   * @return the id number associated with this user account.

   */

  public long create(User user) {

    return persist(user).getId();

  }

  /**

   * Update an existing user account.

   */

  public void update(User user) {

    persist(user);

  }

  public List<User> all() {

    return list(criteria());

  }

}

[Aaron Kimball]

Hi Graham,

This failed again today with a similar error/exception, but MySQL's exception message suggested setting "autoReconnect=true" in the Connector/J settings. I've added "?autoReconnect=true" to the end of my mysql jdbc URL; we'll see if this does the trick.

- Aaron

[Graham O'Regan]

Just be aware that autoReconnect=true works at the driver level and not at the connection pool level and generally isn’t recommended [1]. However, I have seen problems like this in the past and if your application isn’t taking consistent load then it would probably be a valid workaround.

Graham

1. http://dev.mysql.com/doc/connector-j/en/connector-j-reference-configuration-properties.html

[Aaron Kimball]

Hi Graham,

It looks like that didn't fix the issue (despite being in the stack trace as a suggestion ;)

Do you have any other suggestions?

Here's my config:

database:
  driverClass: com.mysql.jdbc.Driver

  user: zrest
  password: xxxxxxx


  url: jdbc:mysql://dev1.foobar.net/zrest?autoReconnect=true

  properties:


    charSet: UTF-8
    hibernate.dialect: org.hibernate.dialect.MySQL5InnoDBDialect

    #hibernate.hbm2ddl.auto: validate



  maxWaitForConnection: 3s


  minSize: 8
  maxSize: 32

  initialSize: 8



  checkConnectionWhileIdle: false


  checkConnectionOnReturn: true



  validationQuery: "/* Health check */ SELECT 1"

... and here's a complete exception trace when a request goes bad after the server's been inactive. This happens from a simple GET request to something that reads some entities from the database:

500 Internal Server Error
Unhandled exception:

org.hibernate.TransactionException: JDBC begin transaction failed: 
Call stack: 
  org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:76)
  org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:162)
  org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1431)
  io.dropwizard.hibernate.UnitOfWorkRequestDispatcher.beginTransaction(UnitOfWorkRequestDispatcher.java:57)
  io.dropwizard.hibernate.UnitOfWorkRequestDispatcher.dispatch(UnitOfWorkRequestDispatcher.java:41)
  io.dropwizard.jersey.guava.OptionalResourceMethodDispatchAdapter$OptionalRequestDispatcher.dispatch(OptionalResourceMethodDispatchAdapter.java:37)
  com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
  com.sun.jersey.server.impl.uri.rules.ResourceObjectRule.accept(ResourceObjectRule.java:100)
  com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
  com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
  com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)
  com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)
  com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
  com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
  com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
  com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:540)
  com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:715)
  javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
  io.dropwizard.jetty.NonblockingServletHolder.handle(NonblockingServletHolder.java:49)
  org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1515)
  org.eclipse.jetty.servlets.UserAgentFilter.doFilter(UserAgentFilter.java:83)
  org.eclipse.jetty.servlets.GzipFilter.doFilter(GzipFilter.java:348)
  io.dropwizard.jetty.BiDiGzipFilter.doFilter(BiDiGzipFilter.java:127)
  org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1486)
  io.dropwizard.servlets.ThreadNameFilter.doFilter(ThreadNameFilter.java:29)
  org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1486)
  org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:519)
  org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1097)
  org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:448)
  org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1031)
  org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:136)
  org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
  com.codahale.metrics.jetty9.InstrumentedHandler.handle(InstrumentedHandler.java:173)
  io.dropwizard.jetty.RoutingHandler.handle(RoutingHandler.java:51)
  org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
  org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:92)
  org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
  org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:162)
  org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
  org.eclipse.jetty.server.Server.handle(Server.java:446)
  org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:271)
  org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:246)
  org.eclipse.jetty.io.AbstractConnection$ReadCallback.run(AbstractConnection.java:358)
  org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:601)
  org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:532)
  java.lang.Thread.run(Thread.java:744)

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 184,333,668 milliseconds ago.  The last packet sent successfully to the server was 184,333,668 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.
Call stack: 
  sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
  sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
  sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
  java.lang.reflect.Constructor.newInstance(Constructor.java:526)
  com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
  com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1127)
  com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3983)
  com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2596)
  com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
  com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)
  com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5357)
  sun.reflect.GeneratedMethodAccessor20.invoke(Unknown Source)
  sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  java.lang.reflect.Method.invoke(Method.java:606)
  org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
  org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
  org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
  com.sun.proxy.$Proxy28.setAutoCommit(Unknown Source)
  org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:72)
  org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:162)
  org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1431)
  io.dropwizard.hibernate.UnitOfWorkRequestDispatcher.beginTransaction(UnitOfWorkRequestDispatcher.java:57)
  io.dropwizard.hibernate.UnitOfWorkRequestDispatcher.dispatch(UnitOfWorkRequestDispatcher.java:41)
  io.dropwizard.jersey.guava.OptionalResourceMethodDispatchAdapter$OptionalRequestDispatcher.dispatch(OptionalResourceMethodDispatchAdapter.java:37)
  com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
  com.sun.jersey.server.impl.uri.rules.ResourceObjectRule.accept(ResourceObjectRule.java:100)
  com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
  com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
  com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)
  com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)
  com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
  com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
  com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
  com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:540)
  com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:715)
  javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
  io.dropwizard.jetty.NonblockingServletHolder.handle(NonblockingServletHolder.java:49)
  org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1515)
  org.eclipse.jetty.servlets.UserAgentFilter.doFilter(UserAgentFilter.java:83)
  org.eclipse.jetty.servlets.GzipFilter.doFilter(GzipFilter.java:348)
  io.dropwizard.jetty.BiDiGzipFilter.doFilter(BiDiGzipFilter.java:127)
  org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1486)
  io.dropwizard.servlets.ThreadNameFilter.doFilter(ThreadNameFilter.java:29)
  org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1486)
  org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:519)
  org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1097)
  org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:448)
  org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1031)
  org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:136)
  org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
  com.codahale.metrics.jetty9.InstrumentedHandler.handle(InstrumentedHandler.java:173)
  io.dropwizard.jetty.RoutingHandler.handle(RoutingHandler.java:51)
  org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
  org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:92)
  org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
  org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:162)
  org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
  org.eclipse.jetty.server.Server.handle(Server.java:446)
  org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:271)
  org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:246)
  org.eclipse.jetty.io.AbstractConnection$ReadCallback.run(AbstractConnection.java:358)
  org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:601)
  org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:532)
  java.lang.Thread.run(Thread.java:744)

Caused by: java.net.SocketException: Broken pipe
Call stack: 
  java.net.SocketOutputStream.socketWrite0(Native Method)
  java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113)
  java.net.SocketOutputStream.write(SocketOutputStream.java:159)
  java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
  java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
  com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3964)
  com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2596)
  com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
  com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2832)
  com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5357)
  sun.reflect.GeneratedMethodAccessor20.invoke(Unknown Source)
  sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  java.lang.reflect.Method.invoke(Method.java:606)
  org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
  org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:109)
  org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:80)
  com.sun.proxy.$Proxy28.setAutoCommit(Unknown Source)
  org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:72)
  org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:162)
  org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1431)
  io.dropwizard.hibernate.UnitOfWorkRequestDispatcher.beginTransaction(UnitOfWorkRequestDispatcher.java:57)
  io.dropwizard.hibernate.UnitOfWorkRequestDispatcher.dispatch(UnitOfWorkRequestDispatcher.java:41)
  io.dropwizard.jersey.guava.OptionalResourceMethodDispatchAdapter$OptionalRequestDispatcher.dispatch(OptionalResourceMethodDispatchAdapter.java:37)
  com.sun.jersey.server.impl.uri.rules.HttpMethodRule.accept(HttpMethodRule.java:302)
  com.sun.jersey.server.impl.uri.rules.ResourceObjectRule.accept(ResourceObjectRule.java:100)
  com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147)
  com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84)
  com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542)
  com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473)
  com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419)
  com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409)
  com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409)
  com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:540)
  com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:715)
  javax.servlet.http.HttpServlet.service(HttpServlet.java:848)
  io.dropwizard.jetty.NonblockingServletHolder.handle(NonblockingServletHolder.java:49)
  org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1515)
  org.eclipse.jetty.servlets.UserAgentFilter.doFilter(UserAgentFilter.java:83)
  org.eclipse.jetty.servlets.GzipFilter.doFilter(GzipFilter.java:348)
  io.dropwizard.jetty.BiDiGzipFilter.doFilter(BiDiGzipFilter.java:127)
  org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1486)
  io.dropwizard.servlets.ThreadNameFilter.doFilter(ThreadNameFilter.java:29)
  org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1486)
  org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:519)
  org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1097)
  org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:448)
  org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1031)
  org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:136)
  org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
  com.codahale.metrics.jetty9.InstrumentedHandler.handle(InstrumentedHandler.java:173)
  io.dropwizard.jetty.RoutingHandler.handle(RoutingHandler.java:51)
  org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
  org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:92)
  org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
  org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:162)
  org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:97)
  org.eclipse.jetty.server.Server.handle(Server.java:446)
  org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:271)
  org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:246)
  org.eclipse.jetty.io.AbstractConnection$ReadCallback.run(AbstractConnection.java:358)
  org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:601)
  org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:532)
  java.lang.Thread.run(Thread.java:744)

[Aaron Kimball]

I just found the checkConnectionOnBorrow option. Given that the connection is stale after a long timeout, I think this might be more useful than checkConnectionOnReturn, when the connection object is presumably still good...

[Aaron Kimball]

For anyone else who is curious and stumbles upon this thread in the future: checkConnectionOnBorrow = true seems to have fixed it

- Aaron

[jonathan mukiibi]

Hey Aaron am just wondering whether using Hibernate or JDBI is any different and if so please can you direct me with how you do your hibernate stuff...

[Aaron Kimball]

Hi Jonathan,

I'm pretty new at it so maybe someone else could weigh in... but basically they're different libraries. My app doesn't depend on the dropwizard-jdbi code. There's a tutorial here in the manual: http://dropwizard.readthedocs.org/en/latest/manual/hibernate.html

The basic difference I see is that JDBI requires you to write the SQL directly in your DAOs. Hibernate fabricates the SQL itself using its selector / constraint API. This looked more robust to me on the backend; I use Derby for testing and MySQL for the production side and don't write raw SQL specialized for either backend.

Hope this helps

- Aaron

[jonathan mukiibi]

Thanks