[#MXS-4243] EXPLAIN select output differs from cli or direct DB outputs

The customer has been getting different EXPLAIN select outputs for the Maxscale GUI SQL Editor versus the same EXPLAIN select ran against the Maxscale node via cli or directly querying the backend DB host.

Below is an example of the Maxscale configuration used. All tests and issues seen were using the "server1" server and listener service "Schema-Service-Listener"

[maxscale]

threads                                 =       4

admin_host                              =       maxhost

admin_port                              =       8080

auth_read_timeout                       =       10s

admin_secure_gui                        =       false

admin_pam_readonly_service              =       maxscale

#log_info                               =       1

#log_notice                             =       1

#log_warning                            =       1

#log_debug                              =       1

[server1]

type                                    =       server

address                                 =       <IP>

port                                    =       <port>

proxy_protocol                          =       on

[server2]

type                                    =       server

address                                 =       <IP>

port                                    =       <port>

proxy_protocol                          =       on

[server3]

type                                    =       server

address                                 =       <IP>

port                                    =       <port>

proxy_protocol                          =       on

[server4]

type                                   =       server

address                                =       <IP>

port                                   =       <port>

proxy_protocol                         =       on

[server5]

type                                    =       server

address                                 =       <IP>

port                                    =       <port>

proxy_protocol                          =       on

[server6]

type                                    =       server

address                                 =       <IP>

port                                    =       <port>

proxy_protocol                          =       on

#[server7]

#type                                    =       server

#address                                 =       <IP>

#port                                    =       <port>

#proxy_protocol                          =       on

#rank                                   =       secondary

[Scehma-Router-Service]

type                                    =       service

router                                  =       schemarouter

servers                                 =       server1,server2,server3,server4,server5,server6

user                                    =       <username>

password                                =       XXXXXX

ignore_tables_regex                     =       <regex list>

connection_timeout                      =       3000

auth_all_servers                        =       true

[Schema-Service-Listener]

type                                    =       listener

service                                 =       Scehma-Router-Service

protocol                                =       mariadbclient

port                                    =       <port>

authenticator                           =       PAMAuth

[RN-RO-Monitor]

type                                    =       monitor

module                                  =       mariadbmon

servers                                 =       server1,server2,server3,server4,server5,server6

user                                    =       <username>

password                                =       XXXXXX

#ignore_external_masters                        =       true

## Dont add the below Servers to Scehma-Router-Service and RN-RO-Monitor section.

[server8]

type                                    =       server

address                                 =       <hostname>

port                                    =       <port>

protocol                                =       mariadbclient

[server9]

type                                    =       server

address                                 =       <hostname>

port                                    =       <port>

protocol                                =       mariadbclient

[server10]

type                                    =       server

address                                 =       <hostname>

port                                    =       <port>

protocol                                =       mariadbclient

authenticator                           =       PAMAuth

[server11]

type                                    =       server

address                                 =       <hostname>

port                                    =       <port>

protocol                                =       mariadbclient

[server12]

type                                    =       server

address                                 =       <hostname>

port                                    =       <port>

protocol                                =       mariadbclient

Please note for security reasons and respecting the associated customer's need for confidentiality, screenshots/PDFs that were taken by the customer can be found securely at the following internal link for MariaDB employees:

As one can see looking at the PDF/screenshots saved in the secure Google drive location, the Maxscale GUI SQL Editor consitently returns a key for the one table referenced in the EXPLAIN select regardless of using the server endpoint or the listener service. Conversely, the same EXPLAIN select returns NULL for MariaDB cli query against the Maxscale node or directley from the MariaDB console directly on the server.

[MXS-4243] EXPLAIN select output differs from cli or direct DB outputs Created: 2022-08-12 Updated: 2022-09-21 Resolved: 2022-09-21
Status:	Closed
Project:	MariaDB MaxScale
Component/s:	maxgui, schemarouter
Affects Version/s:	6.3.1
Fix Version/s:	N/A

[MXS-4243] EXPLAIN select output differs from cli or direct DB outputs Created: 2022-08-12 Updated: 2022-09-21 Resolved: 2022-09-21