"set autocommit = 0" is not a transaction

[beebol]

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 | +-----------+------------+-----------+--------------------+----------------------------------+------------+------------+------------+----------+----------+----------+

[beebol]

admin-version | 1.4.8-32-g669c149 | admin-version | v2.0.0-beta.1-22-ga20617e |

[renecannao]

@beebol , that is correct, set autocommit=0 doesn't start a transaction. Most of the behavior you described is correct, due the current configuration. Although there is a bug:

"set autocommit = false" ,Settings do not take effect.

ProxySQL only understands set autocommit = (0|1) .

[beebol]

@renecannao ,thks, "mysql-forward_autocommit = true" and "mysql-autocommit_false_is_transaction=true", set autocommit = 0 start a transcation on master.