[MXS-2962] Output of 'status' wrong when connected to a backend server via schema router. Created: 2020-04-12  Updated: 2020-10-12  Resolved: 2020-10-12

Status: Closed
Project: MariaDB MaxScale
Component/s: schemarouter
Affects Version/s: 2.4.8
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Oy5p aegh Assignee: markus makela
Resolution: Cannot Reproduce Votes: 0
Labels: need_feedback
Environment:

Debian 10 (Buster)
MariaDB 10.4.12


 Description   

When accessing a database which only resides on srv02 with

mariadb -h srv02.addr.tld -u dbuser02 -p -D srv02_db02

... and executing the 'status' command. The Output of the command shows the status of srv01 instead of srv02.

/var/log/maxscale/maxscale.log

2020-04-12 20:55:38   notice : Server changed state: srv01[10.0.0.1:3306]: new_master. [Running] -> [Master, Running]

-

maxctrl list servers
 
 
 
┌─────────┬───────────────┬──────┬─────────────┬─────────────────┬─────────────────┐
 
│ Server  │ Address       │ Port │ Connections │ State           │ GTID            │
 
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼─────────────────┤
 
│ srv01   │ 10.0.0.1      │ 3306 │ 0           │ Master, Running │ 0-1-200         │
 
├─────────┼───────────────┼──────┼─────────────┼─────────────────┼─────────────────┤
 
│ srv02   │ 10.0.0.2      │ 3306 │ 0           │ Master, Running │ 0-2-450         │
 
└─────────┴───────────────┴──────┴─────────────┴─────────────────┴─────────────────┘

maxscale.cnf

[maxscale]
 
threads=auto
 
 
 
[srv01]
 
type=server
 
address=10.0.0.1
 
port=3306
 
protocol=MariaDBBackend
 
 
 
[srv02]
 
type=server
 
address=10.0.0.2
 
port=3306
 
protocol=MariaDBBackend
 
 
 
[srv01-monitor]
 
type=monitor
 
module=mariadbmon
 
servers=srv01
 
user=maxscale_monitor
 
password=password
 
monitor_interval=2000ms
 
 
 
[srv02-monitor]
 
type=monitor
 
module=mariadbmon
 
servers=srv02
 
user=maxscale_monitor
 
password=password
 
monitor_interval=2000ms
 
 
 
[schema-service]
 
type=service
 
router=schemarouter
 
servers=srv01,srv02
 
user=maxscale
 
password=password
 
auth_all_servers=1
 
 
 
[schema-listener]
 
type=listener
 
service=schema-service
 
protocol=MariaDBClient
 
port=3306



 Comments   
Comment by markus makela [ 2020-04-14 ]

I believe this is expected behavior as the SHOW STATUS command doesn't depend on a database so it picks the least used candidate it finds which happens to be srv1. I think we could tune the behavior so that it if a default database is given, these types of "trivial" queries would be preferably routed to servers which have the default database.

Comment by markus makela [ 2020-06-01 ]

After investigating and looking at the code, the default behavior for unknown queries is to route them to the server with the current active database:

2020-06-01 08:42:13   info   : (2) [schemarouter] Servers and connection counts:
 
2020-06-01 08:42:13   info   : (2) [schemarouter] MaxScale connections : 0 (0) in       127.0.0.1:3000 Master, Running
 
2020-06-01 08:42:13   info   : (2) [schemarouter] MaxScale connections : 0 (0) in       127.0.0.1:3001 Slave, Running
 
2020-06-01 08:42:13   info   : (2) [schemarouter] MaxScale connections : 0 (0) in       127.0.0.1:3002 Slave, Running
 
2020-06-01 08:42:13   info   : (2) [schemarouter] MaxScale connections : 0 (0) in       127.0.0.1:3003 Slave, Running
 
2020-06-01 08:42:13   info   : (2) [schemarouter] Connected Master, Running in  127.0.0.1:3000
 
