The first query after autocommit is false has no transactions

[beebol]

ProxySQL: 2.0.17

configuration:

root 19:50:  [(none)]> show variables like '%commit%';
+---------------------------------------+-------+
| 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  |
+---------------------------------------+-------+

SQL:

set autocommit=0;
select * from sbtest1 limit 1;   // It is possible to route to a read-only group
update sbtest1 set a='a1' where id = 28;
commit;

general_log:

2021-07-23T18:57:00.497617+08:00    7412284 Query   select * from sbtest1 limit 1
2021-07-23T18:57:15.673772+08:00    7412284 Query   SET autocommit=0
2021-07-23T18:57:15.674531+08:00    7412284 Query   update sbtest1 set a='a1' where id = 28
2021-07-23T18:57:40.740048+08:00    7412284 Query   commit

[renecannao]

Hi @beebol . This behaviour is controlled by mysql-enforce_autocommit_on_reads . A lot of applications (or ORMs) run all queries inside a transaction while there is no need for it, thus mysql-enforce_autocommit_on_reads is off by default.

[beebol]

Hi @renecannao. After set autocommit=0, the first query that follows is not in the primary, but is executed in the slave. From the log, you can see that this query is not in the transaction. How to make this query enter the transaction?

In version 1.4.15, when mysql-forward_autocommit is true, select query after set autocommit=0 is in transaction, but in version 2.0.17, all changes, even if mysql-forward_autocommit is set to true, set autocommit =0 is only for recording, and will not be forwarded to the backend.

[renecannao]

mysql-forward_autocommit is deprecated, its logic was complex and prone to errors. If you want the first read to be part of the transaction, you need to set mysql-enforce_autocommit_on_reads=true .

[beebol]

Hi @renecannao In the case of separation of read and write, this transaction will be in the slave, not in the primary, which will cause the subsequent write SQL to report read only.

root 09:03:  [(none)]> select rule_id,match_digest,destination_hostgroup from mysql_query_rules;
+---------+---------------------+-----------------------+
| rule_id | match_digest        | destination_hostgroup |
+---------+---------------------+-----------------------+
| 10      | ^SELECT.*FOR UPDATE | 0                     |
| 20      | ^SELECT             | 1                     |

set autocommit=0;
select * from sbtest1 limit 1;   
update sbtest1 set a='a1' where id = 28;   //  An error will be reported here: read only
commit;

[beebol]

Hi @renecannao to set mysql-enforce_autocommit_on_reads=true ,This transaction will be on the slave, which is not expected. please tell me. How to configure the first read to be part of the transaction(primary, not slave).

[IT-xiaoge]

I also encountered the same problem

[renecannao]

What are you trying to achieve exactly? You want the first SELECT to run inside the transaction, but you also configured proxysql to send all SELECTs to the replica. These configurations are clearly conflicting, you can't have both because are they are the opposite of each other

[IT-xiaoge]

The java spring framework defaults to open the transaction method by set auto comment=0

[renecannao]

It is still not clear what you want to achieve. Can you please clarify?

[beebol]

I want to achieve is that the query in the transaction is in the Primary, and the query in the non-transaction is in the slave(Read group). In version 1.4.15, the following configuration is OK. but in version 2.0.17 is Not what i expected.

root 09:03:  [(none)]> select rule_id,match_digest,destination_hostgroup from mysql_query_rules;
+---------+---------------------+-----------------------+
| rule_id | match_digest        | destination_hostgroup |
+---------+---------------------+-----------------------+
| 10      | ^SELECT.*FOR UPDATE | 0                     |
| 20      | ^SELECT             | 1                     |
root 10:00:  [(none)]> select username,default_hostgroup from mysql_users where username= 'sbtest_rw';
+-----------+-------------------+
| username  | default_hostgroup |
+-----------+-------------------+
| sbtest_rw | 0                 |
+-----------+-------------------+

set autocommit=0;
select * from sbtest1 limit 1;   
update sbtest1 set a='a1' where id = 28;   
commit;

When set autocommit=0, it should be the beginning of a transaction, and subsequent queries or updates should be in the transaction and should not be split. In the new version, because of the separation of read and write routing, the read is no longer in the transaction. Of course I know that the routing configuration here conflicts with what I want to achieve, but what should I configure?

[beebol]

hi @renecannao .

root 11:12:  [(none)]> select rule_id,match_digest,destination_hostgroup from mysql_query_rules where active = 1;
+---------+---------------------+-----------------------+
| rule_id | match_digest        | destination_hostgroup |
+---------+---------------------+-----------------------+
| 1       | .                   | NULL                  |
| 10      | ^SELECT.*FOR UPDATE | 0                     |
| 28      | NULL                | 0                     |
| 30      | NULL                | 2                     |
| 35      | NULL                | 3                     |
| 1000    | .                   | NULL                  |
+---------+---------------------+-----------------------+

root 11:13:  [(none)]> show variables like '%commit%';
+---------------------------------------+-------+
| 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  |
+---------------------------------------+-------+

sql:

sbtest_rw 11:14:  [sbtest]> set autocommit=0;
Query OK, 0 rows affected (0.02 sec)

sbtest_rw 11:14:  [sbtest]> select * from sbtest1 where id = 28;
+----+------+------+------+-----+----+----+----+----+----+------+-------------------------------------------------------------+---+----+-----+----+----+----+----+----+
| id | k    | dadc | dacd | daa | da | dc | dd | d  | a  | c    | pad                                                         | b | dk | dac | xx | ll | ld | lx | xo |
+----+------+------+------+-----+----+----+----+----+----+------+-------------------------------------------------------------+---+----+-----+----+----+----+----+----+
| 28 | 4971 | d    | d    | d   | d  | d  | dd | d2 | a6 | xxxa | 24254594429-63006752771-86115602773-26630920715-27676864358 | b | d  | d   | ad | ad | ad | ad | ad |
+----+------+------+------+-----+----+----+----+----+----+------+-------------------------------------------------------------+---+----+-----+----+----+----+----+----+
1 row in set (0.25 sec)

