Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

proxysql does not treat autocommit=0 as a transaction #1256

Closed
arstercz opened this issue Nov 22, 2017 · 4 comments
Assignees
Milestone

Comments

@arstercz
Copy link

@arstercz arstercz commented Nov 22, 2017

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.

@arstercz

This comment has been minimized.

Copy link
Author

@arstercz arstercz commented Nov 22, 2017

I have read this google groups, but there are many framework(such as Spring) use set autocommit = 0; xxx; commit to make a transaction.

@renecannao

This comment has been minimized.

Copy link
Contributor

@renecannao renecannao commented Nov 22, 2017

Hi @arstercz .
As you probably already know from reading in the mailing list, autocommit=0 doesn'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

@renecannao renecannao added this to the v1.4.4 milestone Nov 22, 2017
@renecannao renecannao self-assigned this Nov 22, 2017
@arstercz

This comment has been minimized.

Copy link
Author

@arstercz arstercz commented Nov 22, 2017

@renecannao yes, I have reading the mailing list, our own application will use begin or start transaction to start a transaction. I'm looking forward to the v1.4.4 version. thanks.

renecannao added a commit that referenced this issue Nov 29, 2017
If autocommit_false_is_transaction=true (false by default), a connection
with autocommit=0 is treated as a transaction.

If forward_autocommit=true (false by default), the same behavior applies.
@renecannao

This comment has been minimized.

Copy link
Contributor

@renecannao renecannao commented Nov 30, 2017

Added new variable mysql-autocommit_false_is_transaction

If mysql-autocommit_false_is_transaction=true (false by default), a connection with autocommit=0 is treated as a transaction.
If forward_autocommit=true (false by default), the same behavior applies.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
2 participants
You can’t perform that action at this time.