look like 'transaction_persistent: 1' didn't work when use java spring Transaction

[nasamitnick]

proxysql version:2.0.10-27-g5b31997 os version:redhat 7.5

set autocommit variables :

+---------------------------------------+----------------+
| variable_name                         | variable_value |
+---------------------------------------+----------------+
| mysql-forward_autocommit              | true           |
| mysql-enforce_autocommit_on_reads     | false          |
| mysql-autocommit_false_not_reusable   | true           |
| mysql-autocommit_false_is_transaction | true           |
+---------------------------------------+----------------+

runtime_mysql_group_replication_hostgroups:

*************************** 1. row ***************************
       writer_hostgroup: 10
backup_writer_hostgroup: 20
       reader_hostgroup: 30
      offline_hostgroup: 40
                 active: 1
            max_writers: 1
  writer_is_also_reader: 0
max_transactions_behind: 200
                comment: NULL

runtime_mysql_servers:

************************** 1. row ***************************
       hostgroup_id: 10
           hostname: 130.1.107.48
               port: 3306
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 4000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: MGR-DB1-NODE1
*************************** 2. row ***************************
       hostgroup_id: 30
           hostname: 130.1.107.49
               port: 3306
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 4000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: MGR-DB1-NODE2
*************************** 3. row ***************************
       hostgroup_id: 30
           hostname: 130.1.107.50
               port: 3306
          gtid_port: 0
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 4000
max_replication_lag: 0
            use_ssl: 0
     max_latency_ms: 0
            comment: MGR-DB1-NODE3

runtime_mysql_users:

*************************** 1. row ***************************
              username: uip_sit
              password: *AFFB3BE27A5EF1B53F42BCC3AB72F81786B22B9A
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: 
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 1
              frontend: 0
       max_connections: 1000
               comment: 
*************************** 2. row ***************************
              username: uip_sit
              password: *AFFB3BE27A5EF1B53F42BCC3AB72F81786B22B9A
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: 
         schema_locked: 0
transaction_persistent: 1
          fast_forward: 0
               backend: 0
              frontend: 1
       max_connections: 1000
               comment: 
2 rows in set (0.00 sec)

runtime_mysql_query_rules:

*************************** 1. row ***************************
              rule_id: 100
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT.*FOR UPDATE$
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 10
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: 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
  gtid_from_hostgroup: NULL
                  log: NULL
                apply: 1
              comment: NULL
*************************** 2. row ***************************
              rule_id: 200
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: ^SELECT
        match_pattern: NULL
 negate_match_pattern: 0
         re_modifiers: CASELESS
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 30
            cache_ttl: NULL
   cache_empty_result: NULL
        cache_timeout: 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
  gtid_from_hostgroup: NULL
                  log: NULL
                apply: 1
              comment: NULL
2 rows in set (0.00 sec)

Execute SQL use java spring Transaction:

1. set autocommit=0; ##route to hostgroup: 10
2. select * from test1; ##route to hostgroup: 30
3. update test1 set c1='1111' where c2='2222'; ##route to hostgroup: 10
4. select * from test1; ##route to hostgroup: 30

Execute SQL use mysql client:

1. set autocommit=0; ##route to hostgroup: 10
2. select * from test1; ##route to hostgroup: 30
3. update test1 set c1='1111' where c2='2222'; ##route to hostgroup: 10
4. select * from test1; ##route to hostgroup: 10

look like 'transaction_persistent: 1' didn't work when use java spring Transaction;

[nasamitnick]

@renecannao please help me,thanks

[renecannao]

Question: are you using prepared statements?

[nasamitnick]

Question: are you using prepared statements? yes，in spring used prepared statements @renecannao

[nasamitnick]

when i set cachePrepStmts=false，It's right

[renecannao]

I think I know what causes this. QueryProcessor is called only during PREPARE , not during execute. We do this because during PREPARE we generate metadata, including destination hostgroups. This is to drastically improve performance, but I see the side effects. I think we should pay some extra cost (call the query processor) and correctly handle routing. Although, for sure we need to disable query rewrite during EXECUTE , because rewrite shouldn't be allowed during PREPARE .

[nasamitnick]

So will you fix this in a later version?@renecannao

[renecannao]

Hi @nasamitnick . I have a working POC already, but a lot of code was changed (around 400 lines), therefore we need to perform a lot of testing before releasing it.