Issue with different nodes connecting to long query and kill query when using reader endpoint

[JaydenA]

Hello. I have a few questions regarding AWS JDBC.

To briefly explain my situation, I am using Spring Boot to connect to an Aurora cluster's cluster reader endpoint. I have set a query timeout in my application. However, when there are multiple reader nodes, I've encountered a situation where long query and kill query sessions, each on different reader nodes, can cause the kill query statement to fail.
With that context in mind, here are my questions:

• When using AWS JDBC, is it possible to avoid the above issue and automatically create a session on the reader node where a long query is running when performing a kill query?

• In the case of AWS JDBC Driver for MySQL, does it have a specific way of handling query timeouts? If so, what is the approach? (e.g., sending kill queries through a separate connection)

If the above is not possible, should I consider using SET SESSION MAX_EXECUTION_TIME, and do you have any best practices related to this with AWS JDBC?

[crystall-bitquill]

Hi @JaydenA,

To clarify, are you trying to run a long query on one instance, and on a second instance a kill query to abort the long query in order to achieve a query timeout?

When the kill query fails, what kind of error do you get? I believe instances cannot access process ids of other instances, and may result in an "Unknown thread id" error if you try a kill query with a process id not available on the current instance.

The AWS JDBC Driver for MySQL is based on the MySQL Connector/J driver and so also uses its approach to query timeouts. In this file it can be seen that a new connection is opened and a kill query is sent.

To timeout a query while using Spring Boot, there are a few options you could try.

• One option is to set the query timeout value using the JdbcTemplate class, like this: jdbcTemplate().setQueryTimeout(10);.
• If you're using transactions, you can also set the timeout value in the transaction annotation, like this: @Transactional(timeout = 10).

[crystall-bitquill]

Hi @JaydenA,

Thank you for clarifying the issue, I was able to reproduce the problem. There is no feature available at the moment to redirect kill queries to the correct reader instance, but we'll be looking into a solution and will let you know when it's ready.

Thank you for your patience!

[JaydenA]

Hi @crystall-bitquill, Thank you for your reply. Is there any way I can track the solution process in the future?

[crystall-bitquill]

Hi @JaydenA, I've opened a PR (#474) for this issue. It's not complete yet, but I will let you know when it's been merged.

[crystall-bitquill]

Hi @JaydenA,

To update you, a fix has been merged for this issue (PR #474) and the changes have been included in the latest release of the driver (version 1.1.11). Could you kindly check it out and let us know if the issue persists?

Thank you!