Unable to connect to Aurora MySQL reader with blue/green deployed cluster

[markusrydh]

Describe the bug

We are using this driver together with MariaDB driver to connect to an AWS Aurora MySQL 5.7 cluster using readWriteSplitting with an internal HikariCP connection pool. It works fine if the cluster is not deployed with blue/green deployment, but if it was deployed using blue/green deployment then connections to the reader fail due to unknown host. The host name that the driver tries to connect to contains "green" or "blue", but no such host name can be resolved.

Note that the actual blue/green deployment took place a long time ago, and no switch is performed related to the time of this issue.

Expected Behavior

We expect the readWriteSplitting plugin to use hostnames for the reader replicas that can actually be resolved, so that the internal connection pools for read-only connections are populated.

What plugins are used? What other connection properties were set?

Plugins: readWriteSplitting,failover,efm

Current Behavior

The driver seems to use data from the @@aurora_server_id to generate fqdn to connect to, but since the instances seem to retain server-ids from the blue/green deployment the fqdn is not valid and it fails to connect.

2023-10-11 09:05:04.643  INFO 1 --- [ dbThreadPool-1] com.zaxxer.hikari.HikariDataSource       : hikari-db-readonly-pool - Starting...

2023-10-11 09:05:05.742 ERROR 1 --- [ dbThreadPool-1] com.zaxxer.hikari.pool.HikariPool        : hikari-db-readonly-pool - Exception during pool initialization.

java.sql.SQLNonTransientConnectionException: Socket fail to connect to host:address=(host=production-main-1-green-zymnmc.cxl<redacted>.rds.amazonaws.com)(port=3306)(type=primary). production-main-1-green-zymnmc.cxl<redacted>.rds.amazonaws.com
	at org.mariadb.jdbc.client.impl.ConnectionHelper.connectSocket(ConnectionHelper.java:137)
	at org.mariadb.jdbc.client.impl.StandardClient.<init>(StandardClient.java:99)
	at org.mariadb.jdbc.Driver.connect(Driver.java:70)
	at org.mariadb.jdbc.Driver.connect(Driver.java:101)
	at org.mariadb.jdbc.Driver.connect(Driver.java:27)
	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.checkFailFast(HikariPool.java:561)
	at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115)
	at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:81)
	at software.amazon.jdbc.HikariPooledConnectionProvider.createHikariDataSource(HikariPooledConnectionProvider.java:329)
	at software.amazon.jdbc.HikariPooledConnectionProvider.lambda$connect$3(HikariPooledConnectionProvider.java:198)
	at software.amazon.jdbc.util.SlidingExpirationCache.lambda$computeIfAbsent$0(SlidingExpirationCache.java:81)
	at java.base/java.util.concurrent.ConcurrentHashMap.computeIfAbsent(ConcurrentHashMap.java:1708)
	at software.amazon.jdbc.util.SlidingExpirationCache.computeIfAbsent(SlidingExpirationCache.java:78)
	at software.amazon.jdbc.HikariPooledConnectionProvider.connect(HikariPooledConnectionProvider.java:196)
	at software.amazon.jdbc.plugin.DefaultConnectionPlugin.connectInternal(DefaultConnectionPlugin.java:155)
	at software.amazon.jdbc.plugin.DefaultConnectionPlugin.connect(DefaultConnectionPlugin.java:148)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$4(ConnectionPluginManager.java:281)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$0(ConnectionPluginManager.java:182)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$1(ConnectionPluginManager.java:186)
	at software.amazon.jdbc.plugin.efm.HostMonitoringConnectionPlugin.connectInternal(HostMonitoringConnectionPlugin.java:279)
	at software.amazon.jdbc.plugin.efm.HostMonitoringConnectionPlugin.connect(HostMonitoringConnectionPlugin.java:274)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$4(ConnectionPluginManager.java:281)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$2(ConnectionPluginManager.java:186)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$1(ConnectionPluginManager.java:186)
	at software.amazon.jdbc.plugin.staledns.AuroraStaleDnsHelper.getVerifiedConnection(AuroraStaleDnsHelper.java:62)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.connectInternal(FailoverConnectionPlugin.java:727)
	at software.amazon.jdbc.plugin.failover.FailoverConnectionPlugin.connect(FailoverConnectionPlugin.java:720)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$4(ConnectionPluginManager.java:281)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$2(ConnectionPluginManager.java:186)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$null$1(ConnectionPluginManager.java:186)
	at software.amazon.jdbc.plugin.readwritesplitting.ReadWriteSplittingPlugin.connectInternal(ReadWriteSplittingPlugin.java:146)
	at software.amazon.jdbc.plugin.readwritesplitting.ReadWriteSplittingPlugin.connect(ReadWriteSplittingPlugin.java:141)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$connect$4(ConnectionPluginManager.java:281)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$2(ConnectionPluginManager.java:186)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithSubscribedPlugins(ConnectionPluginManager.java:164)
	at software.amazon.jdbc.ConnectionPluginManager.connect(ConnectionPluginManager.java:278)
	at software.amazon.jdbc.PluginServiceImpl.connect(PluginServiceImpl.java:438)
	at software.amazon.jdbc.plugin.readwritesplitting.ReadWriteSplittingPlugin.getNewReaderConnection(ReadWriteSplittingPlugin.java:516)
	at software.amazon.jdbc.plugin.readwritesplitting.ReadWriteSplittingPlugin.initializeReaderConnection(ReadWriteSplittingPlugin.java:486)
	at software.amazon.jdbc.plugin.readwritesplitting.ReadWriteSplittingPlugin.switchToReaderConnection(ReadWriteSplittingPlugin.java:449)
	at software.amazon.jdbc.plugin.readwritesplitting.ReadWriteSplittingPlugin.switchConnectionIfRequired(ReadWriteSplittingPlugin.java:326)
	at software.amazon.jdbc.plugin.readwritesplitting.ReadWriteSplittingPlugin.execute(ReadWriteSplittingPlugin.java:226)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$execute$3(ConnectionPluginManager.java:243)
	at software.amazon.jdbc.ConnectionPluginManager.lambda$makePluginChainFunc$2(ConnectionPluginManager.java:186)
	at software.amazon.jdbc.ConnectionPluginManager.executeWithSubscribedPlugins(ConnectionPluginManager.java:164)
	at software.amazon.jdbc.ConnectionPluginManager.execute(ConnectionPluginManager.java:240)
	at software.amazon.jdbc.util.WrapperUtils.executeWithPlugins(WrapperUtils.java:219)
	at software.amazon.jdbc.util.WrapperUtils.runWithPlugins(WrapperUtils.java:161)
	at software.amazon.jdbc.wrapper.ConnectionWrapper.setReadOnly(ConnectionWrapper.java:338)
	at com.zaxxer.hikari.pool.ProxyConnection.setReadOnly(ProxyConnection.java:423)
	at com.zaxxer.hikari.pool.HikariProxyConnection.setReadOnly(HikariProxyConnection.java)
	at org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:190)
	at org.springframework.orm.jpa.vendor.HibernateJpaDialect.beginTransaction(HibernateJpaDialect.java:153)
	at org.springframework.orm.jpa.JpaTransactionManager.doBegin(JpaTransactionManager.java:421)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:400)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:595)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:382)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:707)
