Query rule fast routing feature, what am I doing wrong?

[Timothy Weidner]

I have been banging my head against this for several hours and simply cannot figure out what I am doing wrong; it feels like I'm taking crazy pills.

I have configured some backend servers, a single user, a single standard query rule, and a 'fast routing' rule.  The backends/servers all function exactly as expected.  When the users are configured to use a specific backend, it also works.  When the query rule is configured to send to a *different* backend, it works.

So far, all exactly as expected.  However, when I change the query rule to not have a destination_hostgroup defined, and instead have a 'fast routing' rule to handle that (as per the use-case in the blog post), it simply doesn't work.  It's like the fast routing rule simply doesn't exist.  I have tried at least two or three dozen combinations of settings, rules, users, servers, etc, and the results are always the same.  Additionally, the query rule hit counter does increment, so it's at least firing the rule when I try to connect; it just doesn't work properly.

To be a bit more clear on "does not work properly" I mean that it either doesn't send me to a backend at all (simply times out) as in the configuration below, or sends me to the backend specified in the standard query rule rather than the fast routing rule.  The standard query rule always seems to work, it's like the fast routing option simply never fires.

Given that this is given an extremely brief write-up in the docs, I can only assume that I am missing something somewhere...I have verified all of the places I can think of (no transaction_persistent user settings, etc) but I cannot make any progress on this; I'm wondering if I've completely misunderstood the purpose or functioning of this feature.

As far as my use-case, it's simple:  I have a single user that needs to be able to connect to several backends, and several hundred/thousand schemas distributed among those backends.  As the performance for using plain rules makes this a non-starter, it seemed like the 'fast routing' feature was custom crafted for this purpose; even the blog article's example demonstrated as much.  

Here's some example configs to help clarify this, maybe someone here can spot where I've gone wrong (they *are* loaded into runtime as well):

#############################################################

Server config:

ProxySQL> select * from mysql_servers\G

*************************** 1. row ***************************

       hostgroup_id: 1

           hostname: serverone.lan

               port: 3306

             status: ONLINE

             weight: 1

        compression: 0

    max_connections: 1200

max_replication_lag: 0

            use_ssl: 0

     max_latency_ms: 0

            comment: 

*************************** 2. row ***************************

       hostgroup_id: 2

           hostname: servertwo.lan

               port: 3306

             status: ONLINE

             weight: 1

        compression: 0

    max_connections: 1200

max_replication_lag: 0

            use_ssl: 0

     max_latency_ms: 0

            comment: 

User Config:

ProxySQL> select * from mysql_users\G

*************************** 1. row ***************************

              username: userone

              password: <redacted>

                active: 1

               use_ssl: 0

     default_hostgroup: 0

        default_schema: NULL

         schema_locked: 0

transaction_persistent: 0

          fast_forward: 0

               backend: 1

              frontend: 1

       max_connections: 10000

Query Rule:

ProxySQL> select * from mysql_query_rules\G

*************************** 1. row ***************************

              rule_id: 1

               active: 1

             username: userone

           schemaname: NULL

               flagIN: 0

          client_addr: NULL

           proxy_addr: NULL

           proxy_port: NULL

               digest: NULL

         match_digest: NULL

        match_pattern: NULL

 negate_match_pattern: 0

         re_modifiers: CASELESS

              flagOUT: NULL

      replace_pattern: NULL

destination_hostgroup: NULL

            cache_ttl: 100

            reconnect: NULL

              timeout: NULL

              retries: NULL

                delay: NULL

    next_query_flagIN: NULL

       mirror_flagOUT: NULL

     mirror_hostgroup: NULL

            error_msg: NULL

               OK_msg: NULL

          sticky_conn: NULL

            multiplex: NULL

                  log: 1

                apply: 0

              comment: NULL

Fast Routing Rule:

ProxySQL> select * from mysql_query_rules_fast_routing\G

*************************** 1. row ***************************

             username: userone

           schemaname: schemaone

               flagIN: 0

destination_hostgroup: 2

              comment: testing

#############################################################

Hopefully someone out there can point me in the right direction here, or tell me that I've completely misunderstood how this is supposed to work.  Any help is appreciated; and if more information is necessary, I'm happy to provide it.

[Karl Latiss]

Did you get this working? I also can't seem to get this to work as I expected. In my case the backend chosen is always the user's default hostgroup.

[René Cannaò]

Please upgrade to 1.4.9 .

We found a bug introduced in 1.4.7 (the same version where fast_routing was introduced) just before it was released, and we failed to detect it during testing.

Sorry about that.

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[twei...@ontraport.com]

The update to 1.4.9 *did* fix the issue. If you add users, query rules, and fast routing rules as described in the docs (and the blog post), they now behave as expected.