Track session-specific system variables using `session_track_system_variables`

[wazir-ahmed]

Problem

Currently, ProxySQL tracks changes to system variables by parsing SET statement issued by the client. This approach has some downsides, such as:

• Being unable to track SET statements executed as part of procedures.
• Being unable to capture the value assigned to variables when they are passed as a variable (SET var_name=@variable)

Solution

To overcome these issues with the current implementation, we can make use of the session state tracking feature in MySQL.

SET session_track_system_variables='*'
SET session_track_state_change=ON

By configuring these variables in all backend connections, ProxySQL will be notified by the backend servers directly when there is a change in system variables. ProxySQL can then extract the values from the notifications and override the existing values in the session variable maps it maintains for both client and backend connections.