hibernate 拖放向导默认连接池不会使连接正确过期

wj8zmpe1  于 2022-11-14  发布在  其他
关注(0)|答案(1)|浏览(122)

我正在使用带有Hibernate捆绑包的Drop向导。在让它运行几个小时/几天后,任何访问数据库的尝试都将导致

com.mysql.cj.exceptions.CJCommunicationsException: The last packet successfully received from the server was 33.304.281 milliseconds ago. Th
e last packet sent successfully to the server was 33.304.283 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 timeout
s, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.

[...]
Causing: java.sql.SQLNonTransientConnectionException: No operations allowed after connection closed.

配置如下所示:

database:
  # the name of your JDBC driver
  driverClass: com.mysql.cj.jdbc.Driver

  # the username
  user: ...

  # the password
  password: ...

  # the JDBC URL
  url: jdbc:mysql://localhost/my_database

  # any properties specific to your JDBC driver:
  properties:
    charSet: utf8mb4
    hibernate.hbm2ddl.auto: update

  # the maximum amount of time to wait on an empty pool before throwing an exception
  maxWaitForConnection: 1s

  # the SQL query to run when validating a connection s liveness
  validationQuery: "/* MyService Health Check */ SELECT 1"

  # the timeout before a connection validation queries fail
  validationQueryTimeout: 3s

  # the minimum number of connections to keep open
  minSize: 8

  # the maximum number of connections to keep open
  maxSize: 32

  # whether or not idle connections should be validated
  checkConnectionWhileIdle: true

  checkConnectionOnBorrow: true

  # the amount of time to sleep between runs of the idle connection validation, abandoned cleaner and idle pool resizing
  evictionInterval: 10s

  # the minimum amount of time an connection must sit idle in the pool before it is eligible for eviction
  minIdleTime: 1 minute

  maxConnectionAge: 14400s

Hibernate包是从配置示例化的,就像在DW文档中一样:https://www.dropwizard.io/en/latest/manual/hibernate.html#configuration
数据库WAIT_TIMEOUT为28800秒。我不明白为什么连接不会因为老化(从错误消息建议的最后一个包开始的时间大于14400秒)或在验证后没有被删除,因为

checkConnectionOnBorrow: true
  • 拖放向导版本:2.0.18
  • MySQL-Connector-Java版本:8.0.23

我不知道该如何处理这件事,请帮帮忙。

3htmauhk

3htmauhk1#

如果您将database ase.url属性更改为包含AutoReconnect=TRUE,则该问题应该会得到修复。
因此,在您的配置中放入:

database:
   url: jdbc:mysql://localhost/my_database?autoReconnect=true

在连接器/J的文档中:
自动侦测
驱动程序是否应尝试重新建立过时和/或失效的连接?如果启用,驱动程序将对属于当前事务的陈旧或死连接发出的查询抛出异常,但会在新事务中对该连接发出下一个查询之前尝试重新连接。不推荐使用此功能,因为当应用程序不能正确处理SQLExceptions时,它会产生与会话状态和数据一致性相关的副作用,并且仅在您无法配置应用程序以正确处理死连接和陈旧连接所导致的SQLExceptions时使用。或者,作为最后一个选项,研究将MySQL服务器变量‘WAIT_TIMEOUT’设置为一个较高的值,而不是默认的8小时。
参考文献:https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-connp-props-high-availability-and-clustering.html

相关问题