mysql-enforce_autocommit_on_reads not evaluated for Prepared Statements

[krzysztof-ksiazek]

wget https://github.com/sysown/proxysql/releases/download/v1.3.3/proxysql_1.3.3-ubuntu14_amd64.deb
One Percona Server 5.7.17.

Configure ProxySQL with this host in two host groups. Configure host groups into mysql_replication_hostgroups

*************************** 1. row ***************************
       hostgroup_id: 10
           hostname: 10.0.0.101
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 100
max_replication_lag: 10
            use_ssl: 0
     max_latency_ms: 0
            comment: write server
*************************** 2. row ***************************
       hostgroup_id: 20
           hostname: 10.0.0.101
               port: 3306
             status: ONLINE
             weight: 1
        compression: 0
    max_connections: 100
max_replication_lag: 10
            use_ssl: 0
     max_latency_ms: 0
            comment: read server
2 rows in set (0.00 sec)

*************************** 1. row ***************************
writer_hostgroup: 10
reader_hostgroup: 20
         comment: host groups
1 row in set (0.00 sec)

Install tpcc-mysql:
git clone https://github.com/Percona-Lab/tpcc-mysql.git
cd tpcc-mysql/src
make
cd ..

Setup schema/user on MySQL
mysql -utpcc -ptpccpass -h10.0.0.101 tpcc1000 < create_table.sql
mysql -utpcc -ptpccpass -h10.0.0.101 tpcc1000 < add_fkey_idx.sql
./tpcc_load -h 10.0.0.101 -d tpcc1000 -u tpcc -p tpccpass -w 5

./tpcc_start -h 127.0.0.1 -P6033 -u tpcc -p tpccpass -d tpcc1000 -w 5 -c 1 -r 1 -l 60

While tpcc runs, execute:

update mysql_servers set status='OFFLINE_SOFT'; load mysql servers to runtime;

tpcc is stuck with ProxySQL showing:

+-----------+------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| hostgroup | srv_host   | srv_port | status       | ConnUsed | ConnFree | ConnOK | ConnERR | Queries | Bytes_data_sent | Bytes_data_recv | Latency_us |
+-----------+------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
| 20        | 10.0.0.101 | 3306     | OFFLINE_SOFT | 1        | 0        | 789    | 0       | 168233  | 5220941         | 164068236       | 253        |
| 10        | 10.0.0.101 | 3306     | OFFLINE_SOFT | 0        | 0        | 5      | 0       | 48710   | 2455266         | 3286676         | 253        |
+-----------+------------+----------+--------------+----------+----------+--------+---------+---------+-----------------+-----------------+------------+
2 rows in set (0.00 sec)

[krzysztof-ksiazek]

mysql> select * from mysql_query_rules\G
*************************** 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: NULL
        match_pattern: ^SELECT .* FOR UPDATE
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 10
            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: 200
               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: 20
            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
*************************** 3. row ***************************
              rule_id: 300
               active: 1
             username: NULL
           schemaname: NULL
               flagIN: 0
          client_addr: NULL
           proxy_addr: NULL
           proxy_port: NULL
               digest: NULL
         match_digest: NULL
        match_pattern: .*
 negate_match_pattern: 0
              flagOUT: NULL
      replace_pattern: NULL
destination_hostgroup: 10
            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
3 rows in set (0.00 sec)

mysql> select * from mysql_users\G
*************************** 1. row ***************************
              username: proxydemo
              password: proxydemo
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: proxydemo
         schema_locked: 0
transaction_persistent: 0
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
*************************** 2. row ***************************
              username: tpcc
              password: *8C446904FFE784865DF49B29DABEF3B2A6D232FC
                active: 1
               use_ssl: 0
     default_hostgroup: 10
        default_schema: NULL
         schema_locked: 0
transaction_persistent: 0
          fast_forward: 0
               backend: 1
              frontend: 1
       max_connections: 10000
2 rows in set (0.00 sec)

[renecannao]

@krzysztof-ksiazek : thank you for the report.
The behavior reported seems to be a side effect of the fact that mysql-enforce_autocommit_on_reads is not evaluated with prepared statements. Because of this, the connection to hostgroup 20 will set autocommit=0 and generate a transaction even for SELECT statements. The connection is not returned to the connection pool, therefore it stays in use.

[renecannao]

Title updated.
Thank you for the bug report.

[renecannao]

Fixed in 1.3.4