[MXS-2640] Regression: Error "authentication failed. Unknown database" when DB specified while connecting with rwsplit router Created: 2019-08-15  Updated: 2020-08-27  Resolved: 2020-08-27

Status: Closed
Project: MariaDB MaxScale
Component/s: xpandmon
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Rahul Joshi (Inactive) Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: need_feedback
Environment:

MaxScale server karma182:
OS: CentOS 7
Version: built from 2.4 branch,
[root@karma182 ~]# maxscale -V
MaxScale 2.4.1 - 8ce6a679bef8c73b156d3687af7d6eac3c8f46bc
Clustrix nodes:
OS: CentOS 7
Version: clustrix-fredonyer-16045

Attachments: File clustrix_karma197_rwsplit.cnf     Text File maxscale.log     Text File maxscale_2.log    

 Description    

[root@karma182 ~]# maxctrl list servers
 
┌───────────────────────────┬─────────────┬──────┬─────────────┬─────────────────┬──────┐
 
│ Server                    │ Address     │ Port │ Connections │ State           │ GTID │
 
├───────────────────────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
 
│ @@Cluster-Monitor:node-2  │ 10.2.15.89  │ 3306 │ 0           │ Master, Running │      │
 
├───────────────────────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
 
│ @@Cluster-Monitor:node-3  │ 10.2.15.180 │ 3306 │ 0           │ Master, Running │      │
 
├───────────────────────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
 
│ @@Cluster-Monitor:node-1  │ 10.2.13.91  │ 3306 │ 0           │ Master, Running │      │
 
├───────────────────────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
 
│ @@Cluster-Monitor1:node-2 │ 10.2.15.75  │ 3306 │ 0           │ Master, Running │      │
 
├───────────────────────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
 
│ @@Cluster-Monitor1:node-3 │ 10.2.12.218 │ 3306 │ 0           │ Master, Running │      │
 
├───────────────────────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
 
│ @@Cluster-Monitor1:node-1 │ 10.2.15.40  │ 3306 │ 0           │ Master, Running │      │
 
├───────────────────────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
 
│ BS1                       │ 10.2.13.91  │ 3306 │ 0           │ Master, Running │      │
 
├───────────────────────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
 
│ BS4                       │ 10.2.15.40  │ 3306 │ 0           │ Master, Running │      │
 
├───────────────────────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
 
│ MBS                       │ 127.0.0.1   │ 4006 │ 0           │ Master, Running │      │
 
├───────────────────────────┼─────────────┼──────┼─────────────┼─────────────────┼──────┤
 
│ MBS2                      │ 127.0.0.1   │ 4007 │ 0           │ Slave, Running  │      │
 
└───────────────────────────┴─────────────┴──────┴─────────────┴─────────────────┴──────┘


[root@vqc008c vqc008c]# mysql -h karma182 -umaxscale -pmaxscale_pw -P 4008 test
 
mysql: [Warning] Using a password on the command line interface can be insecure.
 
ERROR 1049 (42000): Unknown database 'test'

If connection is created without specifying the DB, it works.

[root@vqc008c vqc008c]# mysql -h karma182 -umaxscale -pmaxscale_pw -P 4008
 
mysql: [Warning] Using a password on the command line interface can be insecure.
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
 
Your MySQL connection id is 9
 
Server version: 5.5.5-10.2.12 2.4.1-maxscale
 
 
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
 
 
Oracle is a registered trademark of Oracle Corporation and/or its
 
affiliates. Other names may be trademarks of their respective
 
owners.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
mysql> use test;
 
Database changed

karma136 has older MaxScale server installed and run with same conf file as karma182.

[root@karma136 ~]# maxscale -V
 
MaxScale 2.4.1 - 3940547cc20a15af0a8c420b55fe0cf39bdc4549

The same works on that version:

 [root@vqc008c vqc008c]# mysql -h karma136 -umaxscale -pmaxscale_pw -P 4008 test
 
mysql: [Warning] Using a password on the command line interface can be insecure.
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
 
Your MySQL connection id is 15
 
Server version: 5.0.45-clustrix-elk-14841
 
 
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
 
 
Oracle is a registered trademark of Oracle Corporation and/or its
 
affiliates. Other names may be trademarks of their respective
 
owners.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
mysql> select @@version;
 
+---------------------------------+
 
| @@version                       |
 
+---------------------------------+
 
| 5.0.45-clustrix-fredonyer-16045 |
 
+---------------------------------+
 
1 row in set (0.01 sec)

It seems to be looking for mysql tables:

2019-08-15 06:13:04   info   : (4) [readwritesplit] Session command no. 2 failed: #HY000: [8192] Relation not found: "mysql"."roles_mapping"
 
2019-08-15 06:13:04   info   : (4) > Autocommit: [enabled], trx is [not open], cmd: (0x03) COM_QUERY, plen: 14, type: QUERY_TYPE_SESSION_WRITE, stmt: USE mysql
 
