Proxysql: query rules don't match correctly in 2.1.0

Created on 27 Mar 2021 · 10Comments · Source: sysown/proxysql

ProxySQL 2.1.0 and 2.0.17 docker images amd64

select queries mostly always go to the write hostgroup.
I have tried with transaction_persistent set true and false, but it made no difference.

mysql_query_rules

| rule_id | active | username | schemaname | flagIN | match_pattern       | re_modifiers | destination_hostgroup | apply |
+---------+--------+----------+------------+--------+---------------------+--------------+-----------------------+-------+
| 100     | 1      | NULL     | NULL       | 0      | ^SELECT.*FOR UPDATE | CASELESS     | 1                     | 1     |
| 200     | 1      | NULL     | NULL       | 0      | ^SELECT             | CASELESS     | 2                     | 1     |
| 300     | 1      | NULL     | NULL       | 0      | .*                  | CASELESS     | 1                     | 1     |
+---------+--------+----------+------------+--------+---------------------+--------------+-----------------------+-------+

Stats when using 2.0.17

| rule_id | hits |
+---------+------+
| 100     | 0    |
| 200     | 60   |
| 300     | 11   |
+---------+------+

| hostgroup | count_star | digest             | digest_text                                                                                                                                   |
+-----------+------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+
| 2         | 51         | 0x810442EFF7C161A0 | select ordername, name from records where ordername <= ? and domain_id=? and disabled=? and ordername is not null order by ? desc limit ?     |
| 2         | 83         | 0x9584489913F5DD21 | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=? and name=? and domain_id=?                            |
| 2         | 65         | 0x068FE0468093C225 | select ordername from records where ordername > ? and domain_id=? and disabled=? and ordername is not null order by ? asc limit ?             |
| 2         | 59         | 0x20BC05DA953525E5 | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=? and type=? and name=? and domain_id=?                 |
| 2         | 11         | 0x2670390F7DD322F3 | SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED                                                                                        |
| 2         | 39         | 0x6AABFCF148389B46 | select cryptokeys.id, flags, active, published, content from domains, cryptokeys where cryptokeys.domain_id=domains.id and name=?             |
| 2         | 99         | 0xE656E857E0B2C227 | select content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name=? and domainmetadata.kind=?                    |
| 2         | 10         | 0x290E9F09B89ACB74 | select d.id, d.name, d.notified_serial, r.content from records r join domains d on r.name=d.name where r.type=? and r.disabled=? and d.type=? |
| 2         | 97         | 0x8780470E377A2742 | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=? and type=? and name=?                                 |
+-----------+------------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------+



md5-f8ea38d7f3dec2e821e3746ae5921ac2



+---------+------+
| rule_id | hits |
+---------+------+
| 100     | 0    |
| 200     | 33   |
| 300     | 91   |
+---------+------+



md5-60146a1c666722b5c2ac14709b731fbf



+-----------+------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| hostgroup | count_star | digest             | digest_text                                                                                                                                |
+-----------+------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| 1         | 8          | 0x068FE0468093C225 | select ordername from records where ordername > ? and domain_id=? and disabled=? and ordername is not null order by ? asc limit ?          |
| 1         | 19         | 0x9584489913F5DD21 | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=? and name=? and domain_id=?                         |
| 1         | 15         | 0x20BC05DA953525E5 | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=? and type=? and name=? and domain_id=?              |
| 1         | 6          | 0xF3F9AF29299F7155 | select ordername,name from records where ordername <= ? and domain_id=? and disabled=? and ordername is not null order by ? desc limit ?   |
| 2         | 5          | 0x9584489913F5DD21 | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=? and name=? and domain_id=?                         |
| 2         | 6          | 0x8780470E377A2742 | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=? and type=? and name=?                              |
| 1         | 20         | 0x26388CBF0F7DDD8A | select content from domains,domainmetadata where domainmetadata.domain_id=domains.id and name=? and domainmetadata.kind=?                  |
| 2         | 4          | 0xF3F9AF29299F7155 | select ordername,name from records where ordername <= ? and domain_id=? and disabled=? and ordername is not null order by ? desc limit ?   |
| 1         | 2          | 0xDFD1504EB22976D9 | select d.id,d.name,d.notified_serial,r.content from records r join domains d on r.name=d.name where r.type=? and r.disabled=? and d.type=? |
| 2         | 1          | 0xDFD1504EB22976D9 | select d.id,d.name,d.notified_serial,r.content from records r join domains d on r.name=d.name where r.type=? and r.disabled=? and d.type=? |
| 2         | 4          | 0x068FE0468093C225 | select ordername from records where ordername > ? and domain_id=? and disabled=? and ordername is not null order by ? asc limit ?          |
| 1         | 8          | 0xDE74C4B5A38F7F3E | select cryptokeys.id,flags,active,published,content from domains,cryptokeys where cryptokeys.domain_id=domains.id and name=?               |
| 2         | 8          | 0x2670390F7DD322F3 | SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED                                                                                     |
| 1         | 22         | 0x8780470E377A2742 | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=? and type=? and name=?                              |
| 2         | 6          | 0x26388CBF0F7DDD8A | select content from domains,domainmetadata where domainmetadata.domain_id=domains.id and name=? and domainmetadata.kind=?                  |
| 2         | 6          | 0xDE74C4B5A38F7F3E | select cryptokeys.id,flags,active,published,content from domains,cryptokeys where cryptokeys.domain_id=domains.id and name=?               |
| 2         | 6          | 0x20BC05DA953525E5 | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=? and type=? and name=? and domain_id=?              |
+-----------+------------+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------+

