After spending four years working with ProxySQL in production, I've learned a few interesting lessons about interpreting and processing query rules. I hope to save you some time (and avoid wrong turns) with this summary of ProxySQL query rules.
ProxySQL query engine is very powerful and supports the building of complex rule sets. These can be used to route traffic to backend MySQL instances, rewrite queries, and for
traffic mirroring, among other use cases. When the rule set is short and simple, you can easily understand what the outcome for a certain query would be. However, for complex sets or combined scenarios, you need a deeper understanding of the engine logic.
Note 1: Rules are processed in rule_id order
It might seem like a trivial start, but during testing or implementation, it's not uncommon to delete or insert the rules without taking the id into consideration. Furthermore, in more mature environments, it's also common to have disabled rules allocating ids. This can cause the runtime set to be less linear than with newer environments. Unless different chains were implemented, rules are processed based on the id, from lowest to highest.Note 2: Things to consider when using flagIN / flagOUT to create rule chains
Sometimes you need the query engine to take different paths depending on certain conditions, and you need to create rule chains for that purpose. The documentation says that you can set a flagOUT value for a rule so that only those with a matching flagIN are evaluated next. What is not always clear is that a query is only marked with flagOUT if the query matches the rule in question. Also:- Remember that only rules with flagIN equal to 0 are considered at the beginning. [caption id="attachment_108415" align="alignnone" width="528"]
If default values are used for flagIN, flagOUT and apply, all rules are processed, from min to max rule_id[/caption] - Any rules above the one currently being processed are not visited again, regardless of the flagIN value.
- flagOUT equals NULL means stay on the current chain id. [caption id="attachment_108417" align="alignnone" width="625"]
flagOUT is only enforced when the rule matches[/caption]
Note 3: "apply" will end rules processing
Regardless of how many chains we created, and how many rules exist after the one currently being processed, if the apply flag is set to 1, no further rules will be checked. A very important detail is that apply is only honored if there is a match.
Note 4: negate_match_pattern combined with replace_pattern won't work as you might expect
This scenario is more specific, but I noticed this behavior while working on a production solution. You might think if you have a pattern and want to return a generic message for any queries *NOT* matching that pattern, setting the message in replace_pattern and enabling negate_match_pattern will do. As this github issue explains, replace_pattern only works when the query matches the pattern. If you use negate_match_pattern equals 1, the query won't match the pattern, and no replacement will happen.Note 5: IP/Port-based rules
If you are looking to create rules based on specific combinations of ports and IPs, you need to specify every combination in mysql-interfaces parameter . For example, if I want to create different rules for queries coming on different IPs, for port 6098, it is not enough to include 0.0.0.0:6098 in mysql-interfaces. I need to specify every combination of IP and port that I'm expecting to use in rules, even when the port is the same. See the example below: Rules:a) IF proxy_addr = 10.0.0.10 AND proxy_port = 6098 THEN hostgroup = 0
b) IF proxy_addr = 10.0.0.11 AND proxy_port = 6098 THEN hostgroup = 1
c) IF proxy_addr = 10.0.0.12 AND proxy_port = 6098 THEN hostgroup = 2
Incorrect configurationmysql-interfaces = 0.0.0.0:6033;0.0.0.0:6034;0.0.0.0:6098
Correct configuration
mysql-interfaces = 0.0.0.0:6033;0.0.0.0:6034;10.0.0.10:6098;10.0.0.11:6098;10.0.0.12:6098