<redacted>
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1768)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.net.UnknownHostException: production-main-1-green-zymnmc.cxl<redacted>.rds.amazonaws.com
	at java.base/sun.nio.ch.NioSocketImpl.connect(NioSocketImpl.java:572)
	at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:327)
	at java.base/java.net.Socket.connect(Socket.java:633)
	at org.mariadb.jdbc.client.impl.ConnectionHelper.connectSocket(ConnectionHelper.java:131)
	... 71 common frames omitted

Reproduction Steps

Connect to AWS Aurora MySQL cluster that has been deployed with blue/green deployment with at least one reader replica using the plugins above.

Possible Solution

Maybe stripping out any -blue-xxxx and -green-xxxx from the hostname, at least as a retry if it fails? Or are they needed during the switch-over in case a new blue/green deployment?

Additional Information/Context

Cluster:
writer endpoint: production-main.cluster-cxl.rds.amazonaws.com
reader endpoint: production-main.cluster-ro-cxl.rds.amazonaws.com

Writer instance:
DB identifier: production-main-0
Engine: 5.7.mysql_aurora.2.11.2
Endpoint: production-main-0.cxl.rds.amazonaws.com
@@aurora_server_id: production-main-0-green-rcffig

Reader instance:
DB identifier: production-main-1
Engine: 5.7.mysql_aurora.2.11.2
Endpoint: production-main-1.cxl.rds.amazonaws.com
@@aurora_server_id: production-main-1-green-zymnmc

The AWS Advanced JDBC Driver version used

2.2.4

JDK version used

Amazon Corretto 17.0.8.1

Operating System and version

Linux

[crystall-bitquill]

Hi @markusrydh,

Unfortunately the driver does not officially support Blue/Green deployments.

Support for its features are in the backlog, but we cannot comment on a timeline right now.

[markusrydh]

Ok, the documentation does not really make that clear: https://github.com/awslabs/aws-advanced-jdbc-wrapper/blob/main/docs/KnownLimitations.md

It more give the impression that it works, but in a switch-over situation you would need retries for it to reconnect.

[crystall-bitquill]

Hi @markusrydh,

Thanks for your feedback! We've updated the known limitations documentation to be a bit clearer (#680), apologies for the inconvenience. Since Blue/Green deployments aren't officially supported, we'll be closing this issue soon. However, please feel free to reach out again if you have any questions or concerns.

Thank you!

[jstultz]

That doesn't seem to fully clarify the issue here: my read of that documentation is that the driver doesn't support performing a blue/green switchover. The actual problem seems to be that it doesn't work with a cluster that has ever undergone a blue/green deployment in the past, which is very different.

