For transaction_persistent users, when transaction completes, does not go to back to read-write split

[leeparayno]

Environment:

mysql56b-1 (master) - hostgroup0
|- mysql56b-2 (slave) - hostgroup1
|- mysql56b-3 (slave) - hostgroup1

dbuser - default hostgroup0

• set to be transaction_persistent = 1

            active: 1
           use_ssl: 0
  

  default_hostgroup: 0
  default_schema:
  schema_locked: 0
  transaction_persistent: 1
  fast_forward: 0
  backend: 1
  frontend: 1
  max_connections: 10000

Query Rules:

mysql> select * from mysql_query_rules\G;
*************************** 1. row ***************************
rule_id: 1
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: NULL
digest: NULL
match_digest: NULL
match_pattern: ^SELECT.*FOR UPDATE$
negate_match_pattern: 0
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 0
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
log: NULL
apply: 1
comment: NULL
*************************** 2. row ***************************
rule_id: 2
active: 1
username: NULL
schemaname: NULL
flagIN: 0
client_addr: NULL
proxy_addr: NULL
proxy_port: NULL
digest: NULL
match_digest: NULL
match_pattern: ^SELECT
negate_match_pattern: 0
flagOUT: NULL
replace_pattern: NULL
destination_hostgroup: 1
cache_ttl: NULL
reconnect: NULL
timeout: NULL
retries: NULL
delay: NULL
mirror_flagOUT: NULL
mirror_hostgroup: NULL
error_msg: NULL
log: NULL
apply: 1
comment: NULL

Upon first connecting to proxysql connection shows it is on one of the read replicas:
mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql56b-2 |
+------------+
1 row in set (0.11 sec)

Turning off auto commit:

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

Still on read replica:

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql56b-2 |
+------------+
1 row in set (0.00 sec)

Start a transaction:

mysql> start transaction;
Query OK, 0 rows affected (0.03 sec)

Set to default hostgroup0 for master:

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql56b-1 |
+------------+
1 row in set (0.00 sec)

mysql> insert into test3.t1 (name) values ('Dwyane Wade');
Query OK, 1 row affected (0.00 sec)

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql56b-1 |
+------------+
1 row in set (0.00 sec)

Finish the transaction:

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

Should be outside of a transaction, so should be able to go back to read/write split:

mysql> select @@hostname;
+------------+
| @@hostname |
+------------+
| mysql56b-1 |
+------------+
1 row in set (0.00 sec)

[renecannao]

hi @leeparayno .
There is a missing information here that is very important: how read/write split is defined?

[renecannao]

Verified

[renecannao]

Fixed in v1.3.3 (planned to be released tomorrow) and v1.4.0 .
Thank for the report.