Source

patrickdk77

Most helpful comment

Looks like 2.2.0 (resolve prepared queries) resolves this issue for me

patrickdk77 on 10 Jun 2021

👍2 🎉1

All 10 comments

I have the same issue.
There are a lot of select queries going to the writer no matter what I do, including turning transaction_persistent on and off.
I tried different patterns and match digest regexes.
The only solution it to match their exact digest and explicitly set them to go to the reader group. But since I have hundreds of different select queries, I cannot add so many rules

icoldo on 1 Apr 2021

@patrickdk77:

I don't see any evidence of your claim.
It doesn't seem you are running the same traffic on 2.0.17 and 2.1.0 : in fact, even the number of digest text is completely different.
Not only the traffic in 2.0.17 and 2.1.0 is different, but also the outputs of stats_mysql_query_digest and stats_mysql_query_rules do not match.
As an example in 2.0.17 outputs, in stats_mysql_query_rules the sum of hits is 71 , while the sum of count_star in stats_mysql_query_digest is several hundreds.

I don't know if your claims are correct and if there is a real issue, but the output you provided so far seems to not provide useful information to start an investigation.

Please provide correct/useful information.
Thanks

renecannao on 27 Apr 2021

Thanks for letting me know I'm an insane idiot and have n oidea what I'm talkin about, and that since I did not test this with an exact duplicate query test against each and the tested source isn't 100% perfectly matched everything I said and claimed is invalid.

The test is with powerdns on a live system, so the results are not going be 100% identical. I included the queries above so you can see it's the same queries on each, no they are not expected to be 100% perfect match against each other. I don't expect life to always produce a perfect match.

The fact remains the results don't match the source. I don't understand why yo uare so hung up on the number of queries made should be identical. Isn't it very suspicious every query in the first one is a select, and it matches hostgroup 2, like I told it to.
Yet in the second group they are all selects again, but this time they aren't all hostgroup 2, they are mostly hostgroup 1.

Don't get so hung up on numbers matching perfectly. There is no transactions, there is no select for update. Both cases the set is matching hostgroup 2 so that isn't the cause.

Or can you give me any reason why hostgroup 1 is matching all those, other then the counts are different.

patrickdk77 on 27 Apr 2021

Also while I believe that all to be very clearly useful, but you think it's completely useless.
It would be extreemely helpful to say what would be useful infomation to provide instaid of saying I dunno what I'm doing and cannot report anything meaningful.

What about hostgroup 1 being in that list at all when it shouldn't be, is not proof of my claim of a real issue and cannot be useful to start an investigation.

patrickdk77 on 27 Apr 2021

Duplicate reported issue #3427

patrickdk77 on 4 May 2021

It doesn't match for me in ver 2.1.1 as well.

These are the only rules I have going to hostgroup 7

Admin> select rule_id,active,digest,match_digest,match_pattern, destination_hostgroup from mysql_query_rules where destination_hostgroup = 7;
+---------+--------+--------+---------------------------------+---------------+-----------------------+
| rule_id | active | digest | match_digest                    | match_pattern | destination_hostgroup |
+---------+--------+--------+---------------------------------+---------------+-----------------------+
| 10      | 1      | NULL   | ^SELECT.*FROM `article_archive` | NULL          | 7                     |
| 31      | 1      | NULL   | NULL                            | ^EXPLAIN      | 7                     |
+---------+--------+--------+---------------------------------+---------------+-----------------------+

These are my rules sending queries to hostgroup = 1

