Read/Write Split query rules do not work while using sysbench to evaluate perfromance

[Plissmile]

Issue：Read/Write Split query rules do not work while using sysbench to evaluate perfromance

ProxySQL version: 2.1.0-544-g17a4b4a7, codename Trul

OS version: Linux version 4.19.0-12-amd64 ([email protected]) (gcc version 8.3.0 (Debian 8.3.0-6)) #1 SMP Debian 4.19.152-1 (2020-10-18)

Sysbench version: sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

The steps to reproduce the issue：

1. proxysql setting

insert into mysql_users(username,password,default_hostgroup,transaction_persistent) values('mgr','mgr',1,1);
insert into mysql_group_replication_hostgroups(writer_hostgroup,backup_writer_hostgroup,reader_hostgroup,offline_hostgroup,active,max_writers,writer_is_also_reader,max_transactions_behind,comment) values(1,2,3,4,1,1,0,100,'mgr-test');

mysql> select * from runtime_mysql_group_replication_hostgroups\G
*************************** 1. row ***************************
       writer_hostgroup: 1
backup_writer_hostgroup: 2
       reader_hostgroup: 3
      offline_hostgroup: 4
                 active: 1
            max_writers: 1
  writer_is_also_reader: 0
max_transactions_behind: 100
                comment: mgr-test
1 row in set (0.01 sec)

mysql> select hostgroup_id,status from runtime_mysql_servers;
+--------------+--------+
| hostgroup_id | status |
+--------------+--------+
| 1            | ONLINE |
| 3            | ONLINE |
| 3            | ONLINE |
+--------------+--------+
3 rows in set (0.00 sec)

-- Forward the request to the writing group according to the comment keyword
INSERT INTO mysql_query_rules (rule_id,active,username,match_pattern,destination_hostgroup,apply) VALUES (100,1,'mgr','\s*\/\*\s*towrite\s*\*.*',1,1);
-- Forward the write request to the write group
INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (200,1,'mgr','^SELECT.*FOR UPDATE$',1,1);
-- Forward the read request to the read group
INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (201,1,'mgr','^SELECT',3,1);

mysql> select rule_id,active,username,match_digest,match_pattern,destination_hostgroup from mysql_query_rules;
+---------+--------+----------+----------------------+--------------------------+-----------------------+
| rule_id | active | username | match_digest         | match_pattern            | destination_hostgroup |
+---------+--------+----------+----------------------+--------------------------+-----------------------+
| 100     | 1      | mgr      | NULL                 | \s*\/\*\s*towrite\s*\*.* | 1                     |
| 200     | 1      | mgr      | ^SELECT.*FOR UPDATE$ | NULL                     | 1                     |
| 201     | 1      | mgr      | ^SELECT              | NULL                     | 3                     |
+---------+--------+----------+----------------------+--------------------------+-----------------------+
3 rows in set (0.00 sec)

2. sysbench: read only test

sysbench  --threads=1 /usr/share/sysbench/oltp_read_only.lua --tables=1 --table-size=1000 --report-interval=1 --rand-type=pareto --forced-shutdown=1 --max-requests=1 --events=0 --percentile=95  --mysql-user=mgr --mysql-password=mgr --mysql-db=dbtest --mysql-storage-engine=INNODB --mysql-host=xxxxx --mysql-port=6033 --skip_trx=on run

3. result

————————————sysbench stats infomation————————————
...
SQL statistics:
    queries performed:
        read:                            14
        write:                           0
        other:                           0
        total:                           14
    transactions:                        1      (21.60 per sec.)
    queries:                             14     (302.40 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
...

————————————proxysql stats infomation————————————
mysql>  select * from stats_mysql_query_rules;
+---------+------+
| rule_id | hits |
+---------+------+
| 100     | 0    |
| 200     | 14   |
| 201     | 5    |
+---------+------+
3 rows in set (0.01 sec)

4. logs
   mysql general log on writer_hostgroup
   
   There is no related general log on reader_hostgroup and no related error in /var/lib/proxysql/proxysql.log.

Strangely, when I use shell command for e in {01..10};do echo "SELECT c FROM sbtest1 WHERE id=1" |mysql -hxxxx -uxxx -pxxx -P 6033 dbtest;done, the rules can work, read requests were forwarded to the read group. However while using sysbench, read requests were forwarded to both the read group and the write group.

Could you help me to find out why rules do not work? Please let me know if there is any information missing from this issue.