Not to ignore some session variables

[Takashi-kun]

description of the issue

I can't set several session variables written following

• https://github.com/sysown/proxysql/blob/v2.x/lib/MySQL_Variables.cpp#L28-L36
• https://github.com/sysown/proxysql/blob/v2.x/lib/MySQL_Thread.cpp#L2929-L2934

I guess ProxySQL doesn't support setting those parameters in backend connection is for multiprexing function.

How about to enable to set those parameters when we disable connection multiplexing ?
Or is there any reason not be able to set those parameters to the backend connection?

ProxySQL version

2.5.5

OS version

Docker image proxysql/proxysql:2.5.5

The steps to reproduce the issue

### MySQL directly
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 5     |
+---------------+-------+
1 row in set (0.01 sec)

mysql> set session wait_timeout=100;
Query OK, 0 rows affected (0.00 sec)

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

### via ProxySQL 
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 5     |
+---------------+-------+
1 row in set (0.03 sec)

mysql> set session wait_timeout=100;
Query OK, 0 rows affected (0.01 sec)

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

configurations

• docker-compose.yml

version: "3"

services:
  mysql-primary:
    image: mysql:5.7
    container_name: proxysql-mysql-primary
    platform: linux/amd64
    environment:
      MYSQL_ROOT_PASSWORD: password
      MYSQL_DATABASE: test
    volumes:
      - ./primary/my.cnf:/etc/mysql/my.cnf
      - ./primary/data:/var/lib/mysql
      - ./primary/init.sql:/docker-entrypoint-initdb.d/init.sql
    ports:
      - 3306:3306
  proxysql:
    image: proxysql/proxysql:2.5.5
    container_name: proxysql
    command: proxysql -c /etc/proxysql.cnf -f --idle-threads -D /var/lib/proxysql
    platform: linux/amd64
    volumes:
      - ./proxysql/proxysql.cnf:/etc/proxysql.cnf
      - ./proxysql/data:/var/lib/proxysql
    ports:
      - 6032:6032 # admin
      - 6033:6033 # application
    depends_on:
      - mysql-primary

• proxysql.cnf

datadir="/var/lib/proxysql"
errorlog="/var/lib/proxysql/proxysql.log"
admin_variables=
{
    admin_credentials="admin:admin;radmin:radmin"
    mysql_ifaces="0.0.0.0:6032;/tmp/proxysql_admin.sock"
    debug=true
}

mysql_variables=
{
    threads                    = 4
    max_connections            = 8192
    default_query_delay        = 0
    have_compress              = true
    poll_timeout               = 2000
    interfaces                 = "0.0.0.0:6033"
    stacksize                  = 1048576
    server_version             = "5.7.34"
    connect_timeout_server     = 3000

    monitor_enabled            = false
    commands_stats             = true
    sessions_sort              = true
    connect_retries_on_failure = 10
    wait_timeout               = 28801000 # msec
    default_query_timeout      = 36000000 # msec
    max_transaction_idle_time  = 29400000 # msec
    max_transaction_time       = 86400000 # msec
    max_stmts_per_connection   = 100
    max_allowed_packet         = 16777216
    free_connections_pct       = 0

    monitor_local_dns_cache_ttl              = 5000 #msec
    monitor_local_dns_cache_refresh_interval = 3000 #msec

    ssl_p2s_ca                 = ""
    multiplexing               = false
    log_unhealthy_connections  = false
}

 mysql_servers =
 (
 	{
 		address="mysql-primary"
 		port=3306
 		hostgroup=0
 		max_connections=200
 	},
 )

mysql_users:
 (
 	{
 		username = "root"
 		password = "password"
 		default_hostgroup = 0
 		max_connections=1000
 		default_schema="test"
 		active = 1
 	},
 )

• my.cnf

[client]
default-character-set = utf8mb4

[mysqld]
log-bin
server-id=1000
gtid-mode=ON
enforce-gtid-consistency=ON
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci
innodb-use-native-aio = 0
wait_timeout = 5

[renecannao]

Hi @Takashi-kun .
Thank you for your report.
wait_timeout is a session variable intentionally ignored.

Setting wait_timeout for cases in which multiplexing is disabled may indeed make sense, it is something we should consider implementing.

But I have a further question: why do you want to disable multiplexing? It kills many of proxysql features

[Takashi-kun]

@renecannao
Thanks for your quick response!

Why do you want to disable multiplexing?

There are some reasons:

• Our MySQL server is connected from multiple clients and each clients have different characteristic. So the connection formed frontend:backend=1:1 would be better than reusing backend connection by multiplexing
• Our MySQL server's wait_timeout is configured as short time and clients extend that value as they need. Under this situation, it's difficult to achieve the power of multiplexing function

[renecannao]

Can you please elaborate on point 1?
What are these characteristics? ProxySQL should try to track them if possible

[Takashi-kun]

Thanks for your response.

To say short...

• Applicatoins without connection pooling(like PHP application)
• Applications using connection pooling(like Golang application)
• Requires long transaction like batch

Those applications request to same MySQL server and the largest number of requests are without connection pooling one.
So, it's easier to investigate and manage resources frontend:backend=1:1 than using multiplexing(Of course, I should accept the overhead of handshake between ProxySQL and backend MySQL)