2019-08-15 06:13:04   info   : (4) [readwritesplit] Session write, routing to all servers.
 
2019-08-15 06:13:04   info   : (4) [readwritesplit] Route query to master: @@Cluster-Monitor:node-1     [10.2.13.91]:3306
 
2019-08-15 06:13:04   info   : (4) [readwritesplit] Reply complete, last reply from @@Cluster-Monitor:node-1
 
2019-08-15 06:13:04   info   : (4) [readwritesplit] Session command no. 3 failed: #42000: [11267] No such database: "mysql"
 
2019-08-15 06:13:04   error  : (3) [MariaDBAuth] Failed to load users from server 'MBS': [11267] No such database: "mysql"

ERROR:

2019-08-15 06:13:04   warning: (3) [MariaDBAuth] Read-Write-Service_rwsrvc: login attempt for user 'maxscale'@[::ffff:10.2.14.197]:38730, authentication failed. Unknown database: test

Log and cnf file attached.

 Comments   
Comment by Johan Wikman [ 2019-08-15 ]

Are the privileges of the maxscale user exactly the same on both Clustrix installations?

Comment by Rahul Joshi (Inactive) [ 2019-08-15 ]

Yeah, they are the same

[root@karma182 ~]# mysql -h 10.2.15.40 -u maxscale -pmaxscale_pw -e "show grants for maxscale@'%'"
 
+---------------------------------------------------------------+
 
| Grants for maxscale@%                                         |
 
+---------------------------------------------------------------+
 
| GRANT ALL PRIVILEGES ON *.* TO 'maxscale'@'%'                 |
 
| GRANT SELECT ON `system`.`membership` TO 'maxscale'@'%'       |
 
| GRANT SELECT ON `system`.`nodeinfo` TO 'maxscale'@'%'         |
 
| GRANT SELECT ON `system`.`softfailed_nodes` TO 'maxscale'@'%' |
 
+---------------------------------------------------------------+
 
[root@karma182 ~]# mysql -h 10.2.13.91 -u maxscale -pmaxscale_pw -e "show grants for maxscale@'%'"
 
+---------------------------------------------------------------+
 
| Grants for maxscale@%                                         |
 
+---------------------------------------------------------------+
 
| GRANT ALL PRIVILEGES ON *.* TO 'maxscale'@'%'                 |
 
| GRANT SELECT ON `system`.`membership` TO 'maxscale'@'%'       |
 
| GRANT SELECT ON `system`.`nodeinfo` TO 'maxscale'@'%'         |
 
| GRANT SELECT ON `system`.`softfailed_nodes` TO 'maxscale'@'%' |
 
+---------------------------------------------------------------+

Also, if I use port 4006 or port 4007 (used by individual listeners for two Clustrix clusters), it works.

[root@vqc008c vqc008c]# mysql -h karma182 -umaxscale -pmaxscale_pw -P 4006 test
 
mysql: [Warning] Using a password on the command line interface can be insecure.
 
Reading table information for completion of table and column names
 
You can turn off this feature to get a quicker startup with -A
 
 
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
 
Your MySQL connection id is 5
 
Server version: 5.0.45-clustrix-fredonyer-16045
 
 
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
 
 
Oracle is a registered trademark of Oracle Corporation and/or its
 
affiliates. Other names may be trademarks of their respective
 
owners.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
mysql> \q
 
Bye
 
---------------------------------------------------------------------------------------
 
[root@vqc008c vqc008c]# mysql -h karma182 -umaxscale -pmaxscale_pw -P 4007 test
 
mysql: [Warning] Using a password on the command line interface can be insecure.
 
Welcome to the MySQL monitor.  Commands end with ; or \g.
 
Your MySQL connection id is 6
 
Server version: 5.5.5-10.2.12 2.4.1-maxscale
 
 
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
 
 
Oracle is a registered trademark of Oracle Corporation and/or its
 
affiliates. Other names may be trademarks of their respective
 
owners.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
mysql> \q
 
Bye
 
---------------------------------------------------------------------------------------
 
[root@vqc008c vqc008c]# mysql -h karma182 -umaxscale -pmaxscale_pw -P 4008 test
 
mysql: [Warning] Using a password on the command line interface can be insecure.
 
ERROR 1049 (42000): Unknown database 'test'
 
[root@vqc008c vqc008c]#

Comment by Rahul Joshi (Inactive) [ 2019-08-15 ]

Attaching the logs (maxscale_2.log ) with just this one attempt to connect with DB name specified.

Comment by Adrien [ 2019-11-19 ]

FYI,

I had the exact same problem.
I was missing the 'SHOW DATABASES' privilege 

GRANT SHOW DATABASES ON *.* TO 'maxscale'@'maxscalehost';

https://mariadb.com/kb/en/maxscale-troubleshooting/

Comment by markus makela [ 2020-03-02 ]

rahul.joshi@mariadb.com is this still relevant?

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