search

sysown / proxysql

High-performance MySQL proxy with a GPL license.
http://www.proxysql.com
GNU General Public License v3.0
5.96k stars 972 forks source link

Using mysql-autocommit_false_is_transaction to true does not send selects to primary #2391

Open SourajitBasak opened 4 years ago

SourajitBasak commented 4 years ago

We have a configuration where hostgroup = 10 is the primary and hostgroup = 20 is the secondary (read replica)

+--------------+-----------------------------------------------------------------+
| hostgroup_id | hostname                                                        |
+--------------+-----------------------------------------------------------------+
| 10           | xxxxxxxxxxxxx |
| 20           | xxxxxxxxxxxxx |
+--------------+-----------------------------------------------------------------+

Following are the query rules. 

+---------+--------+----------+----------------------+-----------------------+-------+
| rule_id | active | username | match_digest         | destination_hostgroup | apply |
+---------+--------+----------+----------------------+-----------------------+-------+
| 50      | 1      | NULL     | ^SELECT.*FOR UPDATE$ | 10                    | 1     |
| 51      | 1      | NULL     | ^SELECT              | 20                    | 1     |
+---------+--------+----------+----------------------+-----------------------+-------+

Following are the necessary variables .

+---------------------------------------+----------------+
| variable_name                         | variable_value |
+---------------------------------------+----------------+
| mysql-forward_autocommit              | true           |
| mysql-enforce_autocommit_on_reads     | false          |
| mysql-autocommit_false_not_reusable   | true           |
| mysql-autocommit_false_is_transaction | true           |
+---------------------------------------+----------------+

In MySQL global auto-commit is set to ON. 

SHOW VARIABLES WHERE Variable_name='autocommit';
'autocommit', 'ON'

Test 1: When we issue the follow commands.

SET autocommit = 0;
select * from tbl_plans;
commit;

The select query is routed the read replica (20). We were expecting all queries to be routed to primary (10) Is this the expected behavior ? Are we missing some configuration ? We are on v2.0.8.

 select hostgroup, digest_text, count_star from stats_mysql_query_digest;
+-----------+-------------------------+------------+
| hostgroup | digest_text             | count_star |
+-----------+-------------------------+------------+
| 10        | commit                  | 1          |
| 20        | select * from tbl_plans | 1          |
| 10        | SET autocommit = ?      | 1          |
+-----------+-------------------------+------------+
renecannao commented 4 years ago

Set mysql-enforce_autocommit_on_reads=true

SourajitBasak commented 4 years ago

Did as you suggested.

Here is the command we are using to set the variable. UPDATE global_variables SET variable_value='true' where variable_name='mysql-enforce_autocommit_on_reads'; After that we always issue the following. LOAD ADMIN VARIABLES TO RUNTIME;

As a confirmation, these are the necessary variables. 

+---------------------------------------+----------------+
| variable_name                         | variable_value |
+---------------------------------------+----------------+
| mysql-forward_autocommit              | true           |
| mysql-enforce_autocommit_on_reads     | true           |
| mysql-autocommit_false_not_reusable   | true           |
| mysql-autocommit_false_is_transaction | true           |
+---------------------------------------+----------------+

After that we flushed the stat digest table. We executed the same three queries as listed above. Still the select is getting send to read replica. 

+-----------+-------------------------+------------+
| hostgroup | digest_text             | count_star |
+-----------+-------------------------+------------+
| 10        | commit                  | 1          |
| 20        | select * from tbl_plans | 1          |
| 10        | SET autocommit = ?      | 1          |
+-----------+-------------------------+------------+
renecannao commented 4 years ago

After that we always issue the following. LOAD ADMIN VARIABLES TO RUNTIME;

It must be LOAD MYSQL VARIABLES TO RUNTIME

SourajitBasak commented 4 years ago

Used the suggested command. After that all the variables were set to true. 

+---------------------------------------+----------------+
| variable_name                         | variable_value |
+---------------------------------------+----------------+
| mysql-forward_autocommit              | true           |
| mysql-enforce_autocommit_on_reads     | true           |
| mysql-autocommit_false_not_reusable   | true           |
| mysql-autocommit_false_is_transaction | true           |
+---------------------------------------+----------------+

Same queries executed as above. Still all of those are send to 20 (i.e. read replica)

+-----------+-------------------------+------------+
| hostgroup | digest_text             | count_star |
+-----------+-------------------------+------------+
| 20        | commit                  | 1          |
| 20        | select * from tbl_plans | 1          |
| 20        | SET autocommit = ?      | 1          |
+-----------+-------------------------+------------+
SourajitBasak commented 4 years ago

