JDBCConnectionException after a lot of requests

Viewed 108

I faced such problem. When I make a lot of API requests, then the base stops responding and gives an JDBCConnectionException “Unable to acquire JDBC Connection” error. I tried to increase the number of POOL Connections, but it doesn't help. Could you describe how JPA works when requested? Do I understand correctly that if, for example, MAX-POOL = 2, then the user, having accessed from one thread by calling ONE, receives OneConnection, then when he accesses the TWO repository, he receives a SecondConnection? That is, when accessing TWO repositories from one thread, the user takes TWO CONNECTIONS? Or how? If so, how can we fix this so that there is only one connection per user from one thread?

class Controller {
  
  @Resource
  RepositoryOne one;
  @Resource
  RepositoryTwo two;

  /*
    This is a sample code!
    There may be syntax errors.
    I wrote in a notebook.
    The main question is how does returning
    two results from different repositories work?
  */

  String getResult() {
    // If I call this method VERY MANY TIMES!
    // For example ApacheBenchmark.
    // Then the method raises an error:
    // --------------------------------
    // JDBCConnectionException “Unable to acquire JDBC Connection”
    // --------------------------------
    return one.query() + two.query()
  }

  @Repository
  interface RepositoryOne extends JPA {
    @Query("SELECT `title` from `anything` where `something` = 0")
    String query();
  }

  @Repository
  interface RepositoryTwo extends JPA {
    @Query("SELECT `title` from `anything` where `something` = 0")
    String query();
  }
  
}
1 Answers

So why is JDBCConnectionException thrown?

By default, Hibernate uses its internal database connection pool library. That means it keeps a database connection open to be reused later. And MySQL database server has a timeout value for each connection (default is 8 hours or 28,800 seconds). So if a connection has been idle longer than this timeout value, it will be dropped by the server. Therefore, when the Java database application has been idle longer than MySQL server’s connection timeout value, and the end user tries to connect again, Hibernate reuses the idle connection which was already dropped by the server, hence JDBCConnectionExceptionis thrown.

Possible solution

hibernate.c3p0.timeout: the number of seconds an idle connection is kept in the pool. If a connection is idle longer than this timeout value, then it will be replaced by a new one.

So that means you have to set the value of hibernate.c3p0.timeout less than the wait_timeout value on MySQL server. This definitely solves the problem because the pool maintains no connections that are idle longer than the MySQL server’s timeout value.

read more here

Related