proxysql pass db.autocommit(True)

[beebol]

ProxySQL version 1.4.15-1-g61bc777, codename Truls python:

import MySQLdb
ip="10.2x0.1x1.3"
port=6033
#port=3306
username="sbtest_rw"
password="sbtest_rw_123"
db = MySQLdb.connect(host=str(ip), port=int(port), user=username, passwd=password, db='', connect_timeout=2)
db.select_db('information_schema')
cursor = db.cursor()
db.autocommit(True)     # not send to mysql
#db.autocommit=1       # not send to mysql
#cursor.execute("set autocommit=1")       # send to mysql 
cursor.execute("select 'test';")

connect mysql:

2020-12-18T19:02:37.956521+08:00    6233877 Connect sbtest_rw@10xxx on  using TCP/IP
2020-12-18T19:02:37.981512+08:00    6233877 Query   set autocommit=0
2020-12-18T19:02:38.003419+08:00    6233877 Init DB information_schema
2020-12-18T19:02:38.025256+08:00    6233877 Query   set autocommit=1
2020-12-18T19:02:38.048288+08:00    6233877 Query   select 'test'

connect proxysql:

2020-12-18T19:25:32.826584+08:00    6230269 Change user sbtest_rw@10.2x0.1x1.3 on information_schema using TCP/IP
2020-12-18T19:25:37.160885+08:00    6230269 Query   SET NAMES latin1 COLLATE 'latin1_swedish_ci'
2020-12-18T19:25:37.162470+08:00    6230269 Query   set autocommit=0
2020-12-18T19:25:37.205841+08:00    6230269 Query   select 'test'

[renecannao]

I can't reproduce this with default configuration.

Please attach the output of:

SELECT * FROM global_variables WHERE variable_name LIKE '%autocommit%';

Thanks

[beebol]

root 00:47:  [(none)]> SELECT * FROM global_variables WHERE variable_name LIKE '%autocommit%';
+---------------------------------------+----------------+
| variable_name                         | variable_value |
+---------------------------------------+----------------+
| mysql-forward_autocommit              | true           |
| mysql-enforce_autocommit_on_reads     | false          |
| mysql-autocommit_false_not_reusable   | false          |
| mysql-autocommit_false_is_transaction | true           |
+---------------------------------------+----------------+

[renecannao]

So far, I can't reproduce it.

On Admin:

mysql> SET mysql-forward_autocommit='true'; SET mysql-autocommit_false_is_transaction='true'; LOAD MYSQL VARIABLES TO RUNTIME; SELECT * FROM global_variables WHERE variable_name LIKE '%autocommit%';
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+---------------------------------------+----------------+
| variable_name                         | variable_value |
+---------------------------------------+----------------+
| mysql-forward_autocommit              | true           |
| mysql-enforce_autocommit_on_reads     | false          |
| mysql-autocommit_false_not_reusable   | false          |
| mysql-autocommit_false_is_transaction | true           |
+---------------------------------------+----------------+
4 rows in set (0.00 sec)

Running the script:

$ cat 3209.py 
import MySQLdb
ip="127.0.0.1"
port=6033
#port=3306
username="sbtest"
password="sbtest"
db = MySQLdb.connect(host=str(ip), port=int(port), user=username, passwd=password, db='', connect_timeout=2)
db.select_db('information_schema')
cursor = db.cursor()
db.autocommit(True)     # not send to mysql
#db.autocommit=1       # not send to mysql
#cursor.execute("set autocommit=1")       # send to mysql 
cursor.execute("select 'test';")

$ python 3209.py 

On MySQL server general log:

2020-12-18T16:53:08.290064Z    11 Connect   sbtest@localhost on information_schema using TCP/IP
2020-12-18T16:53:08.290207Z    11 Query select 'test'

When you use proxysql you have Change user instead of Connect, that may be relevant because it is possible that the connection was left in the wrong state. I will try to investigate this further.

For further reference, this is connecting directly to MySQL:

2020-12-18T17:03:39.126119Z    13 Connect   sbtest@localhost on  using SSL/TLS
2020-12-18T17:03:39.126335Z    13 Query set autocommit=0
2020-12-18T17:03:39.126443Z    13 Init DB   information_schema
2020-12-18T17:03:39.126536Z    13 Query set autocommit=1
2020-12-18T17:03:39.126645Z    13 Query select 'test'
2020-12-18T17:03:39.128033Z    13 Quit  

Can you reproduce the same issue using default settings? Please note that we may deprecate mysql-forward_autocommit soon, as it seems incorrectly used (it creates complex and incorrect logics)

[beebol]

yes , i can reproduce the same issue "db.autocommit(True) " not send to mysql "db.autocommit(False)" send to mysql

root 01:17:  [(none)]>       select rule_id,username,flagIN,client_addr,match_digest,multiplex,apply,error_msg,log,flagOUT from runtime_mysql_query_rules;
+---------+---------------+--------+--------------+---------------------+-----------+-------+-------------------------+-----+---------+
| rule_id | username      | flagIN | client_addr  | match_digest        | multiplex | apply | error_msg               | log | flagOUT |
+---------+---------------+--------+--------------+---------------------+-----------+-------+-------------------------+-----+---------+
| 1       | NULL          | 0      | NULL         | .                   | NULL      | 0     | NULL                    | 1   | NULL    |
| 2       | NULL          | 0      | 10.10.%     | NULL                | NULL      | 0     | NULL                    | NULL | 1       |
| 3       | NULL          | 0      | 10.17.%      | NULL                | NULL      | 0     | NULL                    | NULL | 1       |
| 4       | NULL          | 0      | 10.20.%     | NULL                | NULL      | 0     | NULL                    | NULL | 1       |
| 5       | NULL          | 0      | 10.4.%    | NULL                | NULL      | 0     | NULL                    | NULL | 1       |
| 6       | NULL          | 0      | 10.1.1.138 | NULL                | NULL      | 0     | NULL                    | NULL | 1       |
| 10      | NULL          | 1      | NULL         | ^SELECT.*FOR UPDATE | NULL      | 1     | NULL                    | NULL | NULL    |
| 20      | NULL          | 1      | NULL         | ^SELECT             | NULL      | 0     | NULL                    | NULL | 2       |
| 29      | onlymaster_rw | 2      | NULL         | NULL                | NULL      | 1     | NULL                    | NULL | NULL    |
| 30      | NULL          | 2      | 10.20.%      | NULL                | NULL      | 1     | NULL                    | NULL | NULL    |
| 35      | NULL          | 2      | 10.17.%      | NULL                | NULL      | 1     | NULL                    | NULL | NULL    |
| 1000    | NULL          | 0      | NULL         | .                   | NULL      | 1     | not allow to connect db | NULL | NULL    |
+---------+---------------+--------+--------------+---------------------+-----------+-------+-------------------------+-----+---------+
12 rows in set (0.00 sec)

if deprecate mysql-forward_autocommit ,How to make set autocommit = 0 to start a transaction?

How to configure , db.autocommit (true) sent to MySQL

[beebol]

in 2.0.15 version.

root 02:16:  [(none)]> show variables like '%autocom%';
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| mysql-forward_autocommit              | true  |
| mysql-enforce_autocommit_on_reads     | false |
| mysql-autocommit_false_not_reusable   | false |
| mysql-autocommit_false_is_transaction | true  |
+---------------------------------------+-------+

set autocommit=? not sent to mysql.

in 1.4.15-1-g61bc777: set autocommit=? sent to mysql. but db.autocommit(True) not send to mysql in python. db.autocommit(False) send to mysql in python.

[renecannao]

ProxySQL tracks autocommit from client and sends it to backend when needed.