Admin> select rule_id,active,digest,match_digest,match_pattern, destination_hostgroup from mysql_query_rules where destination_hostgroup = 1;
+---------+--------+--------------------+--------------+---------------+-----------------------+
| rule_id | active | digest             | match_digest | match_pattern | destination_hostgroup |
+---------+--------+--------------------+--------------+---------------+-----------------------+
| 20      | 1      | NULL               | NULL         | ^SELECT       | 1                     |
| 21      | 1      | NULL               | NULL         | ^\(select     | 1                     |
| 71      | 1      | 0x063A32DFACBA51F6 | NULL         | NULL          | 1                     |
| 72      | 1      | 0x22DA11FB84E7514C | NULL         | NULL          | 1                     |
| 73      | 1      | 0xDD2CE8A380745657 | NULL         | NULL          | 1                     |
+---------+--------+--------------------+--------------+---------------+-----------------------+

However, I'm getting all these queries being sent to hostgroup 7 where it is supposed to go to hostgroup 1

Admin> select hostgroup, digest, digest_text, count_star from stats_mysql_query_digest where hostgroup = 7 order by count_star desc limit 5;
+-----------+--------------------+------------------------------------------------------------------------------------------------------------------------+------------+
| hostgroup | digest             | digest_text                                                                                                            | count_star |
+-----------+--------------------+------------------------------------------------------------------------------------------------------------------------+------------+
| 7         | 0x93483C1678F2A118 | select * from `widget` where `np_widget_id` = ? limit ?                                                                | 122251     |
| 7         | 0x0C22088C540C8680 | select * from `image` where `image`.`np_related_article_id` = ? and `image`.`np_related_article_id` is not null        | 120857     |
| 7         | 0x68E769684090A07D | select * from `image` where `np_related_article_id` = ? and `image_is_deleted` != ? order by `media_order` asc limit ? | 68350      |
| 7         | 0xE171149E9C71A64C | select * from `widget` where `np_widget_id` in (?) order by FIELD(np_widget_id,?)                                      | 33175      |
| 7         | 0x03A2D1F96DBB1C8E | select * from `widget` where `np_widget_id` in (?,?,?) order by FIELD(np_widget_id,?,?,?)                              | 13270      |
+-----------+--------------------+------------------------------------------------------------------------------------------------------------------------+------------+

*UPDATE

This issue did not happen at 2.0.17

teewhey on 5 May 2021

Hi René, i also can reproduce this issue from upgrading or downgrading from 2.1 to 2.0, will try to test with --skip_trx=on and report may be queries inside TRX are alway routed to master , that's the oltp_read_write.lua and config is

admin_variables=
{
    admin_credentials="admin:admin;external:admin"
    mysql_ifaces="[::]:6032;0.0.0.0:6032;/tmp/proxysql_admin.sock"

}

mysql_variables=
{
    threads=24
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
        have_ssl=false
    poll_timeout=2000
    interfaces="[::]:3306;0.0.0.0:3306;/tmp/proxysql.sock"
    default_schema="information_schema"
    stacksize=1048576
    server_version="8.0"
    connect_timeout_server=3000
    monitor_history=600000
    monitor_connect_interval=60000
        monitor_ping_interval=4000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500
        monitor_username="root"
        monitor_password="mariadb"
    ping_interval_server_msec=20000
    ping_timeout_server=1000
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
        monitor_writer_is_also_reader=1
}


# defines all the MySQL servers
mysql_servers =
(

    { address="db1.bench.svc.rs1" , port=3306 , hostgroup=1, max_connections=1024 },
    { address="db2.bench.svc.rs1" , port=3306 , hostgroup=1, max_connections=1024 }
)

mysql_users:
(
 { username = "root" , password = "mariadb" , default_hostgroup = 0 , active = 1 }
)
#defines MySQL Query Rules
mysql_query_rules:
(
          {
              rule_id=1
              active=1
              match_digest="^SELECT .* FOR UPDATE$"
              destination_hostgroup=0
              apply=1
          },
          {
              rule_id=2
              active=1
              match_digest="^SELECT"
              destination_hostgroup=1
              apply=1
          },
          {
              rule_id=3
              active=1
              match_pattern="^SELECT @@.*"
              destination_hostgroup=0
              apply=1
          },
          {
              rule_id=4
              active=1
              match_pattern="^SHOW.*VARIABLES"
              destination_hostgroup=0
              apply=1
          }
)
mysql_replication_hostgroups=
(
        {
                writer_hostgroup=0
                reader_hostgroup=1
                comment="test repl 1"
       }
)