A client connection can use a backend connection with a session variable set by a different client

[renecannao]

A clear description of the issue

Up to ProxySQL 2.0.12 a series of global variables exist that define the default for session variables: if the client doesn't specify any value for a given variable, ProxySQL will set the default session variable value to the backend connection.
This approach was incorrect, because a client shouldn't have assigned a variable that didn't explicitly ask for.
This implementation was changed in 2.0.13 , and variables are set in backend connections only if the client explicitly sets it.
The logic was: if the client didn't explicitly set variable it means it doesn't care, therefore proxysql will leave in the backend connection whatever session variable value set by a different client.
In reality this algorithm is not perfect, because it is possible that a client expects a connection to have certain variable value, even if it didn't set it explicitly.

ProxySQL version

2.0.13

OS version

Any

The steps to reproduce the issue

• Connect to proxysql, and change a variable, for example sql_mode:

$ mysql -u sbtest -psbtest -h 127.0.0.1 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'sql_mode';
+---------------+------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                        |
+---------------+------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+---------------+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select 1;
+---+  
| 1 |  
+---+  
| 1 |  
+---+  
1 row in set (0.00 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected (0.00 sec)

mysql> select 1;
+---+  
| 1 |  
+---+  
| 1 |  
+---+  
1 row in set (0.00 sec)

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> \q
Bye

• Reconnect, and verify the value of sql_mode (it should be the same value set by the previous connection):

$ mysql -u sbtest -psbtest -h 127.0.0.1 -P6033
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 (ProxySQL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

The full ProxySQL error log

Not relevant

[yakirgb]

Tested on our env and it's works like a charm.

[renecannao]

This needs to be ported to 2.1.0