[Feature] COM_RESET_CONNECTION for resetting connection

[leenoix]

Currently, it looks like proxysql is using COM_CHANGE_USER for resetting the connection.
However, the backend we are using (vitess) is not supporting COM_CHANGE_USER, but it only has support for COM_RESET_CONNECTION.
Furthermore, AFAIK, COM_RESET_CONNECTION is light weight version when resetting the connection, so I think it would be very nice if we can control wether to send COM_CHANGE_USER or COM_RESET_CONNECTION.

Is there any plan/timeline for this feature? If not, could you please consider having the feature :)

Thank you.

[renecannao]

Hi @leenoix !
There is some history behind this.
COM_RESET_CONNECTION was introduced in MySQL 5.7 : so we opted to always use COM_CHANGE_USER to be as backward compatible as possible.
That is, our idea was to use COM_CHANGE_USER because it is always available on the backend, no matter the version of MySQL.
To know that Vitess supports COM_RESET_CONNECTION but doesn't support COM_CHANGE_USER completely flips the problem upside down.

To make things even more interesting, ProxySQL supports COM_CHANGE_USER but doesn't support COM_RESET_CONNECTION from clients. Although, we have this already implemented (thanks @JavierJF) and we plan to maake it available in release v2.4.0 .

Is there any plan/timeline for this feature? If not, could you please consider having the feature :)

We currently do not have any plan/timeline for this feature, but we can surely schedule it.

Meanwhile, if the use of COM_CHANGE_USER is causing you problem, I would recommend setting mysql-connpoll_reset_queue_length=0: proxysql shouldn't try to reset connection, but rather drop them and create new ones.

[leenoix]

@renecannao
Thank you for your super quick response :)
We will use connpoll_reset_queue_length=0
Thank you for considering scheduling the feature 👍 Looking forward to it!

[leenoix]

By the way, it seems like setting this variable to 0 almost always doesn't cause COM_CHANGE_USER, but sometimes we are facing some COM_CHANGE_USER related errors in proxysql logs even with the value set to 0. (We are sure that value is 0 since we have used this value for months without modifying )

2021-10-09 18:25:19 MySQL_Session.cpp:1533:handler_again___status_RESETTING_CONNECTION(): [ERROR] Detected an error during COM_CHANGE_USER on (0,<ourendpoint>,3306) , FD (Conn:41 , MyDS:41) : 1047, command handling not implemented yet: 17

I think resetting connection is being done on particular circumstances.
Here is what we have found: The number of logs that look like 2021-10-09 18:27:54 MySQL_Thread.cpp:4439:process_all_sessions(): [WARNING] Closing unhealthy client connection localhost:0 is almost the same as the number of above error. (Not sure if they are related)

@renecannao Is there anything we can do to make proxysql not to reset connection at all?

(proxysql version we are using is 2.0.16)

[sf-jin-ku]

@renecannao we're also that even with "connpoll_reset_queue_length=0", COM_CHANGE_USER is happening in 2.0.16. any situation you can guess? maybe upgrading to the latest version is suggested?