Failover with a autocommit=false setup lead to broken connections

[jebeaudet]

Describe the bug

I have a Spring Boot application with HikariCP and hibernate running with autocommit=false like this (for this reason):

spring:
    hikari:
      auto-commit: false
  jpa:
    properties:
      hibernate.connection.provider_disables_autocommit: true

During a recent event of applying an OS patch on my aurora cluster, the event lead to some connection being completely broken in the pool. These connections were always throwing the error with the code 08007 which are flagged as DO_NOT_EVICT by our SQLExceptionOverride, as suggested in the driver readme. This meant that all requests using that connection would end up as a 500.

Following this, I've circled back to the documention to see that the proper usage of this connection reuse behavior is to reset the session state ourselves in the code. However, this is easily overlooked IMO and it seems to lead to a completely broken connection in my case.

Expected Behavior

We should never have a connection not being evicted that is not in a valid state.

Current Behavior

Session state is completely lost and we end up with a broken connection in the pool.

Here is the stack trace I was getting (I've removed some irrelevant frames) :

org.springframework.orm.jpa.JpaSystemException: Unable to commit against JDBC Connection; nested exception is org.hibernate.TransactionException: Unable to commit against JDBC Connection
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:331)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233)
	at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:566)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:743)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:711)
	at com.coveo.cloud.baseservice.configuration.datasource.GlobalClusterAwareTransactionManager.commit(GlobalClusterAwareTransactionManager.java:99)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupport.java:654)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:407)
	at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: org.hibernate.TransactionException: Unable to commit against JDBC Connection
	at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:92)
	at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:282)
	at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:101)
	at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:562)
	... 203 common frames omitted
Caused by: java.sql.SQLException: Can't call commit when autocommit=true
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:67)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ConnectionImpl.commit(ConnectionImpl.java:801)
	at jdk.internal.reflect.GeneratedMethodAccessor140.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:568)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ha.ConnectionProxy.lambda$invoke$0(ConnectionProxy.java:198)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ha.plugins.DefaultConnectionPlugin.execute(DefaultConnectionPlugin.java:80)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ha.plugins.NodeMonitoringConnectionPlugin.execute(NodeMonitoringConnectionPlugin.java:249)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ha.plugins.failover.FailoverConnectionPlugin.execute(FailoverConnectionPlugin.java:276)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ha.plugins.ConnectionPluginManager.execute(ConnectionPluginManager.java:138)
	at software.aws.rds.jdbc.mysql.shading.com.mysql.cj.jdbc.ha.ConnectionProxy.invoke(ConnectionProxy.java:195)
	at jdk.proxy3/jdk.proxy3.$Proxy178.commit(Unknown Source)
	at com.zaxxer.hikari.pool.ProxyConnection.commit(ProxyConnection.java:387)
	at com.zaxxer.hikari.pool.HikariProxyConnection.commit(HikariProxyConnection.java)
	at com.coveo.cloud.baseservice.configuration.datasource.readonly.ReadOnlyConnection.commit(ReadOnlyConnection.java:78)
	at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:86)
	... 206 common frames omitted

Reproduction Steps

Have a autocommit=false setup and trigger a non graceful failover (a failover with a good period of unavailability, not the button in the rds console).

Possible Solution

1. Would it be possible to keep the connection session parameters in a variable somewhere and restore it when the failover kicks in?
2. The documentation should be updated with an emphasis that you need to handle the autoCommit status of the connection in the case of a failover.
3. Do you have any experience with hooks or library that could do the retry logic mentioned in the readme when using hibernate + spring for tx management?

Additional Information/Context

No response

The AWS JDBC Driver for MySQL version used

1.1.7

JDK version used

OpenJDK 64-Bit Server VM Zulu17.42+19-CA (build 17.0.7+7-LTS, mixed mode)

Operating System and version

azul jdk container running on ubuntu 20.04

[crystall-bitquill]

Hi @jebeaudet,

Thanks for reaching out and raising this issue.

We'll take a look at this and keep you updated as we investigate.

Thank you for your patience!

[crystall-bitquill]

Hi @jebeaudet,

We've recently merged a fix. Could you kindly checkout the latest snapshot build and let us know if the issue persists?

Thank you!

[crystall-bitquill]

Hi @jebeaudet,

I wanted to check in to see if the issue was resolved with the snapshot build.

If there are no more updates on this ticket in the next few days, it will be closed. However, if you have any further questions or comments please feel free to create a new issue or to reopen this one.

Thank you!

[jebeaudet]

Hi @jebeaudet,

I wanted to check in to see if the issue was resolved with the snapshot build.

If there are no more updates on this ticket in the next few days, it will be closed. However, if you have any further questions or comments please feel free to create a new issue or to reopen this one.

Thank you!

Hi! It's not easy for me to use a snapshot build. Once this gets released I'll follow up if there are issues with this. Feel free to close it in the meantime.

Thanks!

[nshelms]

Hi -- I believe I'm running into a similar use case; however, i'm running with autoCommit=true which is throwing SQLSTATE(08S02) exception. I'd like to test the snapshot version but my network cannot consume the snapshot from sonatype. Any chance this could be uploaded to maven or cloudera?

[crystall-bitquill]

Hi @nshelms,

This project follows a monthly release cadence, so we're actually in the process of releasing an updated version of the driver for July. It should be public by the end of the month.

[crystall-bitquill]

Hi @jebeaudet and @nshelms,

Just to let you know, version 1.1.9 of the AWS JDBC Driver for MySQL has been released, and that includes the fix for this issue. We'll be closing this issue now, but if you have any questions or concerns please feel free to create a new issue or reopen this one.

[nshelms]

@crystall-bitquill and @karenc-bq -- We tested the new version today and it seems to have resolved our issue. Thank you!