using MySQL `autoReconnect=true` configuration results in unhandled access denied errors

[steve-thousand]

Took some time tracking down the root cause, but I was getting com.mysql.cj.exceptions.CJException: Access denied for user while using the secrets manager MySQL driver for MySQL driver 8.0.31. This is a problem because the error handling for the AWSSecretsManagerMySQLDriver expects a SQLException but the CJException is not a SQLException subclass, so these credential issues were not being automatically handled by the driver.

I found that the cause was using the autoReconnect=true JDBC parameter. When using this parameter, the MySQL driver ends up taking a separate code from autoReconnect=false:

java.sql.SQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:110)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
	at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:898)
	at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:823)
	at com.mysql.cj.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:448)
	at com.mysql.cj.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:241)
	at com.mysql.cj.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:198)
...
Caused by: com.mysql.cj.exceptions.CJException: Access denied for user 'test_user1671740990998'@'172.17.0.1' (using password: YES)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
	at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:129)
	at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:848)
	at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:770)
	at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:738)
	at com.mysql.cj.protocol.a.NativeProtocol.checkErrorMessage(NativeProtocol.java:155)
	at com.mysql.cj.protocol.a.NativeAuthenticationProvider.proceedHandshakeWithPluggableAuthentication(NativeAuthenticationProvider.java:472)
	at com.mysql.cj.protocol.a.NativeAuthenticationProvider.connect(NativeAuthenticationProvider.java:212)
	at com.mysql.cj.protocol.a.NativeProtocol.connect(NativeProtocol.java:1433)
	at com.mysql.cj.NativeSession.connect(NativeSession.java:133)
	at com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:842)
	... 82 more

When autoReconnect=false, the com.mysql.cj.jdbc.ConnectionImpl.createNewIO method calls com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly instead of com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries. The com.mysql.cj.jdbc.ConnectionImpl.connectWithRetries method ends up throwing a SQLNonTransientConnectionException containing a CJException with a vendorCode equal to the expected error code 1045.

So, the immediate fix I've found is to just not use autoReconnect=true, turns out its usage is not recommended by MySQL anyway. But it seems like others have found the error detection to be problematic and the error handling here could be improved to unwrap exceptions and check for CJExceptions as well as SQLExceptions

[jbct]

Thanks for the bug report, steve-thousand. We'll track this for a future update.

[iifawzi]

Hello @willtong1234 @simonmarty, I’m wondering if this is something we should fix (happy to commit time to it). Although autoReconnect is deprecated, it’s still important to maintain consistent behavior no matter what flags are used in my opinion.

When using MySQL Connector/J with autoReconnect=true, authentication failures behave differently due to internal retries. Instead of throwing a direct SQLException, the driver wraps the error in a SQLNonTransientConnectionException for the retry error with a CJException as the root cause.

Options that I could think of:

Option 1: Check SQLState 28000
SQLNonTransientConnectionException is already an instance of SQLException, however, the vendor code is 0 not 1045, but it has SQLState of 28000 that we can check.

• Pros: simpler, no dependencies needed.
• Cons: 28000 is a generic authentication state; may catch false positives

Option 2: Direct Connector/J dependency

CJException is defined in the connector, to be able to have something like instanceof CJException an option could be to have it as a dependency, otherwise we can utilize reflection.

• Pros: Type-safe, straightforward implementation
• Cons: Introduces a hard dependency on MySQL Connector/J which can be seen as unnecessary.

Option 3: Reflection-based CJException handling

• Pros: No hard dependency, type-safe error code extraction
• Cons: Requires reflection, slightly more complex (especially for testing)

  if (t.getClass().getName().equals("com.mysql.cj.exceptions.CJException")) {
      try {
          java.lang.reflect.Method getVendorCodeMethod = t.getClass().getMethod("getVendorCode");
          int vendorCode = (Integer) getVendorCodeMethod.invoke(t);
          if (vendorCode == errorCode) {
              return true;
          }
      } catch (NoSuchMethodException | IllegalAccessException |
               InvocationTargetException | ClassCastException e) {
      }
  }

I personally think Option 1 or 3 would be the best choices.

For Option 1, 28000 maps to a small set of access-denied errors, so the risk of false positives is relatively low, and it might even help in similar cases like #222:

Error 1045 — ER_ACCESS_DENIED_ERROR (SQLSTATE: 28000)

Error 1698 — ER_ACCESS_DENIED_NO_PASSWORD_ERROR (SQLSTATE: 28000)

Error 1873 — ER_ACCESS_DENIED_CHANGE_USER_ERROR (SQLSTATE: 28000)

If we want to avoid introducing a direct dependency, the reflection-based approach seems like a solid alternative if you think 28000 is overkill.

Happy to implement the fix in whichever way we align on, as I might be missing other simpler/better options.