Queries[SELECT] part of transactions are not going to the Writer/Primary and routing to other secondaries

[aniljoshi2022]

Hi Rene,

I see we already have some bugs mentioning the behaviour of queries(SELECT) redirections, which should have been expected to land on the Primary/Writer and not on the secondary.

[https://github.com/sysown/proxysql/issues/2391] & [https://github.com/sysown/proxysql/issues/2572]

Well, it appears this was working in some older versions, say [1.4] and not showing the same expected behaviour in the 2.x release.

mysql-forward_autocommit = true it in ProxySQL version 2.0.10-27-g5b31997，not work ，but in ProxySQL version 1.4.16-23-gf954ef3，work fine！

For your reference, I tried the below queries and the simple SELECT with autocommit=false landing on another secondaries.

for i in {1..1000}; do mysql -utest -pTest@1234 -h 127.0.0.1 -P 6033 -BNe 'SET autocommit=false; SELECT @@server_id'; done|sort|uniq -c;

486 2887717378
514 2887717381

for i in {1..1000}; do mysql -utest -pTest@1234 -h 127.0.0.1 -P 6033 -BNe 'SET autocommit=0; SELECT @@server_id'; done|sort|uniq -c;

  480 2887717378
    520 2887717381

for i in {1..1000}; do mysql -utest -pTest@1234 -h 127.0.0.1 -P 6033 -BNe 'SET autocommit=1; SELECT @@server_id'; done|sort|uniq -c;

466 2887717378
    534 2887717381

Here I tried with the commit however still it shows the same behaviour.

mysql -utest -pTest@1234 -h 127.0.0.1 -P 6033 -BNe 'SET autocommit=false; select count(1)from mysql.user; commit';

mysql> select hostgroup, digest_text, count_star from stats_mysql_query_digest;
+-----------+----------------------------------+------------+
| hostgroup | digest_text                      | count_star |
+-----------+----------------------------------+------------+
| 0         | commit                           | 1          |
**| 1         | select count(?)from mysql.user   | 2          |**
| 0         | select @@version_comment limit ? | 2          |
| 0         | SET autocommit=false             | 2          |
+-----------+----------------------------------+------------+
4 rows in set (0.00 sec)

I was having below configurations.

mysql> SHOW VARIABLES WHERE Variable_name='mysql-forward_autocommit'; 
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| mysql-forward_autocommit | false |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES WHERE Variable_name='mysql-enforce_autocommit_on_reads'; 
+-----------------------------------+-------+
| Variable_name                     | Value |
+-----------------------------------+-------+
| mysql-enforce_autocommit_on_reads | false |
+-----------------------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES WHERE Variable_name='mysql-autocommit_false_not_reusable'; 
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| mysql-autocommit_false_not_reusable | false |
+-------------------------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW VARIABLES WHERE Variable_name='mysql-autocommit_false_is_transaction'; 
+---------------------------------------+-------+
| Variable_name                         | Value |
+---------------------------------------+-------+
| mysql-autocommit_false_is_transaction | false  |
+---------------------------------------+-------+
1 row in set (0.00 sec)

Well I tried a few tweaking to see if the behaviour changes however the outcome was same.

UPDATE global_variables SET variable_value='TRUE' WHERE variable_name='mysql-forward_autocommit';
UPDATE global_variables SET variable_value='FALSE' WHERE variable_name='mysql-enforce_autocommit_on_reads';
UPDATE global_variables SET variable_value='TRUE' WHERE variable_name='mysql-autocommit_false_not_reusable';
UPDATE global_variables SET variable_value='TRUE' WHERE variable_name='mysql-autocommit_false_is_transaction';

One more point, as per documentation, setting autocommit to 0 should turn the session into a transaction. However, after reviewing the code, it looks like it's not able to detect that autocommit is false or 0 which looks like some buggy behaviour .

mysql_connection.cpp

   2541 bool MySQL_Connection::IsActiveTransaction() {
   2542         bool ret=false;
   2543         if (mysql) {
   2544                 ret = (mysql->server_status & SERVER_STATUS_IN_TRANS);
   2545                 if (ret == false && (mysql)->net.last_errno && unknown_transaction_status == true) {
   2546                         ret = true;
   2547                 }
   2548                 if (ret == false) {
   2549                         //bool r = ( mysql_thread___autocommit_false_is_transaction || mysql_thread___forward_autocommit ); // deprecated , see #3253
   2550                         bool r = ( mysql_thread___autocommit_false_is_transaction);
   2551                         if ( r && (IsAutoCommit() == false) ) {
   2552                                 ret = true;
   2553                         }
   2554                         ret = false;
   2555                 }
   2556                 // in the past we were incorrectly checking STATUS_MYSQL_CONNECTION_HAS_SAVEPOINT
   2557                 // and returning true in case there were any savepoint.
   2558                 // Although flag STATUS_MYSQL_CONNECTION_HAS_SAVEPOINT was not reset in
   2559                 // case of no transaction, thus the check was incorrect.
   2560                 // We can ignore STATUS_MYSQL_CONNECTION_HAS_SAVEPOINT for multiplexing
   2561                 // purpose in IsActiveTransaction() because it is also checked
   2562                 // in MultiplexDisabled()
   2563         }
   2564         return ret;
   2565 }

```gdb:

(gdb) break mysql_connection.cpp:2551
Breakpoint 1 at 0x9007c7: file mysql_connection.cpp, line 2551.
(gdb) continue
Continuing.
[New Thread 0x7f65f02df640 (LWP 80926)]
[Thread 0x7f65f02df640 (LWP 80926) exited]
[Switching to Thread 0x7f65fb3fe640 (LWP 77857)]

Thread 5 "proxysql" hit Breakpoint 1, MySQL_Connection::IsActiveTransaction (this=0x7f65f5780c00) at mysql_connection.cpp:2551
2551    mysql_connection.cpp: No such file or directory.
(gdb) p mysql_thread___autocommit_false_is_transaction
$1 = true
(gdb) p IsAutoCommit()
$2 = true

Please let me know if you need any more details/information.

[aniljoshi2022]

@renecannao I'll appreciate your response on this ?

Thank you, very much! Anil

[renecannao]

Hi @aniljoshi2022 .

According to Github new issue template, where is the configuration? According to new issue template, this issue is likely to be closed (without reply).

which should have been expected to land on the Primary/Writer and not on the secondary

Can you explain this expectation? What did you configure to send these queries on the primary?

[aniljoshi2022]

@renecannao

Let me add the configuration details for better clarity.

`mysql> select * from mysql_servers;
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname   | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | 172.28.0.2 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.28.0.3 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.28.0.4 | 3306 | 0         | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)

