Query routing match on set autocommit=0

[bt...@gatessolutions.com]

Greetings,

  Our applications commonly demarcate the start of transaction with 'set autocommit=0'.  Our current setup uses transaction_persistent=1 on all users, and the usual hostgroup 0 (master/writer) and 1 (slave/reader) configuration with the following query routing rules:

mysql> select rule_id, active, match_pattern, destination_hostgroup from mysql_query_rules\G

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

              rule_id: 7

               active: 1

        match_pattern: ^start transaction read only.*

destination_hostgroup: 1

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

              rule_id: 8

               active: 1

        match_pattern: ^start transaction.*|^begin.*|^set autocommit\=0.*

destination_hostgroup: 0

*************************** 3. row ***************************

              rule_id: 9

               active: 1

        match_pattern: ^select.*

destination_hostgroup: 1

3 rows in set (0.01 sec)

In our testing, transactions beginning with 'start transaction' or 'begin' route correctly to hostgroup 0, however transactions beginning with 'set autocommit=0' do not match the rule and are balanced across the servers in hostgroup 1 according to weight.

How can we reliably route transactions starting with 'set autocommit=0' to hostgroup 0?

Thanks much in advance.

[bt...@gatessolutions.com]

Any thoughts on this one from the community?  I figure somebody must have solved as "set autocommit=0' is a common way to start a transaction, but it has me puzzled.

[René Cannaò]

Hi,

Please refer to https://github.com/sysown/proxysql/issues/813 .

In a nutshell, "set autocommit" is not sent to any backend but tracked internally, and it makes sense in a lot of installations.

In ProxySQL 1.4.0, this behavior will be optional.

About "transactions... balanced across the servers in hostgroup 1" : what is the value of default_hostgroup in mysql_servers?

If default_hostgroup=1, it is expected that transactions are balanced across servers in hostgroup 1 .

If default_hostgroup=0, no transactions should starts on hostgroup 1 unless transactions start with "start transaction read only" , or enforce_autocommit_on_reads is set to true.

IMPORTANT: This e-mail (including any attachments) is intended for the use of the individual or entity to which it is addressed and may contain information that is classified, private, or confidential. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is prohibited. If you have received this communication in error, please notify us immediately by replying to this e-mail. Thank you.

--
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.

[bt...@gatessolutions.com]

Rene,

  Thank you for the reply.  Please disregard what I said about the queries balancing across the two servers in hostgroup 1, that was due to a misconfiguration.

To take a step back for a moment I'll just state what I really want to achieve: with a master hostgroup (1) and a slave hostgroup (2), I want everything to go to hostgroup 1 except for:

1) Single-statement SELECT when autocommit is 1 (the default on the server backend(s) is autocommit=1).

2) A read only transaction initiated with 'start transaction read only'

So in that sense, it doesn't seem necessary that 'set autocommit=0' is sent to the backends, only that the ProxySQL:

1) Can match a query rule against 'set autocommit=0' to route this to the master hostgroup (1).

2) Send all additional queries that are part of that transaction (started by set autocommit=0) to the master hostgroup because the user's transaction_persistent is set to 1.

With that in mind, here is my test user setup:

MySQL [(none)]> select * from mysql_users\G

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

              username: proxy-test

              password: <sanitized>

                active: 1

               use_ssl: 0

     default_hostgroup: 1

        default_schema: NULL

         schema_locked: 0

transaction_persistent: 1

          fast_forward: 0

               backend: 1

              frontend: 1

       max_connections: 10000

1 row in set (0.00 sec)

And here are the query rules:

MySQL [(none)]> select rule_id, active, match_pattern, destination_hostgroup, apply from mysql_query_rules\G

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

              rule_id: 2

               active: 1

        match_pattern: ^start transaction read only.*$

destination_hostgroup: 2

                apply: 1

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

              rule_id: 3

               active: 1

        match_pattern: ^start transaction.*$|$begin.*$|^set autocommit=0.*$

destination_hostgroup: 1

                apply: 1

*************************** 3. row ***************************

              rule_id: 4

               active: 1

        match_pattern: ^select.*$

destination_hostgroup: 2

                apply: 1

3 rows in set (0.00 sec)

All of the rules work as expected, except for set autocommiit=0.  Here is the results of my test where dev-db-005 is the master and dev-db-006 is the slave:

$ mysql -h 127.0.0.1 --port 6033 --user proxy-test -p -e 'start transaction read only; select @@hostname\G commit;'

@@hostname: dev-db-006

$ mysql -h 127.0.0.1 --port 6033 --user proxy-test -p -e 'start transaction; select @@hostname\G commit;'

@@hostname: dev-db-005

$ mysql -h 127.0.0.1 --port 6033 --user proxy-test -p -e 'begin; select @@hostname\G commit;'

@@hostname: dev-db-005

$ mysql -h 127.0.0.1 --port 6033 --user proxy-test -p -e 'set autocommit=0; select @@hostname\G commit;

@@hostname: dev-db-006 -- should be dev-db-005

$ mysql -h 127.0.0.1 --port 6033 --user proxy-test -p -e 'select @@hostname;'
@@hostname: dev-db-006

Any insight into why start transaction/begin works but set autocommit=0 doesn't?  Thanks again for your help!

To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.