Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Cannot Reproduce
-
2.4.8
-
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 |
Attachments
Activity
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?
Oy5p aegh any updates on this issue? Have you had a chance to try whether this happens with the 2.5 version of MaxScale?
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.