Proxysql: Ask about how to configure proxysql transaction

[WilliamShin]

Hi..

I want to configure a sample project in proxysql, proxysql transaction processing does not work as I thought.

I configured the query rule to divide into smaller groups based on AvatarId and to pass to hostgroups 10 and 20.

In general, query rules work well.
If you test the transaction (as in # 4 below), it will be forwarded only to the default host group.

That is, all queries are passed by default to 10.
How do I apply a transaction based on the AvatarId value set in a query rule?

The proxysql configuration of my sample project is shown below.

1. mysql user configuration
   +---------------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
   | username | password | active | use_ssl | default_hostgroup | default_schema | schema_locked | transaction_persistent | fast_forward | backend | frontend | max_connections |
   +---------------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+
   | Test | 123123 | 1 | 0 | 10 | Test | 0 | 1 | 0 | 1 | 1 | 10000 |
   +---------------+----------+--------+---------+-------------------+----------------+---------------+------------------------+--------------+---------+----------+-----------------+

2. global_variables configuration
   proxy admin> select * from global_variables where variable_name like '%autocommit%';
   +---------------------------------------+----------------+
   | variable_name | variable_value |
   +---------------------------------------+----------------+
   | mysql-forward_autocommit | false |
   | mysql-enforce_autocommit_on_reads | false |
   | mysql-autocommit_false_not_reusable | false |
   | mysql-autocommit_false_is_transaction | false |
   +---------------------------------------+----------------+

3. query rule
   proxy admin> select * from mysql_query_rulesG;
   ******** 1. row *******
   rule_id: 92
   active: 1
   username: Test
   schemaname: NULL
   flagIN: 0
   client_addr: NULL
   proxy_addr: NULL
   proxy_port: NULL
   digest: NULL
   match_digest: ^INSERT(.)AvatarId
   match_pattern: NULL
   negate_match_pattern: 0
   re_modifiers: CASELESS
   flagOUT: 1000
   replace_pattern: NULL
   destination_hostgroup: NULL
   cache_ttl: NULL
   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: NULL
   apply: 0
   comment: NULL
   ******** 2. row *******
   rule_id: 94
   active: 1
   username: Test
   schemaname: NULL
   flagIN: 1000
   client_addr: NULL
   proxy_addr: NULL
   proxy_port: NULL
   digest: NULL
   match_digest: NULL
   match_pattern: (sd[02468],
   negate_match_pattern: 0
   re_modifiers: CASELESS
   flagOUT: NULL
   replace_pattern: NULL
   destination_hostgroup: 20
   cache_ttl: NULL
   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: NULL
   apply: 1
   comment: NULL
   ******* 3. row *******
   rule_id: 95
   active: 1
   username: Test
   schemaname: NULL
   flagIN: 0
   client_addr: NULL
   proxy_addr: NULL
   proxy_port: NULL
   digest: NULL
   match_digest: ^(DELETE|UPDATE|SELECT)(.)AvatarId
   match_pattern: NULL
   negate_match_pattern: 0
   re_modifiers: CASELESS
   flagOUT: 2000
   replace_pattern: NULL
   destination_hostgroup: NULL
   cache_ttl: NULL
   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: NULL
   apply: 0
   comment: NULL
   ******** 4. row *******
   rule_id: 98
   active: 1
   username: Test
   schemaname: NULL
   flagIN: 2000
   client_addr: NULL
   proxy_addr: NULL
   proxy_port: NULL
   digest: NULL
   match_digest: NULL
   match_pattern: AvatarId`s=sd02468
   negate_match_pattern: 0
   re_modifiers: CASELESS
   flagOUT: NULL
   replace_pattern: NULL
   destination_hostgroup: 20
   cache_ttl: NULL
   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: NULL
   apply: 1
   comment: NULL

4. test
   mysql -h 127.0.0.1 --port 6033 --user Test -p -e "BEGIN;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;INSERT TABLE AvatarId=1;COMMIT;"
   mysql -h 127.0.0.1 --port 6033 --user Test -p -e "BEGIN;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;INSERT TABLE AvatarId=2;COMMIT;"

mysql -h 127.0.0.1 --port 6033 --user Test -p -e "BEGIN;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;INSERT TABLE AvatarId=1;ROLLBACK;"
mysql -h 127.0.0.1 --port 6033 --user Test -p -e "BEGIN;SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;INSERT TABLE AvatarId=2;ROLLBACK;"

1. version
   proxy admin> select @@version;
   +------------------+
   | @@version |
   +------------------+
   | 1.4.9-3-gd9fd599 |
   +------------------+
   1 row in set (0.00 sec)

Thanks a lot!
William

[pondix]

This is the place for paid support and community users to report a reproducible bug in ProxySQL, suggest enhancements or make feature requests. This also includes documentation errors and/or missing documentation.

• Remember, this is the place for fixing and improving ProxySQL rather than the place to get free support.
• If you do not have a paid support contract please try https://groups.google.com/forum/#!forum/proxysql. Due to the high volume of requests, we are unable to always provide timely support for community users.

If you are submitting a reproducible bug report, please provide:
[ ] A clear description of your issue
[ ] The version of OS and ProxySQL
[ ] Every step to reproduce the issue
[ ] The error log
[ ] If it is a crashing bug, a core dump will be extremely useful.

Please use markdown to format code or SQL: https://guides.github.com/features/mastering-markdown/

Thank you!

[crazyacking]

@WilliamShin This is answer that you want: #1256