[MXS-3895]  Route Select / Read only to Slave node when using readwritesplit Created: 2021-11-25  Updated: 2022-01-04  Resolved: 2022-01-04

Status: Closed
Project: MariaDB MaxScale
Component/s: readwritesplit
Affects Version/s: 2.5.15
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Han-Nung Hsu Assignee: Unassigned
Resolution: Incomplete Votes: 1
Labels: Maxscale, configuration, readwritesplit
Environment:

CentOS 7.9.2009
MariaDB 10.5.10

Attachments: PNG File 圖片1.png     PNG File 圖片2.png    

 Description   

Hi all,
According to default setting of readwritesplit, no reads are sent to the master.
However, when I check InnoDB row read metrics on percona, I find that read are sent to master node.
Moreover, when I close slave node for HA test, I find there are no error occur(suppose there may have some error during failover).

How to route read query only to node slave when I using readwritesplit routing?

This is my maxscale configuration:

[server1]
 
type=server
 
address=<%=scope.function_gclookup(['Server1IP'])%>
 
port=<%=scope.function_gclookup(['Server1Port'])%>
 
protocol=MariaDBBackend
 
 
 
[server2]
 
type=server
 
address=<%=scope.function_gclookup(['Server2IP'])%>
 
port=<%=scope.function_gclookup(['Server2Port'])%>
 
protocol=MariaDBBackend
 
 
 
[Replication-Monitor]
 
type=monitor
 
module=mariadbmon
 
servers=server1,server2
 
user=maxscale
 
password=[password]
 
monitor_interval=2000
 
auto_failover=true
 
auto_rejoin=true
 
 
 
[Read-Only-Service]
 
type=service
 
router=readconnroute
 
servers=server1,server2
 
user=maxscale
 
password=[password]
 
router_options=slave
 
 
 
[Read-Write-Service]
 
type=service
 
router=readwritesplit
 
servers=server1,server2
 
user=maxscale
 
password=[password]
 
 
 
[Read-Only-Listener]
 
type=listener
 
service=Read-Only-Service
 
protocol=MariaDBClient
 
port=4008
 
 
 
[Read-Write-Listener]
 
type=listener
 
service=Read-Write-Service
 
protocol=MariaDBClient
 
port=1433


# maxctrl show service:
 
...
 
{​​​​                                                           │
 
│                     │     "auth_all_servers": false,                              │
 
│                     │     "causal_reads": "false",                                │
 
│                     │     "causal_reads_timeout": 10000,                          │
 
│                     │     "cluster": null,                                        │
 
│                     │     "connection_keepalive": 300,                            │
 
│                     │     "connection_timeout": 0,                                │
 
│                     │     "delayed_retry": false,                                 │
 
│                     │     "delayed_retry_timeout": 10000,                         │
 
│                     │     "disable_sescmd_history": false,                        │
 
│                     │     "enable_root_user": false,                              │
 
│                     │     "lazy_connect": false,                                  │
 
│                     │     "localhost_match_wildcard_host": true,                  │
 
│                     │     "log_auth_warnings": true,                              │
 
│                     │     "master_accept_reads": false,                           │
 
│                     │     "master_failure_mode": "fail_instantly",                │
 
│                     │     "master_reconnection": false,                           │
 
│                     │     "max_connections": 0,                                   │
 
│                     │     "max_sescmd_history": 50,                               │
 
│                     │     "max_slave_connections": "255",                         │
 
│                     │     "max_slave_replication_lag": 0,                         │
 
│                     │     "net_write_timeout": 0,                                 │
 
│                     │     "optimistic_trx": false,                                │
 
│                     │     "password": "*****",                                    │
 
│                     │     "prune_sescmd_history": false,                          │
 
│                     │     "rank": "primary",                                      │
 
│                     │     "retain_last_statements": -1,                           │
 
│                     │     "retry_failed_reads": true,                             │
 
│                     │     "router_options": null,                                 │
 
│                     │     "session_trace": false,                                 │
 
│                     │     "session_track_trx_state": false,                       │
 
│                     │     "slave_connections": 255,                               │
 
│                     │     "slave_selection_criteria": "LEAST_CURRENT_OPERATIONS", │
 
│                     │     "strict_multi_stmt": false,                             │
 
│                     │     "strict_sp_calls": false,                               │
 
│                     │     "strip_db_esc": true,                                   │
 
│                     │     "targets": null,                                        │
 
│                     │     "transaction_replay": false,                            │
 
│                     │     "transaction_replay_attempts": 5,                       │
 
│                     │     "transaction_replay_max_size": "1073741824",            │
 
│                     │     "transaction_replay_retry_on_deadlock": false,          │
 
│                     │     "use_sql_variables_in": "all",                          │
 
│                     │     "user": "maxscale",                                     │
 
│                     │     "version_string": null                                  │
 
│                     │ }​​​​



 Comments   
Comment by markus makela [ 2021-11-25 ]

Whether reads are routed to the slave node depends on whether the read is done inside of a transaction or not. If it is done outside of a transaction with autocommit enabled, it can be routed to a slave server. If the read is done inside a read-only transaction (i.e. START TRANSACTION READ ONLY) then it also can be routed to a slave server. All reads done inside read-write transactions must be routed to the master server to retain the guarantee that all the statement in the transaction use the same transaction snapshot.

Comment by markus makela [ 2021-12-08 ]

I'd recommend adding log_info=true under the [maxscale] section to enable the verbose log level. This should show you why reads are routed to the current master server.

Comment by markus makela [ 2022-01-04 ]

Not enough information to deduce whether this happens due to an open transaction or whether this is an actual bug. HansHsu please update the issue with steps to reproduce so that we can verify whether this is a bug or not.

Generated at Thu Feb 08 04:24:43 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.