Description
set autocommit=0, the first select request was sent to the slave.
proxysql variables:
root 16:05: [(none)]> show variables like '%auto%';
+---------------------------------------+-------+
| Variable_name | Value |
+---------------------------------------+-------+
| mysql-forward_autocommit | false |
| mysql-enforce_autocommit_on_reads | false |
| mysql-autocommit_false_not_reusable | false |
| mysql-autocommit_false_is_transaction | true |
+---------------------------------------+-------+
root 16:06: [(none)]> select * from runtime_mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
| 0 | 10.20.31.3 | 3306 | ONLINE | 1000 | 0 | 2000 | 0 | 0 | 0 | |
| 1 | 10.20.31.5 | 3306 | ONLINE | 1000 | 0 | 1000 | 30 | 0 | 0 | online-slave |
| 1 | 10.20.31.6 | 3306 | ONLINE | 1000 | 0 | 1000 | 30 | 0 | 0 | online-slave |
| 1 | 10.20.131.4 | 3306 | ONLINE | 1000 | 0 | 2000 | 30 | 0 | 0 | |
| 1 | 10.20.31.3 | 3306 | ONLINE | 1000 | 0 | 2000 | 0 | 0 | 0 | |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+--------------+
5 rows in set (0.01 sec)
root 16:07: [(none)]> select rule_id,active,flagIN,client_addr,match_digest,flagOUT,error_msg,log,apply from runtime_mysql_query_rules;
+---------+--------+--------+---------------+---------------------+---------+-------------------------+-----+-------+
| rule_id | active | flagIN | client_addr | match_digest | flagOUT | error_msg | log | apply |
+---------+--------+--------+---------------+---------------------+---------+-------------------------+-----+-------+
| 1 | 1 | 0 | 10.10.1.38 | NULL | 1 | NULL | NULL | 0 |
| 2 | 1 | 0 | 10.10.10.15 | NULL | 1 | NULL | NULL | 0 |
| 3 | 1 | 0 | 10.2.% | NULL | 1 | NULL | NULL | 0 |
| 4 | 1 | 0 | 10.20.% | NULL | 1 | NULL | NULL | 0 |
| 5 | 1 | 0 | 10.4.1.% | NULL | 1 | NULL | NULL | 0 |
| 10 | 1 | 1 | NULL | ^SELECT.*FOR UPDATE | NULL | NULL | NULL | 1 |
| 20 | 1 | 1 | NULL | ^SELECT | NULL | NULL | NULL | 1 |
| 1000 | 1 | 0 | NULL | . | NULL | not allow to connect db | NULL | 1 |
+---------+--------+--------+---------------+---------------------+---------+-------------------------+-----+-------+
8 rows in set (0.00 sec)
test 1:
mysql> show session variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.03 sec)
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from sbtest1 limit 1;
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------+----+
| id | k | c | pad | dd |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------+----+
| 8 | 60994 | 88658076981-28257193684-53183042641-08566229847-92845627546-46433913626-82618684116-59416871281-45638910500-66696979407 | sssss | |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------+----+
1 row in set (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
select sql to slave:
root 16:08: [(none)]> select * from stats_mysql_query_digest_reset;
+-----------+------------+-----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+-----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| 0 | sbtest | sbtest_rw | 0xDB3A841EF5443C35 | commit | 1 | 1538035792 | 1538035792 | 0 | 0 | 0 |
| 1 | sbtest | sbtest_rw | 0x33BBFAE7731547BC | select * from sbtest1 limit ? | 1 | 1538035789 | 1538035789 | 1401 | 1401 | 1401 |
| 0 | sbtest | sbtest_rw | 0x3DCD8685E25FCD55 | set autocommit = ? | 1 | 1538035785 | 1538035785 | 0 | 0 | 0 |
| 0 | sbtest | sbtest_rw | 0x949B8F9BD520E9AF | show session variables like ? | 1 | 1538035778 | 1538035778 | 2172 | 2172 | 2172 |
| 0 | sbtest | sbtest_rw | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1538035774 | 1538035774 | 0 | 0 | 0 |
+-----------+------------+-----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
5 rows in set (0.01 sec)
test 2:
mysql> set autocommit = false;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from sbtest1 limit 1;
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------+----+
| id | k | c | pad | dd |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------+----+
| 8 | 60994 | 88658076981-28257193684-53183042641-08566229847-92845627546-46433913626-82618684116-59416871281-45638910500-66696979407 | sssss | |
+----+-------+-------------------------------------------------------------------------------------------------------------------------+-------+----+
1 row in set (0.02 sec)
mysql> show session variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.03 sec)
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
"set autocommit = false" ,Settings do not take effect.
root 16:09: [(none)]> select * from stats_mysql_query_digest_reset;
+-----------+------------+-----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| hostgroup | schemaname | username | digest | digest_text | count_star | first_seen | last_seen | sum_time | min_time | max_time |
+-----------+------------+-----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+
| 0 | sbtest | sbtest_rw | 0xDB3A841EF5443C35 | commit | 1 | 1538035898 | 1538035898 | 0 | 0 | 0 |
| 0 | sbtest | sbtest_rw | 0x949B8F9BD520E9AF | show session variables like ? | 1 | 1538035895 | 1538035895 | 2150 | 2150 | 2150 |
| 0 | sbtest | sbtest_rw | 0x33BBFAE7731547BC | select * from sbtest1 limit ? | 1 | 1538035886 | 1538035886 | 503 | 503 | 503 |
| 0 | sbtest | sbtest_rw | 0x642F0314D34E54E9 | set autocommit = false | 1 | 1538035881 | 1538035881 | 670 | 670 | 670 |
| 0 | sbtest | sbtest_rw | 0x226CD90D52A2BA0B | select @@version_comment limit ? | 1 | 1538035876 | 1538035876 | 0 | 0 | 0 |
+-----------+------------+-----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+