Any update on this issue ?

We have tried setting both combinations mysql-forward_autocommit/mysql-autocommit_false_not_reusable=true and mysql-autocommit_false_is_transaction=true but in both cases the SELECT is sent to read hostgroup.

Our system is a mix of reads or writes. Our objective is to send operations involving only SELECTS to the read nodes and everything else to master. Everything else will have combination of SELECT, INSERT, UPDATE. We are using Spring framework and have no control over the way a transaction is started; Spring uses SET autocommit = 0. For only SELECT we instruct Spring not to start any explicit transaction.

SourajitBasak commented 4 years ago

One more observation. Test 1:

set autocommit = 0;
insert ..
select …
commit;

In the above all the statements are send to master. But if we change the order, then the SELECT goes to read replica and insert to master.

set autocommit = 0;
select …
insert ..
commit;
nigel889 commented 4 years ago

I have same issuse!

jhriggs commented 4 years ago

For this to behave as you expect, wouldn't it also require transaction_persistent = 1 for the user(s) in question?:

transaction_persistent - if this is set for the user with which the MySQL client is connecting to ProxySQL (thus a "frontend" user - see below), transactions started within a hostgroup will remain within that hostgroup regardless of any other rules

moliniao999 commented 2 years ago

I have the same problem, is there a solution?

moliniao999 commented 2 years ago

We have a configuration where hostgroup = 10 is the primary and hostgroup = 20 is the secondary (read replica)

+--------------+-----------------------------------------------------------------+
| hostgroup_id | hostname                                                        |
+--------------+-----------------------------------------------------------------+
| 10           | xxxxxxxxxxxxx |
| 20           | xxxxxxxxxxxxx |
+--------------+-----------------------------------------------------------------+

Following are the query rules.

+---------+--------+----------+----------------------+-----------------------+-------+
| rule_id | active | username | match_digest         | destination_hostgroup | apply |
+---------+--------+----------+----------------------+-----------------------+-------+
| 50      | 1      | NULL     | ^SELECT.*FOR UPDATE$ | 10                    | 1     |
| 51      | 1      | NULL     | ^SELECT              | 20                    | 1     |
+---------+--------+----------+----------------------+-----------------------+-------+

Following are the necessary variables .

+---------------------------------------+----------------+
| variable_name                         | variable_value |
+---------------------------------------+----------------+
| mysql-forward_autocommit              | true           |
| mysql-enforce_autocommit_on_reads     | false          |
| mysql-autocommit_false_not_reusable   | true           |
| mysql-autocommit_false_is_transaction | true           |
+---------------------------------------+----------------+

In MySQL global auto-commit is set to ON.

SHOW VARIABLES WHERE Variable_name='autocommit';
'autocommit', 'ON'

Test 1: When we issue the follow commands.

SET autocommit = 0;
select * from tbl_plans;
commit;

The select query is routed the read replica (20). We were expecting all queries to be routed to primary (10) Is this the expected behavior ? Are we missing some configuration ? We are on v2.0.8.

 select hostgroup, digest_text, count_star from stats_mysql_query_digest;
+-----------+-------------------------+------------+
| hostgroup | digest_text             | count_star |
+-----------+-------------------------+------------+
| 10        | commit                  | 1          |
| 20        | select * from tbl_plans | 1          |
| 10        | SET autocommit = ?      | 1          |
+-----------+-------------------------+------------+

hello, I have the same problem, is there a solution?

moliniao999 commented 2 years ago

hello，I have the same problem, is there a solution?

LuDell commented 2 years ago

I have the same problem. Is there a solution now?

v1talM commented 1 year ago

Hi @LuDell @moliniao999 @nigel889 @SourajitBasak you guys, 2 years later... Is there a solution? 😂

sbstnpl1 commented 4 months ago

We have the same issue on 2.5.5(-percona-1.1). ProxySQL is used by mybatis, which organizes transactions by autocommit= (instead of BEGIN TRANSACTION). We've set:

mysql-autocommit_false_is_transaction true mysql-enforce_autocommit_on_reads false

unfortunately, we still see many transactions on the reader host group. We would love to have a fix/workaround for this :-)

renecannao commented 4 months ago

Probably you have some other issue, like sending all reads to reader by default (instead of being selective on what to send), and maybe you also have some lock on hostgroup (error log should show that)