Could not able to add new query_rules and all traffic went to single writer

ajay · February 2, 2022, 5:07am

Hi @Slava_Sarzhan and team, Greetings, I had successfully deployed the pxc-operator and pxc-db with proxysql using helm in k8s. And did do the sysbench test using oltp_read_write.lua script observed that all the reads & writes went to only one writer.
ProxySQLAdmin> select hostgroup,ConnUsed, MaxConnUsed, Queries from stats_mysql_connection_pool;
±----------±---------±------------±--------+
| hostgroup | ConnUsed | MaxConnUsed | Queries |
±----------±---------±------------±--------+
| 11 | 0 | 7 | 109148 |
| 11 | 0 | 0 | 0 |
| 11 | 0 | 0 | 0 |
| 10 | 0 | 1 | 1 |
| 10 | 0 | 0 | 0 |
| 10 | 0 | 0 | 0 |
| 12 | 0 | 0 | 0 |
| 12 | 0 | 0 | 0 |
±----------±---------±------------±--------+
and then i tried re-route my top 2 queries to hostgroup 10 which is reader and tried to add query rules for that as below but could not see any get added.

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES(13,1,‘mssandbox’,‘^SELECT c FROM sysbenchdb WHERE id=?$’,10,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES(14,1,‘mssandbox’,‘DISTINCT c FROM sysbenchdb’,10,1);
Query OK, 1 row affected (0.00 sec)
ProxySQLAdmin> LOAD MYSQL QUERY RULES TO RUNTIME;
ProxySQLAdmin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username=‘mssandbox’ ORDER BY rule_id;
Empty set (0.00 sec)

Please help or suggest on how to divert the traffic to various and also how to add new query rules.
i went through [K8SPXC-735] ProxySQL uses only one server for reads - Percona JIRA is it still ok to use percona xtradbcluster with proxysql in production based on that issue?

Thank you so much,
Ajay

mughees52 · February 2, 2022, 6:45am

Hello Mind Posting below from ProxSQL:

select * from runtime_mysql_servers order by 1,5;
select username,active,default_hostgroup,max_connections from mysql_users;
select * from runtime_mysql_galera_hostgroups;

ajay · February 2, 2022, 12:22pm

Hi @mughees52, posted the responses below.

ProxySQLAdmin> select * from runtime_mysql_servers order by 1,5;
±-------------±----------------------------------------------±-----±----------±--------±--------±------------±----------------±--------------------±--------±---------------±--------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
±-------------±----------------------------------------------±-----±----------±--------±--------±------------±----------------±--------------------±--------±---------------±--------+
| 10 | pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | SHUNNED | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | SHUNNED | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 12 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 12 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
±-------------±----------------------------------------------±-----±----------±--------±--------±------------±----------------±--------------------±--------±---------------±--------+
8 rows in set (0.00 sec)

ProxySQLAdmin> select username,active,default_hostgroup,max_connections from mysql_users;
±-------------±-------±------------------±----------------+
| username | active | default_hostgroup | max_connections |
±-------------±-------±------------------±----------------+
| clustercheck | 1 | 11 | 10000 |
| monitor | 1 | 11 | 10000 |
| operator | 1 | 11 | 10000 |
| replication | 1 | 11 | 10000 |
| root | 1 | 11 | 10000 |
| xtrabackup | 1 | 11 | 10000 |
| mssandbox | 1 | 11 | 10000 |
| clustercheck | 1 | 11 | 10000 |
| monitor | 1 | 11 | 10000 |
| operator | 1 | 11 | 10000 |
| replication | 1 | 11 | 10000 |
| root | 1 | 11 | 10000 |
| xtrabackup | 1 | 11 | 10000 |
| mssandbox | 1 | 11 | 10000 |
±-------------±-------±------------------±----------------+
14 rows in set (0.00 sec)

ProxySQLAdmin> select * from runtime_mysql_galera_hostgroups;
±-----------------±------------------------±-----------------±------------------±-------±------------±----------------------±------------------------±--------+
| writer_hostgroup | backup_writer_hostgroup | reader_hostgroup | offline_hostgroup | active | max_writers | writer_is_also_reader | max_transactions_behind | comment |
±-----------------±------------------------±-----------------±------------------±-------±------------±----------------------±------------------------±--------+
| 11 | 12 | 10 | 13 | 1 | 1 | 1 | 100 | NULL |
±-----------------±------------------------±-----------------±------------------±-------±------------±----------------------±------------------------±--------+
1 row in set (0.00 sec)

ajay · February 2, 2022, 1:03pm

Hi @mughees52, i have added the user to the hg 10 but it was moved to 11 after running the tests.
INSERT INTO mysql_users (username,password,default_hostgroup,active) VALUES(‘msandbox’,‘msandbox’,10,1);

ProxySQLAdmin> select username,active,default_hostgroup,max_connections from mysql_users;
±-------------±------------------------------------------±-------±--------±------------------±---------------±--------------±-----------------------±-------------±--------±---------±----------------±--------+
| username | active | default_hostgroup | max_connections |
±-------------±------------------------------------------±-------±--------±------------------±---------------±--------------±-----------------------±-------------±--------±---------±----------------±--------+
| msandbox | 1 | 10 | 10000

test:
sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-db=sysbenchdb --mysql-host=pxc-db-proxysql.pxc.svc.cluster.local --mysql-user=msandbox --mysql-password=msandbox --db-driver=mysql --threads=10 --tables=10 --skip-trx=true --table-size=1000 --time=50 --report-interval=10 --mysql-ignore-errors=1062 run

after test:

ProxySQLAdmin> select hostgroup,ConnUsed, MaxConnUsed, Queries from stats_mysql_connection_pool;
±----------±---------±------------±--------+
| hostgroup | ConnUsed | MaxConnUsed | Queries |
±----------±---------±------------±--------+
| 11 | 0 | 7 | 180832 |
| 11 | 0 | 0 | 0 |
| 11 | 0 | 0 | 0 |
| 10 | 0 | 1 | 1 |
| 10 | 0 | 0 | 0 |
| 10 | 0 | 0 | 0 |
| 12 | 0 | 0 | 0 |
| 12 | 0 | 0 | 0 |
±----------±---------±------------±--------+
8 rows in set (0.00 sec)

ProxySQLAdmin> select username,active,default_hostgroup,max_connections from mysql_users;
±-------------±-------±------------------±----------------+
| username | active | default_hostgroup | max_connections |
±-------------±-------±------------------±----------------+
| clustercheck | 1 | 11 | 10000 |
| monitor | 1 | 11 | 10000 |
| operator | 1 | 11 | 10000 |
| replication | 1 | 11 | 10000 |
| root | 1 | 11 | 10000 |
| xtrabackup | 1 | 11 | 10000 |
| mssandbox | 1 | 11 | 10000 |
| msandbox | 1 | 11 | 10000 |
| clustercheck | 1 | 11 | 10000 |
| monitor | 1 | 11 | 10000 |
| operator | 1 | 11 | 10000 |
| replication | 1 | 11 | 10000 |
| root | 1 | 11 | 10000 |
| xtrabackup | 1 | 11 | 10000 |
| mssandbox | 1 | 11 | 10000 |
| msandbox | 1 | 11 | 10000 |
±-------------±-------±------------------±----------------+
16 rows in set (0.00 sec)

mughees52 · February 2, 2022, 4:10pm

make the weight in mysql_servers as same for all nodes . so proxysql can load balance equally

I see pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local weight is 1000000

ajay · February 2, 2022, 4:20pm

Hi @mughees52 , which is not come by default? do we have to add manually every-time it changes?

matthewb · February 2, 2022, 5:44pm

Check your query rules again. Run the two INSERTs, then SELECT from mysql_query_rules. Verify they are present. Then SAVE MYSQL QUERY RULES TO DISK; LOAD MYSQL QUERY RULES TO RUNTIME;

Also, make sure they are proper regex. Keep them simple. “^SELECT” alone is fine.

ajay · February 2, 2022, 6:08pm

hey @matthewb, replicated the same steps as you mentioned. Please see below outputs for queries.
ProxySQLAdmin> select rule_id,active,username,match_digest,destination_hostgroup from mysql_query_rules;
±--------±-------±-------------±--------------------±----------------------+
| rule_id | active | username | match_digest | destination_hostgroup |
±--------±-------±-------------±--------------------±----------------------+
| 1 | 1 | clustercheck | ^SELECT.*FOR UPDATE | 11 |
| 2 | 1 | clustercheck | ^SELECT | 10 |
| 3 | 1 | monitor | ^SELECT.*FOR UPDATE | 11 |
| 4 | 1 | monitor | ^SELECT | 10 |
| 5 | 1 | operator | ^SELECT.*FOR UPDATE | 11 |
| 6 | 1 | operator | ^SELECT | 10 |
| 7 | 1 | replication | ^SELECT.*FOR UPDATE | 11 |
| 8 | 1 | replication | ^SELECT | 10 |
| 9 | 1 | root | ^SELECT.*FOR UPDATE | 11 |
| 10 | 1 | root | ^SELECT | 10 |
| 11 | 1 | xtrabackup | ^SELECT.*FOR UPDATE | 11 |
| 12 | 1 | xtrabackup | ^SELECT | 10 |
±--------±-------±-------------±--------------------±----------------------+
12 rows in set (0.00 sec)
ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (13,1,‘mssandbox’,‘^SELECT.*FOR UPDATE’,11,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (14,1,‘mssandbox’,‘^SELECT’,10,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> load mysql users to runtime;
Query OK, 0 rows affected (0.01 sec)

ProxySQLAdmin> save mysql users to disk;
Query OK, 0 rows affected (0.03 sec)

ProxySQLAdmin> select rule_id,active,username,match_digest,destination_hostgroup from mysql_query_rules;±--------±-------±-------------±--------------------±----------------------+
| rule_id | active | username | match_digest | destination_hostgroup |
±--------±-------±-------------±--------------------±----------------------+
| 1 | 1 | clustercheck | ^SELECT.*FOR UPDATE | 11 |
| 2 | 1 | clustercheck | ^SELECT | 10 |
| 3 | 1 | monitor | ^SELECT.*FOR UPDATE | 11 |
| 4 | 1 | monitor | ^SELECT | 10 |
| 5 | 1 | operator | ^SELECT.*FOR UPDATE | 11 |
| 6 | 1 | operator | ^SELECT | 10 |
| 7 | 1 | replication | ^SELECT.*FOR UPDATE | 11 |
| 8 | 1 | replication | ^SELECT | 10 |
| 9 | 1 | root | ^SELECT.*FOR UPDATE | 11 |
| 10 | 1 | root | ^SELECT | 10 |
| 11 | 1 | xtrabackup | ^SELECT.*FOR UPDATE | 11 |
| 12 | 1 | xtrabackup | ^SELECT | 10 |
| 14 | 1 | mssandbox | ^SELECT | 10 |
±--------±-------±-------------±--------------------±----------------------+
13 rows in set (0.00 sec)

rule_id number 13 was not added. but 14 got added. i will run the sysbench read and read_write scripts and will post the results here soon.

Thank you,

matthewb · February 2, 2022, 6:24pm

Why did you load/save users when we are dealing with rules? Insert rule 13, select to verify, save to disk, load to run. Insert 14, select to verify, save, load.

ajay · February 2, 2022, 7:10pm

Sorry my bad. did the right ones now. but still same. i did change weight of mysql servers but after a very few seconds it was moved back to original.

ProxySQLAdmin> update mysql_servers set weight=1;

Query OK, 8 rows affected (0.00 sec)

ProxySQLAdmin> load mysql servers to runtime;

Query OK, 0 rows affected (0.01 sec)

ProxySQLAdmin> save mysql servers to disk;

ProxySQLAdmin> select * from mysql_servers;
±-------------±----------------------------------------------±-----±----------±-------±-------±------------±----------------±--------------------±--------±---------------±--------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
±-------------±----------------------------------------------±-----±----------±-------±-------±------------±----------------±--------------------±--------±---------------±--------+
| 11 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 12 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 12 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | |
±-------------±----------------------------------------------±-----±----------±-------±-------±------------±----------------±--------------------±--------±---------------±--------+
8 rows in set (0.00 sec)

after a moment it went back original:

ProxySQLAdmin> select * from mysql_servers;
±-------------±----------------------------------------------±-----±----------±-------±--------±------------±----------------±--------------------±--------±---------------±--------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
±-------------±----------------------------------------------±-----±----------±-------±--------±------------±----------------±--------------------±--------±---------------±--------+
| 11 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 12 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 12 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 10 | pxc-db-pxc-2.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-0.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | |
| 11 | pxc-db-pxc-1.pxc-db-pxc.pxc.svc.cluster.local | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | |
±-------------±----------------------------------------------±-----±----------±-------±--------±------------±----------------±--------------------±--------±---------------±--------+
8 rows in set (0.00 sec)

and all the load again went to the one with high wighted writer. Could you please advise me to make the load distributed when using application-level users (not system-level users eg:root). (note: if i use the root user for test (sysbench read and write) then i could see the load is splitting between writer and readers)

Thank you,
Ajay

ajay · February 4, 2022, 1:22pm

@matthewb i could able to add query rules but it is not just after one attempt of (insert,verify,save and load) for one particular query, i had to repeat insert, verify, save and load more than 7 times at-least in order to stick one query into the table. is there any solution to make them stick into table after one attempt? please suggest. Thank you.

Michael_Coburn · February 4, 2022, 4:33pm

Hi @ajay

What version of ProxySQL are you on? There is an open bug related to 2.3.2 where read/write splitting is not working as expected - perhaps this applies to you as well

github.com/sysown/proxysql

Rules splitting does not work after upgrade

opened 05:29PM - 19 Jan 22 UTC

alessandropomes

Hello! We are facing an issue while upgrading proxysql version from 2.0.12 to… 2.3.2 . These version are placed on a docker container in our ec2 instance of Amazon Linux 2. The actual configuration file that is working properly on version 2.0.12 is the following: > `datadir="/var/lib/proxysql" > > admin_variables= > { > admin_credentials="xxxxxxxxx" > mysql_ifaces="0.0.0.0:6032" > web_enabled=true > } > > mysql_variables= > { > threads=4 > max_connections=3096 > default_query_delay=0 > default_query_timeout=36000000 > have_compress=true > poll_timeout=2000 > interfaces="0.0.0.0:6033" > default_schema="information_schema" > stacksize=1048576 > server_version="5.5.30" > connect_timeout_server=3000 > monitor_username="proxysql_monitor" > monitor_password="XXXXXXXX" > monitor_history=600000 > monitor_connect_interval=100 > monitor_ping_interval=100 > monitor_read_only_interval=100 > monitor_read_only_timeout=500 > autocommit_false_is_transaction=false > ssl_p2s_cipher="ECDHE-RSA-AES256-SHA" > ssl_p2s_ca="/opt/rds-ca-2019-eu-west-1.pem" > ping_interval_server_msec=120000 > ping_timeout_server=500 > commands_stats=true > sessions_sort=true > connect_retries_on_failure=10 > have_ssl=true > } > > > mysql_servers = > ( > { > address = "xxxxxxxx-db-01" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain > port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain > hostgroup = 10 # no default, required > status = "ONLINE" # default: ONLINE > weight = 1 # default: 1 > compression = 0 # default: 0 > max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned > use_ssl = 1 > }, > { > address = "xxxxxxxx-db-01-rr" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain > port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain > hostgroup = 20 # no default, required > status = "ONLINE" # default: ONLINE > weight = 1000 # default: 1 > compression = 0 # default: 0 > max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned > use_ssl = 1 > }, > { > address = "xxxxxxxx-db-01" # no default, required . If port is 0 , address is interpred as a Unix Socket Domain > port = 3306 # no default, required . If port is 0 , address is interpred as a Unix Socket Domain > hostgroup = 20 # no default, required > status = "ONLINE" # default: ONLINE > weight = 1 # default: 1 > compression = 0 # default: 0 > max_replication_lag = 10 # default 0 . If greater than 0 and replication lag passes such threshold, the server is shunned > use_ssl = 1 > } > > > mysql_users: > ( > { > username = "XXX" # no default , required > password = "XX" # default: '' > default_hostgroup = 10 # default: 0 > active = 1 # default: 1 > }, > { > username = "XXX" # no default , required > password = "XX" # default: '' > default_hostgroup = 10 # default: 0 > active = 1 # default: 1 > }, > { > username = "XX" # no default , required > password = "XX" # default: '' > default_hostgroup = 10 # default: 0 > active = 1 # default: 1 > } > ) > > > mysql_query_rules: > ( > { > rule_id=1 > active=1 > digest="0x06409F41D5CACDB8" > destination_hostgroup=20 > log=1 > apply=1 > }, > { > rule_id=3 > active=1 > digest="0x24252F7C609CDD5C" > destination_hostgroup=20 > log=1 > apply=1 > }, > { > rule_id=4 > active=1 > digest="0x2B9737719C248E60" > destination_hostgroup=20 > log=1 > apply=1 > }, > { > rule_id=6 > active=1 > digest="0x35827B807F4D38F3"` > ..... And a list of 164 rules..from rule id 1 to 164. Basically we have 2 hostgroup : 10 and 20. 10 is our master db(xxxxxxxx-db-01) and 20(xxxxxxxx-db-01-rr) is our read replica. The issue we are facing is that once we move the image from 2.0 to 2.3.2 we have evidence that the rules splitting does not work anymore. We have started a test in which we can reproduce the exact queries. Once we change version and launch the test, the number of queries splitted on read replica decrease, while we see increasing number of queries on master.(booth in proxysql querie stats and db metrics) We have ensured that: 1-Connection with backend is working fine. 2-Splitting is actually working for different type of queries (We select the most used ones and we see also the query delivered on read replica) So do you have any idea on what is causing this behavior? I also upload the two different type of global variable we set on the different version: in 2.3.2: [global2.3.2.txt](https://github.com/sysown/proxysql/files/7899239/global2.3.2.txt) in 2.0.12. [global2.0.12.txt](https://github.com/sysown/proxysql/files/7899232/global2.0.12.txt) Please if you need more details on the problem please ask me it. Thank you in advance for the consideration.

ajay · February 4, 2022, 5:23pm

HI @Michael_Coburn,
bash-4.4$ proxysql --version
ProxySQL version 2.0.18-percona-1.1, codename Truls.

Sorry, i might confused you and team here, because of mixing up issues above regarding weight and query rules.
Regarding query rules: As per @renecannao comments on hostgroups, query rules and temporary tables · Issue #941 · sysown/proxysql · GitHub, by default “ProxySQL is configured to send all the traffic to the master” and that is what happened in my case too. (which is answered my question in the 2nd part of this issue’s title statement).
And here i need to add query rules to split the traffic, if there is a query rule present in the mysql_query_table then queries are splitting between my read and write hostgroups regardless of user(whether system-level/unprivileged).
So, my current question on query rules here is :i could able to add query rules through proxysql admin interface but it is not just after one attempt of (insert,verify,save and load) for one particular query, i had
to repeat insert, verify, save and load more than 7 times at-least in order to stick one query into the
table. is there any solution to make them stick into table after single attempt of (insert,verify,save and
load)?

ajay · February 4, 2022, 8:52pm

below is the reference for my problem for which i asked a question in my previous/above comment:

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (13,1,‘msd’,‘^SELECT c FROM sbtest3 WHERE id=?$’,10,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username=‘msd’ ORDER BY rule_id;
Empty set (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (13,1,‘msd’,‘^SELECT c FROM sbtest3 WHERE id=?$’,10,1);
Query OK, 1 row affected (0.00 sec)

ProxySQLAdmin> INSERT INTO mysql_query_rules (rule_id,active,username,match_digest,destination_hostgroup,apply) VALUES (13,1,‘msd’,‘^SELECT c FROM sbtest3 WHERE id=?$’,10,1);
ERROR 1045 (28000): ProxySQL Admin Error: UNIQUE constraint failed: mysql_query_rules.rule_id
ProxySQLAdmin> SELECT match_digest,destination_hostgroup FROM mysql_query_rules WHERE active=1 AND username=‘msd’ ORDER BY rule_id;
±------------------------------------±----------------------+
| match_digest | destination_hostgroup |
±------------------------------------±----------------------+
| ^SELECT c FROM sbtest3 WHERE id=?$ | 10 |
±------------------------------------±----------------------+
1 row in set (0.00 sec)

ProxySQLAdmin> save mysql query rules to disk;
Query OK, 0 rows affected (0.02 sec)

ProxySQLAdmin> load mysql query rules to runtime;
Query OK, 0 rows affected (0.00 sec)