A weird question about autocommit and for update

[lazzyfu]

hello:

When the java program executes the following statement：

SET autocommit=0; select id from u_personal_info p where p.id_number=123422 for update;

An error occurred： 2018-01-19 15:29:39 MySQL_Session.cpp:2763:handler(): [WARNING] Error during query on (11,10.71.223.32,3306): 1290, The MySQL server is running with the --read-only option so it cannot execute this statement

mysql_query_rules configuration is as follows: INSERT INTO mysql_query_rules(rule_id,active,match_pattern, destination_hostgroup,log,apply) VALUES (1,1,'^SELECT.*FOR UPDATE',10,1,1); INSERT INTO mysql_query_rules(rule_id,active,match_pattern,destination_hostgroup,log,apply) VALUES (1000,1,'^SELECT',11,1,1);

group 10 : write group group 11: reader group 10.71.223.32 is slave and is a member of group 11

I do not understand setting autocommit, this statement does not match mysql_query_rules.

Please help, thanks.

[lazzyfu]

Open a mysql terminal, execute the above statement (copy and paste ), for update statement will be sent to the slave, not the master

[renecannao]

hi @lazzyfu . In the issue template it is specified:

If you are submitting a bug report, please provide a clear description of your issue, the version of OS and ProxySQL, every step to reproduce the issue, and the error log. If it is a crashing bug, a core dump will be extremely useful.

What is missing that I think is very relevant?

• ProxySQL version
• error log

I just tried this on latest 1.4.5 , and I can't reproduce the issue. Maybe it is an old bug? I don't know, because you aren't telling which version you are using.

[lazzyfu]

hi @renecannao ProxySQL Version : 1.4.3-1-g79a32d3 Os Version: centos 7.2

Proceed as follows

step1: Proxysql Setting: Admin> select * from mysql_replication_hostgroups; +------------------+------------------+----------+ | writer_hostgroup | reader_hostgroup | comment | +------------------+------------------+----------+ | 10 | 11 | RW Split | +------------------+------------------+----------+

Admin> select hostgroup_id,hostname,port,status from mysql_servers; +--------------+--------------+------+--------+ | hostgroup_id | hostname | port | status | +--------------+--------------+------+--------+ | 10 | 10.74.243.36 | 3306 | ONLINE | | 11 | 10.74.243.42 | 3306 | ONLINE | | 11 | 10.74.243.36 | 3306 | ONLINE | +--------------+--------------+------+--------+

Admin> select rule_id,active,username,match_pattern,destination_hostgroup,log,apply from mysql_query_rules; +---------+--------+----------+---------------------+-----------------------+-----+-------+ | rule_id | active | username | match_pattern | destination_hostgroup | log | apply | +---------+--------+----------+---------------------+-----------------------+-----+-------+ | 1 | 1 | fzf | ^SELECT.*FOR UPDATE | 10 | 1 | 1 | | 1000 | 1 | fzf | ^SELECT | 11 | 1 | 1 | +---------+--------+----------+---------------------+-----------------------+-----+-------+ 2 rows in set (0.00 sec)

Admin> show variables like '%version%'; +----------------------+------------------+ | Variable_name | Value | +----------------------+------------------+ | admin-version | 1.4.3-1-g79a32d3 | | mysql-server_version | 5.7.19 | +----------------------+------------------+

**Connect ProxySQL and Execute the following SQL [root@db-42 ~]# mysql -ufzf -p123.com -h10.74.243.42 -P6033 test (fzf@10.74.243.42)[stage_ccoopfs]> SET autocommit=0; Query OK, 0 rows affected (0.01 sec)

(fzf@10.74.243.42)[stage_ccoopfs]> select
-> id -> from u_personal_info p -> where p.id_number=123422 -> for update; ERROR 2013 (HY000): Lost connection to MySQL server during query

** ProxySQL err log ... 2018-01-22 09:17:33 MySQL_Session.cpp:2763:handler(): [WARNING] Error during query on (11,10.74.243.42,3306): 1290, The MySQL server is running with the --read-only option so it cannot execute this statement 2018-01-22 09:17:33 MySQL_Session.cpp:2782:handler(): [WARNING] Retrying query. 2018-01-22 09:17:33 MySQL_Session.cpp:2763:handler(): [WARNING] Error during query on (11,10.74.243.42,3306): 1290, The MySQL server is running with the --read-only option so it cannot execute this statement

The specified for update statement is routed to slave(group 11), with no matching rules. If the set autocommit is changed to begin, there is no problem

[renecannao]

(fzf@10.74.243.42)[stage_ccoopfs]> select -> id -> from u_personal_info p -> where p.id_number=123422 -> for update;

Here is the issue. You are using newlines, and the regex on match_pattern won't work. Try match_digest instead.

[lazzyfu]

Thanks, It is good to use match_digest. What is the difference between match_pattern and match_digest ?

[lazzyfu]

match_digest : it matches the regular expression again the digest of the query, as represented in stats_mysql_query_digest.query_digest match_pattern : it matches the regular expression again the unmodified text of the query

thanks.