How to resolve this error for AuororaMySQL "SELECT command denied to user 'user'@'ip' for table 'rds_topology'"

[ksampath8]

Hi Team , we are noticing below error messages with AWS advanced wrapper driver version 2.3.1
We are using AWS Aurora MySQL database.
As mentioned in the docs , we are not mentioning the wrapperDialect parameter.
It looks like the wrapper driver is using RdsMultiAzDbClusterMysqlDialect.java , instead of AuroraMysqlDialect.java

Could you please suggest if below is error because the AWS wrapper tries to determine if it's RDS or Aurora and then, query fails and it falls back to AuroraMySQL Dialect?

Please let me know if I can share any additional details
Trace

java.sql.SQLSyntaxErrorException: SELECT command denied to user 'service'@'ip' for table 'rds_topology'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:121)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200)
	at software.amazon.jdbc.dialect.RdsMultiAzDbClusterMysqlDialect.isDialect(RdsMultiAzDbClusterMysqlDialect.java:44)
	at software.amazon.jdbc.dialect.DialectManager.getDialect(DialectManager.java:222)
	at software.amazon.jdbc.PluginServiceImpl.updateDialect(PluginServiceImpl.java:566)
	at software.amazon.jdbc.plugin.DefaultConnectionPlugin.connectInternal(DefaultConnectionPlugin.java:216)
	at software.amazon.jdbc.plugin.DefaultConnectionPlugin.connect(DefaultConnectionPlugin.java:191)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:357)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$0(ConnectionPluginManager.java:251)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:228)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$1(ConnectionPluginManager.java:251)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:256)
	at software.amazon.jdbc.plugin.efm.HostMonitoringConnectionPlugin.connectInternal(HostMonitoringConnectionPlugin.java:284)
	at software.amazon.jdbc.plugin.efm.HostMonitoringConnectionPlugin.connect(HostMonitoringConnectionPlugin.java:279)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:357)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:255)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:228)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:255)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:256)
	at software.amazon.jdbc.plugin.staledns.AuroraStaleDnsHelper.getVerifiedConnection(AuroraStaleDnsHelper.java:72)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.connectInternal(FailoverConnectionPlugin.java:920)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.connect(FailoverConnectionPlugin.java:913)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:357)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:255)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:228)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:255)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:256)
	at software.amazon.jdbc.plugin.AuroraConnectionTrackerPlugin.connectInternal(AuroraConnectionTrackerPlugin.java:91)
	at software.amazon.jdbc.plugin.AuroraConnectionTrackerPlugin.connect(AuroraConnectionTrackerPlugin.java:84)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:357)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:255)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:228)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:255)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithSubscribedPlugins(ConnectionPluginManager.java:218)
	at software.amazon.jdbc.ConnectionPluginManager.connect(ConnectionPluginManager.java:354)
	at software.amazon.jdbc.wrapper.ConnectionWrapper.init(ConnectionWrapper.java:161)
	at software.amazon.jdbc.wrapper.ConnectionWrapper.<init>(ConnectionWrapper.java:106)
	at software.amazon.jdbc.Driver.connect(Driver.java:172)
	at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:358)
	at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
	at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:477)
	at com.zaxxer.hikari.pool.HikariPool.access$100(HikariPool.java:71)
	at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:725)
	at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:711)
	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.base/java.lang.Thread.run(Unknown Source)

[brunos-bq]

Hello @ksampath8 ,

Thank you very much for reaching about this issue.

For some reason, an exception was thrown while executing the isDialect() method and it should not have been.

We have written and merged a fix that should fix the issue.

Could you kindly checkout our snapshot build and let us know if the issue still persists?

Thank you!

[ksampath8]

Hi @brunos-bq
Thank you very much for looking into this .
We will test the snapshot build and update back.

[malacroix]

Hi @brunos-bq,

We have used the 2.3.3-SNAPSHOT snapshot, but it didn't actually fix the issue. I could confirm that the code was updated, since now the stacktrace is slightly different and is aligned with the changes you introduced in #799. You can see below the updated stacktrace that matches the updated code.

I've looked a bit more closely at patterns of this error and I think I have a better understanding of what is going on, so here is additional detail about what were are seeing.

1. We are not seeing those errors in any log. We have actually been seeing those errors in mysql service traces through DataDog where exception/error details are captured in the traces. This error doesn't seem to cause any problem to our applications, whether it is with the latest snapshot or a released version, but it generates a high amount of mysql errors the more we convert our services to use the new AWS Advanced JDBC Wrapper.

2. This error did not appear with the AWS MySQL JDBC Driver, which we have been using for at least 9 months now.

3. Analyzing the errors for a particular service in a particular environment, I've noticed this exception is thrown once every 30 minutes per pod of the service. And it's mostly always in the HikariPool-1 connection adder (if the service is actually idle). Those are Spring Boot applications where HikariCP is enabled by default. This "30 minutes" windows seems to relate to the maxLifetime default configuration (see HikariCP reference here).

This last point seems to indicate that the problem is happening every time a new connection is made to the Database. It seems that the AWS JDBC Wrapper tries to see if it's a RDS MultiAz database, which isn't the case for us. But the current approach of how to determine has 2 issues in my mind:

