MySQL_Session::kill_query_thread not using SSL when use_ssl in true for a MySQL server

[mcrauwel]

We are seeing these errors in our ProxySQL error log

2023-05-12 14:47:12 MySQL_Session.cpp:237:kill_query_thread(): [WARNING] KILL CONNECTION 3237346336 on ****.connect.psdb.cloud:3306
2023-05-12 14:47:12 MySQL_Session.cpp:260:kill_query_thread(): [ERROR] Failed to connect to server ****.connect.psdb.cloud:3306 to run KILL CONNECTION 3237346336: Error: unknown error: Code: UNAVAILABLE
server does not allow insecure connections, client must use SSL/TLS

looking both here and here ProxySQL is not trying to configure SSL.

We saw this in relation with the following error, which was blocking our testing.

MySQL_Session.cpp:3058:handler_again___status_CHANGING_USER_SERVER(): [WARNING] Error during change user: 1047, command handling not implemented yet: 17

ProxySQL version 2.5.1 running in Kubernetes.

[renecannao]

Hi @mcrauwel .
Thank you for the report.
I confirm the issue, and it is likely easy to fix.

About your testing, please note that command handling not implemented yet: 17 is an error coming from the backend, that doesn't support COM_CHANGE_USER . I assume it is not a MySQL server.
Furthermore, I think there is no correlation between command handling not implemented yet: 17 and kill_query_thread() : they seems to be unrelated.

[mcrauwel]

Hey @renecannao

Correct the backend is Vitess (PlanetScale). Apparently the implementation of the mysql_change_user() api is not complete. Will open an issue on the Vitess side.

Intermediate solution is to create a hostgroup/connection pool per user.

Additionally, if ProxySQL detects an error and kill_query_thread() is failing, should ProxySQL try to hard kill the thread/connection? Just a consideration?

Thanks

[asychev]

Related: #3656

[renecannao]

@mcrauwel

Additionally, if ProxySQL detects an error and kill_query_thread() is failing, should ProxySQL try to hard kill the thread/connection? Just a consideration?

It is not "additionally" , it is the default behavior. KILL CONNECTION is "additional". Let me explain.
When ProxySQL wants to destroy a connection it simply disconnects.
But it is possible that the backend server doesn't detect this immediately, and therefore the connection still exists for some time. If the connection is idle the backend normally detects this immediately, but if the connection was running an expensive DML the backend may not detect this immediately (because too busy running the DML and not paying attention to the connection being terminated), thus the DML continues running.
Thus , if mysql-kill_backend_connection_when_disconnect is enabled, ProxySQL disconnects the backend connection AND it also tries to kill it.

@asychev : I don't know if COM_RESET_CONNECTION is relevant here.
From the previous comment of @mcrauwel there are multiple users.

[mcrauwel]

Thanks for the clarification!