Are there any requirements for topology or host monitoring?

[efenderbosch-atg]

The wrapper driver is working great in dev, qa and stage envs, but in prod every SQL operation takes 30 seconds. I assume it is trying to contact a node and then failing, then falls back to another node. Are there any ports that need to be added to security groups/etc for topology introspection and host monitoring to work properly? Our read and writer nodes are both up and running and can be connected to directly and will serve requests.

I'm using the SF_D0 profile preset.

I turned software.amazon.jdbc logging up to DEBUG and see this:
Node is no longer available in the topology: HostSpec[host=prod-aurora-cluster.cluster-xxxx.us-east-1.rds.amazonaws.com, port=3306, WRITER, AVAILABLE, weight=100, null]

[efenderbosch-atg]

I turned up logging to trace. Some relevant logs

Connecting to jdbc:mysql://prod-aurora-cluster.cluster-xxxxx.us-east-1.rds.amazonaws.com:3306/xxxxx
with properties: 
[verifyServerCertificate] false
[password] ***
[wrapperDialect] aurora-mysql
[tcpKeepAlive] false
[connectTimeout] 10000
[socketTimeout] 5000
[user] *****
[useSSL] true

Topology: 
   HostSpec[host=prod-aurora-green-deumrv.xxxx.us-east-1.rds.amazonaws.com, port=3306, READER, AVAILABLE, weight=608, 2024-06-13 18:36:27.433327]
   HostSpec[host=prod-aurora-us-east-1c-green-pjyxrh.xxxx.us-east-1.rds.amazonaws.com, port=3306, WRITER, AVAILABLE, weight=17, 2024-06-13 18:36:26.442383]

Changes:
	Host 'prod-aurora-us-east-1c-green-pjyxrh.xxxx.us-east-1.rds.amazonaws.com:3306/': [NODE_ADDED]
	Host 'prod-aurora-cluster.cluster-xxxx.us-east-1.rds.amazonaws.com:3306/': [NODE_DELETED]
	Host 'prod-aurora-green-deumrv.xxxx.us-east-1.rds.amazonaws.com:3306/': [NODE_ADDED]

Node is no longer available in the topology: HostSpec[host=prod-aurora-cluster.cluster-xxxx.us-east-1.rds.amazonaws.com, port=3306, WRITER, AVAILABLE, weight=100, null]

Topology: 
   HostSpec[host=prod-aurora-green-deumrv.xxxx.us-east-1.rds.amazonaws.com, port=3306, READER, AVAILABLE, weight=608, 2024-06-13 18:36:27.433327]
   HostSpec[host=prod-aurora-us-east-1c-green-pjyxrh.xxxx.us-east-1.rds.amazonaws.com, port=3306, WRITER, AVAILABLE, weight=17, 2024-06-13 18:36:26.442383]

Writer host: HostSpec[host=prod-aurora-us-east-1c-green-pjyxrh.xxxx.us-east-1.rds.amazonaws.com, port=3306, WRITER, AVAILABLE, weight=17, 2024-06-13 18:36:26.442383]

Writer host address: null

[From cache] Topology: 
   HostSpec[host=prod-aurora-green-deumrv.xxxx.us-east-1.rds.amazonaws.com, port=3306, READER, AVAILABLE, weight=608, 2024-06-13 18:36:27.433327]
   HostSpec[host=prod-aurora-us-east-1c-green-pjyxrh.xxxx.us-east-1.rds.amazonaws.com, port=3306, WRITER, AVAILABLE, weight=17, 2024-06-13 18:36:26.442383]

[hsuamz]

Thanks for reaching out @efenderbosch-atg , the attached logs are helpful.

Could you please confirm if you are using Blue/Green in production? I ask because I see the green annotation in your HostSpec.

If so, please refer to some limitations with the driver and Blue/Green here. As a workaround, could you try using the configuration parameter enableGreenNodeReplacement? For more information, you can also refer to here.

For some additional background, we observe that after a Blue/Green switchover, the service topology will continue to have the green annotation until the cluster is restarted. Since restarting the cluster is not ideal until the topology is correct at the service level, we've implemented a workaround in the driver to help here.

Hope this helps!

[efenderbosch-atg]

We aren't use blue/green any longer, but were during a migration from Aurora MySQL v5 compatible version to Aurora MySQL v8 compatible. I know that AWS tech support had to get involved and I'm wondering if there's still some sort of blue/green remnants hanging around that still need to be cleaned up. We'll probably open a support ticket.

Would your workaround just entail adding &enableGreenNodeReplacement=true to the JDBC URL?

[hsuamz]

Yes, adding &enableGreenNodeReplacement=true to the JDBC URL is correct.

If the -green annotation is not intentional, using the above parameter should work in this case since the logs show that the driver is looking for prod-aurora-cluster.cluster-xxxx.us-east-1.rds.amazonaws.com.

[efenderbosch-atg]

I finally was able to try this today. Failed with No database selected from Hibernate. Rolling back...