1. The RdsMultiAzDbClusterMysqlDialect.isDialect() relies on the existence of the rds_topology table, which seem to only exist in a particular setup. This is causing useless errors that surely could be avoided. Since we have not noticed such errors with the AWS MySQL JDBC error, shouldn't there be any other way to achive the "isDialect()" check for the RdsMultiAzDbClusterMysqlDialect?
2. The error happens on every connection. This is generated a high amount of those errors. Shouldn't it be possible to somehow cache the result for the Dialect? Is there really a possibility that the Dialect could change live without restarting the application?

I hope this can help target the problem. Let me know if you need more details or data about this.

---

Update stacktrace with snapshot 2.3.3-SNAPSHOT

java.sql.SQLSyntaxErrorException: SELECT command denied to user '<service>'@'<ip>' for table 'rds_topology'
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1200)
	at software.amazon.jdbc.dialect.RdsMultiAzDbClusterMysqlDialect.isDialect(RdsMultiAzDbClusterMysqlDialect.java:47)
	at software.amazon.jdbc.dialect.DialectManager.getDialect(DialectManager.java:222)
	at software.amazon.jdbc.PluginServiceImpl.updateDialect(PluginServiceImpl.java:566)
	at software.amazon.jdbc.plugin.DefaultConnectionPlugin.connectInternal(DefaultConnectionPlugin.java:216)
	at software.amazon.jdbc.plugin.DefaultConnectionPlugin.connect(DefaultConnectionPlugin.java:191)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:362)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$0(ConnectionPluginManager.java:256)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:233)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$1(ConnectionPluginManager.java:256)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:261)
	at software.amazon.jdbc.plugin.efm.HostMonitoringConnectionPlugin.connectInternal(HostMonitoringConnectionPlugin.java:282)
	at software.amazon.jdbc.plugin.efm.HostMonitoringConnectionPlugin.connect(HostMonitoringConnectionPlugin.java:277)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:362)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:260)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:233)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:260)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:261)
	at software.amazon.jdbc.plugin.staledns.AuroraStaleDnsHelper.getVerifiedConnection(AuroraStaleDnsHelper.java:72)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.connectInternal(FailoverConnectionPlugin.java:922)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.connect(FailoverConnectionPlugin.java:915)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:362)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:260)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:233)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:260)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$2(ConnectionPluginManager.java:261)
	at software.amazon.jdbc.plugin.AuroraConnectionTrackerPlugin.connectInternal(AuroraConnectionTrackerPlugin.java:91)
	at software.amazon.jdbc.plugin.AuroraConnectionTrackerPlugin.connect(AuroraConnectionTrackerPlugin.java:84)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$6(ConnectionPluginManager.java:362)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$3(ConnectionPluginManager.java:260)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithTelemetry(ConnectionPluginManager.java:233)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$4(ConnectionPluginManager.java:260)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithSubscribedPlugins(ConnectionPluginManager.java:223)
	at software.amazon.jdbc.ConnectionPluginManager.connect(ConnectionPluginManager.java:359)
	at software.amazon.jdbc.wrapper.ConnectionWrapper.init(ConnectionWrapper.java:161)
	at software.amazon.jdbc.wrapper.ConnectionWrapper.<init>(ConnectionWrapper.java:106)
	at software.amazon.jdbc.Driver.connect(Driver.java:170)
	at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138)
	at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364)
	at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206)
	at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476)
	at com.zaxxer.hikari.pool.HikariPool.access$100(HikariPool.java:71)
	at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:726)
	at com.zaxxer.hikari.pool.HikariPool$PoolEntryCreator.call(HikariPool.java:712)
	at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
	at java.base/java.lang.Thread.run(Thread.java:829)

[brunos-bq]

Hello @ksampath8,

We have written and merged another change with the hope of finally resolving that issue.

Would you mind trying the new snapshot version and verifying whether the issue still persists?

Thanks!

[malacroix]

Thanks for the quick action! I saw the PR #824 and I think that should indeed fix things. We'll update the snapshot and have it run for some time and will get back to you.

[malacroix]

Ok @brunos-bq, we have a service that was redeployed using aws-advanced-jdbc-wrapper-2.3.3-20240110.235457-24.jar that has been running for over an hour now and we have yet to see an error. This looks pretty good! Normally, we would have had en error per pod on startup on "main" thread and then, every 30 minutes in the "HikariPool-1 connection adder", but so far nothing as expected. We are going to leave this snapshot running for more time just to ensure nothing else comes out.

Would you be able to let us know when to expect this fix to be released? Thanks!

[brunos-bq]

Hello @malacroix,

The cadence of releases was a bit impacted by the holiday season but we aim to release a new version of the driver every month, often near the end of every month. As the changes were merged, this fix will automatically be included in any new release.

https://github.com/awslabs/aws-advanced-jdbc-wrapper?tab=readme-ov-file#releases

[malacroix]

Excellent, thanks for the information. Will look forward the next release.

Again, thanks for your quick reply and action!

[brunos-bq]

The fix implemented for this issue has been released in the latest version of the AWS JDBC Driver.

Closing the ticket.