mysql-forward_autocommit=true being ignored since 2.0.13

[jangajdorus]

This is relevant part of my proxysql setup:

mysql-forward_autocommit = true;
mysql-autocommit_false_not_reusable = true;

proxysql 2.0.10.

set autocommit=0; as start of transaction has been working fine for me on 2.0.10 (propagated to backend server) until upgrade to Percona ProxySQL 2.0.14. After upgrade (having the same global_variables) beginning of the transactions are not sent to the backend server. See here:

(and proxysql log is right, confirmed from mysql general log.)

When I discovered the issue, I've upgraded to 2.0.15 (from https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever -> main is CentOS 7.6 x64) - still the same. Afterwards was downgrading via 2.0.13 to finally 2.0.12 where autocommit was again sent to backend (still the same config).

see here:

Some relevant info:

• [ ] ProxySQL version affected: 2.0.13 +
• [ ] OS version: CentOS 7.6 x86-64
• [ ] The steps to reproduce: turn on mysql-forward_autocommit and mysql-autocommit_false_not_reusable. (Actually its probably just about mysql-forward_autocommit)
• snippets from proxylog attached above

.. not familiar with proxysql code, however this seems suspicious:

[renecannao]

Hi @jangajdorus. In 2.0.13 we implemented several improvements in how SET statements are processed, and we also added support for savepoints. It is possible that one of the changes inadvertently changed the behavior of mysql-forward_autocommit. Considering that we planned to deprecated this variable from quite long time, can I ask what is the real problem? I understand set autocommit=0 isn't sent in your case, but what is the problem you are facing? Also note that set autocommit=0 doesn't start a transaction: a transaction is started on the next query involving a transactional table. In the screenshot you attached I see a SELECT statement after set autocommit=0 : maybe what you need isn't mysql-forward_autocommit = true, but mysql-enforce_autocommit_on_reads=true .

[jangajdorus]

Hi @renecannao, the problem is we have bunch of quite long transactions (one of them order submit), where (with help of Java Spring libraries) also amqp mesage send is involved (not in all transactions but lets take this for example). So if amqp message is not sent successfully for whatever reason, we simply rollback current transaction (and in our example order is not confirmed). As said, sometimes we need to do rollback (and rollback is correctly sent to backend db) but without set autocommit=0; being propagated to backend db rollback doesnt have any effect (well, as expected). Therefore we need to propagate 'set autocommit=0;` to destined mysql backend.

The sample I attached was just from my testing, transactions are much longer - yet we use mysql-enforce_autocommit_on_reads=true as well.

From the code (didn't investigate further so I might be wrong) seems to me since 2.0.13 when mysql session is created, you set mysql-forward_autocommit=false without checking the global settings (in constructor) and perhaps without being changed later somewhere to correctly load global setting of mysql-forward_autocommit.