mysql> select * from mysql_replication_hostgroups;
+------------------+------------------+------------+---------+
| writer_hostgroup | reader_hostgroup | check_type | comment |
+------------------+------------------+------------+---------+
| 0                | 1                | read_only  |         |
+------------------+------------------+------------+---------+
1 row in set (0.00 sec)

mysql> select * from mysql_query_rules;
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| rule_id | active | username | schemaname | flagIN | client_addr | proxy_addr | proxy_port | digest | match_digest | match_pattern        | negate_match_pattern | re_modifiers | flagOUT | replace_pattern | destination_hostgroup | cache_ttl | cache_empty_result | cache_timeout | reconnect | timeout | retries | delay | next_query_flagIN | mirror_flagOUT | mirror_hostgroup | error_msg | OK_msg | sticky_conn | multiplex | gtid_from_hostgroup | log | apply | attributes | comment |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
| 3       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SELECT.*FOR UPDATE$ | 0                    | CASELESS     | NULL    | NULL            | 0                     | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     |            | NULL    |
| 4       | 1      | NULL     | NULL       | 0      | NULL        | NULL       | NULL       | NULL   | NULL         | ^SELECT              | 0                    | CASELESS     | NULL    | NULL            | 1                     | NULL      | NULL               | NULL          | NULL      | NULL    | NULL    | NULL  | NULL              | NULL           | NULL             | NULL      | NULL   | NULL        | NULL      | NULL                | NULL | 1     |            | NULL    |
+---------+--------+----------+------------+--------+-------------+------------+------------+--------+--------------+----------------------+----------------------+--------------+---------+-----------------+-----------------------+-----------+--------------------+---------------+-----------+---------+---------+-------+-------------------+----------------+------------------+-----------+--------+-------------+-----------+---------------------+-----+-------+------------+---------+
2 rows in set (0.00 sec)

The point is if I do an explicit begin like as below, then the query route to the Writer node while with other options as i mentioned in my initial post [autocommit=false/0] routing the same to only Readers node. Isn't the expectation that the queries with [autocommit=false/0] should route to the Writer node as part of the transaction ?

for i in {1..100}; do mysql -umonitor -pMonitor@1234 -h 127.0.0.1 -P 6033 -BNe 'begin; SELECT @@server_id'; done|sort|uniq -c; Output:

 mysql: [Warning] Using a password on the command line interface can be insecure.
    100 2887516162

vs

for i in {1..100}; do mysql -umonitor -pMonitor@1234 -h 127.0.0.1 -P 6033 -BNe 'SET autocommit=false; SELECT @@server_id'; done|sort|uniq -c;

 49 2887516163
     51 2887516164

OR

for i in {1..100}; do mysql -umonitor -pMonitor@1234 -h 127.0.0.1 -P 6033 -BNe 'SET autocommit=0; SELECT @@server_id'; done|sort|uniq -c; 

 37 2887516163
     63 2887516164

Thank you, Anil

[renecannao]

Why are you using these query rules? ProxySQL documentation clearly states to not use these query rules for production.

The point is if I do an explicit begin like as below, then the query route to the Writer node

According to your query rules, BEGIN will be routed to writer hostgroup.

... other options as i mentioned in my initial post [autocommit=false/0] routing the same to only Readers node

autocommit=0 is not routed : ProxySQL will remember that client wants to use autocommit=0. SELECT is routed!

Isn't the expectation that the queries with [autocommit=false/0] should route to the Writer node as part of the transaction ?

There is no transaction after running autocommit=0 . A transaction starts after querying a transactional table if autocommit=0 . Thus, be careful when routing all SELECTs to replicas: you risk to start transactions on the replicas.