[sergiyvamz]

Hi @jstultz

We investigated Blue/Green deployments with an Aurora cluster and would like to clarify
what driver features are supported in such case.

In general, the AWS Advanced JDBC Wrapper supports any host names provided in a connection string, including those that are specific for Blue/Green Deployments. For instance, the following endpoints cause
no issues and the driver can successfully connect to a database.

bg-test.cluster-<XYZ>.us-east-2.rds.amazonaws.com
bg-test.cluster-ro-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-1.<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-2.<XYZ>.us-east-2.rds.amazonaws.com

bg-test-green-5jznso.cluster-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-green-5jznso.cluster-ro-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-1-green-q8dhgs.<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-2-green-xqjhn3.<XYZ>.us-east-2.rds.amazonaws.com

bg-test-old1.cluster-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-old1.cluster-ro-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-1-old1.<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-2-old1.<XYZ>.us-east-2.rds.amazonaws.com

When Blue/Green Deployments is created and switchover is in progress, the driver's
advanced features, like cluster failover and read/write splitting,
may not be supported. However, the driver's basic functionality, like connecting
to a database and executing SQL statements, is still in place.

For more detail, supported features are presented below. The example refers
to a bg-test Aurora cluster with two instances: instance-1 and instance-2.

1. before Blue/Green Deployment has been created:

   available endpoints:
   bg-test.cluster-<XYZ>.us-east-2.rds.amazonaws.com
bg-test.cluster-ro-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-1.<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-2.<XYZ>.us-east-2.rds.amazonaws.com

   the driver supported features:
   all stated features including
   - basic connectivity and SQL statement execution
   - advanced features (failover support and RW Splitting)

2. after Blue/Green Deployment has been created:

   available endpoints:
   bg-test.cluster-<XYZ>.us-east-2.rds.amazonaws.com
bg-test.cluster-ro-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-1.<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-2.<XYZ>.us-east-2.rds.amazonaws.com

bg-test-green-5jznso.cluster-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-green-5jznso.cluster-ro-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-1-green-q8dhgs.<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-2-green-xqjhn3.<XYZ>.us-east-2.rds.amazonaws.com

   the driver supported features:
   all stated features including
   - basic connectivity and SQL statement execution
   - advanced features (failover support and RW Splitting)

3. during Blue/Green switchover:
   the driver supported features:
   - basic connectivity and SQL statement execution

   the driver NOT supported features:
   - transitioning/connecting to a new-blue cluster
   - advanced features (failover support and RW Splitting)

4. after Blue/Green switchover:
   available endpoints:
   bg-test-old1.cluster-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-old1.cluster-ro-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-1-old1.<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-2-old1.<XYZ>.us-east-2.rds.amazonaws.com

bg-test.cluster-<XYZ>.us-east-2.rds.amazonaws.com
bg-test.cluster-ro-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-1.<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-2.<XYZ>.us-east-2.rds.amazonaws.com

   the driver supported features:
   - basic connectivity and SQL statement execution

   the driver NOT supported features:
   - advanced features (failover support and RW Splitting)

5. after rebooting new-blue instances:
   available endpoints:
   bg-test.cluster-<XYZ>.us-east-2.rds.amazonaws.com
bg-test.cluster-ro-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-1.<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-2.<XYZ>.us-east-2.rds.amazonaws.com

   the driver supported features (when connecting to new-blue):
   all stated features including
   - basic connectivity and SQL statement execution
   - advanced features (failover support and RW Splitting)

6. after rebooting old1 instances:
   available endpoints:
   bg-test-old1.cluster-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-old1.cluster-ro-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-1-old1.<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-2-old1.<XYZ>.us-east-2.rds.amazonaws.com

   the driver supported features (when connecting to old1 cluster):
   all stated features including
   - basic connectivity and SQL statement execution
   - advanced features (failover support and RW Splitting)

7. after Blue/Green Deployment deleted:
   separate clusters:

• bg-test-old1
bg-test-old1.cluster-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-old1.cluster-ro-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-1-old1.<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-2-old1.<XYZ>.us-east-2.rds.amazonaws.com

• bg-test
bg-test.cluster-<XYZ>.us-east-2.rds.amazonaws.com
bg-test.cluster-ro-<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-1.<XYZ>.us-east-2.rds.amazonaws.com
bg-test-instance-2.<XYZ>.us-east-2.rds.amazonaws.com

  the driver supported features:
  all stated features including
  - basic connectivity and SQL statement execution
  - advanced features (failover support and RW Splitting)

I hope that clarifies the issue in detail.
Thank you!

[hsuamz]

Will be closing this issue since our documentation has been clarified with #1028. For visibility, there are limitations with Blue/Green support and we have recently implemented a workaround with the usage of enableGreenNodeReplacement configuration parameter, which allows the driver to override incorrect topology metadata and try to connect to available new Blue endpoints.

We'll make an announcement when full support for Blue/Green is made available.