2020-06-01 08:42:13   info   : (2) [schemarouter] Connected Slave, Running in   127.0.0.1:3001
 
2020-06-01 08:42:13   info   : (2) [schemarouter] Connected Slave, Running in   127.0.0.1:3002
 
2020-06-01 08:42:13   info   : (2) [schemarouter] Connected Slave, Running in   127.0.0.1:3003
 
2020-06-01 08:42:13   info   : (2) [schemarouter] Client logging in directly to a database 'db1', postponing until databases have been mapped.
 
2020-06-01 08:42:13   info   : (2) Started SchemaRouter-Router client session [2] for 'maxuser' from ::ffff:127.0.0.1
 
2020-06-01 08:42:13   info   : (2) Connected to 'server1' with thread id 220
 
2020-06-01 08:42:13   info   : (2) Connected to 'server2' with thread id 120
 
2020-06-01 08:42:13   info   : (2) Connected to 'server3' with thread id 192
 
2020-06-01 08:42:13   info   : (2) Connected to 'server4' with thread id 198
 
2020-06-01 08:42:13   info   : (2) [schemarouter] USE 'db1' sent to server4 for session 0x615000080c80
 
2020-06-01 08:42:13   info   : (2) [schemarouter] > Command: COM_QUERY, stmt: show databases 
2020-06-01 08:42:13   info   : (2) [schemarouter] > Command: COM_QUERY, stmt: show tables 
2020-06-01 08:42:13   info   : (2) [schemarouter] Using active database 'db1' on 'server4'
 
2020-06-01 08:42:13   info   : (2) [schemarouter] Route query to        server4 [127.0.0.1]:3003 <
 
2020-06-01 08:42:13   info   : (2) [schemarouter] > Command: COM_FIELD_LIST, stmt:  
2020-06-01 08:42:13   info   : (2) [schemarouter] Using active database 'db1' on 'server4'
 
2020-06-01 08:42:13   info   : (2) [schemarouter] Route query to        server4 [127.0.0.1]:3003 <
 
2020-06-01 08:42:13   info   : (2) [schemarouter] > Command: COM_QUERY, stmt: select @@version_comment limit 1 
2020-06-01 08:42:13   info   : (2) [schemarouter] Using active database 'db1' on 'server4'
 
2020-06-01 08:42:13   info   : (2) [schemarouter] Route query to        server4 [127.0.0.1]:3003 <
 
2020-06-01 08:42:15   info   : (2) [schemarouter] > Command: COM_QUERY, stmt: select DATABASE(), USER() limit 1 
2020-06-01 08:42:15   info   : (2) [schemarouter] Using active database 'db1' on 'server4'
 
2020-06-01 08:42:15   info   : (2) [schemarouter] Route query to        server4 [127.0.0.1]:3003 <
 
2020-06-01 08:42:15   info   : (2) [schemarouter] > Command: COM_QUERY, stmt: select @@character_set_client, @@character_set_connection, @@character_set_server, @@character_set_database limit 1 
2020-06-01 08:42:15   info   : (2) [schemarouter] Using active database 'db1' on 'server4'
 
2020-06-01 08:42:15   info   : (2) [schemarouter] Route query to        server4 [127.0.0.1]:3003 <
 
2020-06-01 08:42:15   info   : (2) [schemarouter] > Command: COM_STATISTICS, stmt:  
2020-06-01 08:42:15   info   : (2) [schemarouter] Using active database 'db1' on 'server4'
 
2020-06-01 08:42:15   info   : (2) [schemarouter] Route query to        server4 [127.0.0.1]:3003 <

Can you give a reproducible test case that we could use?

Comment by markus makela [ 2020-08-20 ]

Oy5p aegh any updates on this issue? Have you had a chance to try whether this happens with the 2.5 version of MaxScale?

Comment by markus makela [ 2020-08-27 ]

Oy5p aegh please test this with the latest 2.5 release of MaxScale.

Comment by markus makela [ 2020-10-12 ]

Closing as Cannot Reproduce as there's been no replies.

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