Closed
Description
My Java application have the following transaction statement, but I found the select is send to the slave, it should be send to the master:
set autocommit = 0;
select version from t where version = xx;
delete from t where version = xx;
commit;
env:
version: proxysql 1.4.3
+-- write group: 1
+-- read group: 2
os: Centos 6.5
mysql: percona-server 5.6.21
the query rule is:
mysql_query_rules:
(
{
rule_id=1
active=1
match_pattern="SELECT.+FOR.+UPDATE$"
destination_hostgroup=1
apply=1
re_modifiers="CASELESS"
},
{
rule_id=2
active=1
match_pattern="^SELECT"
destination_hostgroup=2
apply=1
re_modifiers="CASELESS"
}
)
and I have enable the following variables:
mysql-forward_autocommit 1
the user's transaction_persistent value is 1 which in mysql_users table.
Metadata
Metadata
Assignees
Type
Projects
Relationships
Development
No branches or pull requests
Participants
Activity
arstercz commentedon Nov 22, 2017
I have read this google groups, but there are many framework(such as Spring) use
set autocommit = 0; xxx; committo make a transaction.renecannao commentedon Nov 22, 2017
Hi @arstercz .
As you probably already know from reading in the mailing list,
autocommit=0doesn't start a transaction, therefore proxysql "not treating autocommit=0 as a transaction" is technically correct, because there is no transaction yet.I am planning to implement this feature request in 1.4.4 .
Thanks
arstercz commentedon Nov 22, 2017
@renecannao yes, I have reading the mailing list, our own application will use
beginorstart transactionto start a transaction. I'm looking forward to the v1.4.4 version. thanks.Added variable autocommit_false_is_transaction #1256
renecannao commentedon Nov 30, 2017
Added new variable
mysql-autocommit_false_is_transactionIf
mysql-autocommit_false_is_transaction=true(falseby default), a connection withautocommit=0is treated as a transaction.If
forward_autocommit=true(falseby default), the same behavior applies.gmelikov commentedon Sep 17, 2020
@renecannao could you please describe why the default is
mysql-autocommit_false_is_transaction=false?If I understand correctly, default MySQL behavior is equal to
mysql-autocommit_false_is_transaction=true.