Tabletserver connpool query killing will not work correctly against multi-instance MySQL backends, may kill unrelated queries

[aquarapid]

I realized this while reading through the code:

• We spin up a dbaPool alongside the main pool
• On shutdown and for the Vitess query deadlines, we kill running query/queries by grabbing a connection from the dbaPool, and then KILL-ing the thread id for the relevant query/queries.

This all works well as long as all connections are going to the same MySQL backend. However, this might not always be the case:

• If you point vttablet at an Aurora read-only endpoint, and you have multiple reader instances, you may have db connections going to any of the backend databases, since the DNS -> IP resolution for the connections are made at the time we Connect() to the database, not when vttablet starts, or when the pool gets created.
• As a result, your dbaPool may well not be connected to the same backend as the running query you are trying to kill. So the query killing for deadlines and on vttablet shutdown may not be effective.
• Worse, since vttablet just blindly issues a KILL with the thread id; you may accidentally kill a thread with the same ID on a different instance that does not belong to vttablet or is doing something else on behalf of vttablet.

Fixing this isn't trivial. Some possible options:

• We could resolve the IP address for a DNS name at pool creation time to ensure the same address are used for all connections in that pool, including the dbaPool. Two issues with this would be:
  • dealing with TLS verification issues, since we're not using the server DNS name
  • connection spread across backend instances would not be as even as before
• When killing a connection thread, dig out the actual host info for the connection, connect to that instance, and perform the kill. Issues:
  • Would need to make a new connection for each kill. This is going to be slow(er) for large pools.
  • TLS verification issues would still be a problem

[aquarapid]

To solve this generally is high effort (we'd have to track each backend instance individually); as a workaround, the following has been suggested:

• Disable the Vitess query timeouts (-queryserver-config-query-timeout and -queryserver-config-transaction-timeout); and control the timeouts via alternate MySQL-controlled directives (max_execution_time` or the equivalent MySQL query comment directive). This handles the case in which Vitess kills queries during normal operation.
• To handle the case during shutdown, where Vitess can kill running queries, increase the vttablet -shutdown_grace_period to be larger than the timeouts you are setting on queries via MySQL means above. This will ensure that on shutdown, Vitess will wait long enough for these queries to time out and terminate, and will not need to actively try to kill queries.

This is not ideal, but should limit the potential negative consequences of this issue.