sbtest_rw 11:14:  [sbtest]> update sbtest1 set d='d3' where id = 28;
Query OK, 1 row affected (0.43 sec)
Rows matched: 1  Changed: 1  Warnings: 0

sbtest_rw 11:15:  [sbtest]> select * from sbtest1 where id = 28;
+----+------+------+------+-----+----+----+----+----+----+------+-------------------------------------------------------------+---+----+-----+----+----+----+----+----+
| id | k    | dadc | dacd | daa | da | dc | dd | d  | a  | c    | pad                                                         | b | dk | dac | xx | ll | ld | lx | xo |
+----+------+------+------+-----+----+----+----+----+----+------+-------------------------------------------------------------+---+----+-----+----+----+----+----+----+
| 28 | 4971 | d    | d    | d   | d  | d  | dd | d3 | a6 | xxxa | 24254594429-63006752771-86115602773-26630920715-27676864358 | b | d  | d   | ad | ad | ad | ad | ad |
+----+------+------+------+-----+----+----+----+----+----+------+-------------------------------------------------------------+---+----+-----+----+----+----+----+----+
1 row in set (0.02 sec)

sbtest_rw 11:15:  [sbtest]> commit;
Query OK, 0 rows affected (0.14 sec)

mysql general log:

2021-08-18T11:14:22.346616+08:00    7412284 Query   select * from sbtest1 where id = 28
2021-08-18T11:15:11.761767+08:00    7412284 Query   SET autocommit=0
2021-08-18T11:15:11.850613+08:00    7412284 Query   update sbtest1 set d='d3' where id = 28
2021-08-18T11:15:35.361723+08:00    7412284 Query   select * from sbtest1 where id = 28
2021-08-18T11:15:50.035097+08:00    7412284 Query   commit

The first query is after set autocommit=0, but it is before in the mysql general log, which results in the first query not in the transaction, which is problematic. How should I configure to solve this problem?

[beebol]

@renecannao is this a bug?

[srikiraju]

This behaviour is controlled by mysql-enforce_autocommit_on_reads . A lot of applications (or ORMs) run all queries inside a transaction while there is no need for it, thus mysql-enforce_autocommit_on_reads is off by default.

@renecannao Is this a good default? This means proxysql isn't always a drop-in replacement for folks who expect this to be backward compatible perfectly MySQL and expect implicit transactions with set autocommit=0 even for selects

[renecannao]

@srikiraju : I don't know if it is a good default or not, if it positively or negatively affects the majority of users. What I know by experience is that the majority of developers I worked with in the past weren't aware that ORM automatically sends set autocommit=0, that a transaction is started automatically, and they didn't see a problem with transaction running for hours or days. I am referring to days when proxysql didn't exist yet. I also know that some users here on github issues expect read-write split to still work while a transaction is started on the writer, or even worse when a transaction is started on a replica. To be honest, considering the reported issues of transactions started on replicas, I consider this default safer than the alternative. But it is all configurable, so users can tune behaviors according to their needs.

[srikiraju]

We ran into the same situation(and its probably been very good for us) - maybe some more explicit documentation would be nice :)

[v1talM]

@beebol I also met this problem, have you resolved this problem?

[v1talM]

The java spring framework defaults to open the transaction method by set auto comment=0

hi @IT-xiaoge , i wonder if you solved this problem? if yes, could you plz tell me how you fixed?

[renecannao]

Hi @v1talM . Please read all my comments in this issue. The behavior is configurable

[v1talM]

Hi @v1talM . Please read all my comments in this issue. The behavior is configurable

@renecannao thank you for your answer, I have read all above comments. But i think there might be some communication issues here:

I met the same problem as @beebol . I have two mysql server node, one is primary another is slave for read-only. And i also set up Read/Write split query rules. But when I enable and commit a transaction in the Spring framework of Java, the SQL statements within the transaction are distributed to different MySQL nodes. Transaction SQL like this: SET AUTOCOMMIT = 0; select * from test; update test set id = 1 where id = 2; COMMIT But first read query in transaction was distributed to slave node. I also know JDBC start a transaction is not explicitly. So, is there any way to ensure that all statements within the transaction are committed to the primary node?

thx

[renecannao]

Hi @v1talM .

There is no "read/write split query rules" : proxysql can be configured to send specific SELECT to reader, in a very very selective way. If you configured proxysql to send ALL reads to the replica, the behavior you see is exactly what you configured proxysql for. Stop sending all reads to replica: be very selective on what should to replica (as already explained previously in this issue).

[v1talM]

Hi @v1talM .

There is no "read/write split query rules" : proxysql can be configured to send specific SELECT to reader, in a very very selective way. If you configured proxysql to send ALL reads to the replica, the behavior you see is exactly what you configured proxysql for. Stop sending all reads to replica: be very selective on what should to replica (as already explained previously in this issue).

Thank you for your answer. I also tried this way and deleted all of them. However, I noticed that all query logs (both read and write) without any "Read/Write split query rules" were sent to the primary node. Perhaps I should modify my first read query within the transaction to SELECT ... FOR UPDATE and specify it to be sent to the primary node, while allowing the other queries starting with ^SELECT to be sent to the slave nodes, so that it functions as expected?