search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
5.98k stars 974 forks source link

sometime ‘DELETE’ statement route to write_hostgroup，sometime route to reader_hostgroup #2592

Open nasamitnick opened 4 years ago

nasamitnick commented 4 years ago

proxysql version: 2.0.9 os version: redhat7.5

The configuration is as follows:

runtime_mysql_users
mysql> select * from runtime_mysql_users where username='uip_msm_sit'\G
*************************** 1. row ***************************
              username: uip_msm_sit
              password: *E2F303E7C7E7D0F290F33045CC25FBE4F79000A2
                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_msm_sit
              password: *E2F303E7C7E7D0F290F33045CC25FBE4F79000A2
                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)

mysql> select * from runtime_mysql_group_replication_hostgroups\G
*************************** 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
1 row in set (0.00 sec)

mysql> select * from runtime_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: .*@.*
        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: 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
*************************** 3. 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
3 rows in set (0.00 sec)

select * from stats_mysql_query_digest where username = 'uip_msm_sit' and digest_text like '%DELETE FROM%'\G;
*************************** 1. row ***************************
        hostgroup: 30
       schemaname: uip_msm_db_sit
         username: uip_msm_sit
   client_address: 
           digest: 0x994828D95627E896
      digest_text: DELETE FROM ms_deploy WHERE name=? AND ip=?
       count_star: 3
       first_seen: 1583457455
        last_seen: 1583457476
         sum_time: 9223372036854775807
         min_time: 9223372036854775807
         max_time: 9223372036854775807
sum_rows_affected: 0
    sum_rows_sent: 2
*************************** 2. row ***************************
        hostgroup: 10
       schemaname: uip_msm_db_sit
         username: uip_msm_sit
   client_address: 
           digest: 0x994828D95627E896
      digest_text: DELETE FROM ms_deploy WHERE name=? AND ip=?
       count_star: 1
       first_seen: 1583456120
        last_seen: 1583456120
         sum_time: 602
         min_time: 602
         max_time: 602
sum_rows_affected: 1
    sum_rows_sent: 1
2 rows in set (0.00 sec)

mysql> select * from stats_mysql_query_digest_reset where username = 'uip_msm_sit' and digest_text like '%DELETE FROM%'\G;
*************************** 1. row ***************************
        hostgroup: 30
       schemaname: uip_msm_db_sit
         username: uip_msm_sit
   client_address: 
           digest: 0x994828D95627E896
      digest_text: DELETE FROM ms_deploy WHERE name=? AND ip=?
       count_star: 3
       first_seen: 1583457455
        last_seen: 1583457476
         sum_time: 9223372036854775807
         min_time: 9223372036854775807
         max_time: 9223372036854775807
sum_rows_affected: 0
    sum_rows_sent: 2
*************************** 2. row ***************************
        hostgroup: 10
       schemaname: uip_msm_db_sit
         username: uip_msm_sit
   client_address: 
           digest: 0x994828D95627E896
      digest_text: DELETE FROM ms_deploy WHERE name=? AND ip=?
       count_star: 1
       first_seen: 1583456120
        last_seen: 1583456120
         sum_time: 602
         min_time: 602
         max_time: 602
sum_rows_affected: 1
    sum_rows_sent: 1
2 rows in set (0.00 sec)
renecannao commented 4 years ago

Hi. Error log please, hopefully it will show some details

nasamitnick commented 4 years ago

Hi. Error log please, hopefully it will show some details sorry，No errors in log，no more details，Very strange@renecannao

shafiev commented 4 years ago

@renecannao can you advice a some error log related for we in future have a appropriate logs ?

nasamitnick commented 4 years ago

@renecannao proxysql no error log,help me，Is it related to “transaction_persistent=1”?