[MXS-1675] "Unknown database" while connecting via readconnroute Created: 2018-02-20  Updated: 2020-08-25  Resolved: 2018-03-26

Status: Closed
Project: MariaDB MaxScale
Component/s: readconnroute
Affects Version/s: 2.1.12
Fix Version/s: 1.4.0

Type: Bug Priority: Major
Reporter: Valerii Kravchuk Assignee: Johan Wikman
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

It seems there is a case when default database can not be set at command line while using readconnroute router of MaxScale 2.1.12. The problem looks as follows:

mysql -u user1 -p -h 127.0.0.1 db1
 
ERROR 1049 (42000): Unknown database 'db1'"

While this works:

mysql -u user1 -p -h 127.0.0.1
 
...
 
Server version: 10.0.0 2.1.12-maxscale MariaDB Enterprise Certified Binary, wsrep_25.20.rc3fc46e
 
...
 
MySQL [(none)]> use db1;
 
Reading table information for completion of table and column names
 
...
 
Database changed
 
MySQL [db1]>

In the log we see:

...
 
2018-02-14 17:08:13 notice : [MySQLAuth] [Single Node Router] Loaded 31 MySQL users for listener Single Node Listener.
 
2018-02-14 17:08:13 warning: [MySQLAuth] Single Node Router: login attempt for user 'user1'@[127.0.0.1]:58084, authentication failed.
 
...

Service is configured as:

[maxscale]
 
threads=2
 
 
 
[s1]
 
type=server
 
address=s1.dom
 
port=3306
 
protocol=MySQLBackend
 
priority=1
 
node_a=100
 
node_b=0
 
node_c=0
 
 
 
[s2]
 
type=server
 
address=s2.dom
 
port=3306
 
protocol=MySQLBackend
 
priority=2
 
node_a=0
 
node_b=100
 
node_c=0
 
 
 
[s3]
 
type=server
 
address=s3.dom
 
port=3306
 
protocol=MySQLBackend
 
priority=3
 
node_a=0
 
node_b=0
 
node_c=100
 
 
 
[Galera Monitor]
 
type=monitor
 
module=galeramon
 
servers=s1,s2,s3
 
user=maxscale
 
passwd=x
 
monitor_interval=1000
 
live_nodes=$NODELIST synced_nodes=$SYNCEDLIST
 
use_priority=true
 
 
 
[Single Node Router]
 
type=service
 
router=readconnroute
 
servers=s1,s2,s3
 
user=maxscale
 
passwd=x
 
localhost_match_wildcard_host=1
 
router_options=synced
 
weightby=node_b

User has the following rights:

| GRANT USAGE ON *.* TO 'user1'@'%' IDENTIFIED BY PASSWORD |
 
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, LOCK TABLES ON `db1`.* TO 'user1'@'%' |



 Comments   
Comment by markus makela [ 2018-02-20 ]

The output of this SQL could help figure out why it fails.

Another possitibity is that there exists a 'user1'@'127.0.0.1' and the grants for this user override the ones for 'user1'@'%'.

Comment by Johan Wikman [ 2018-02-20 ]

I have, based on the provided information, replicated the situation as closely as possible but was not able to repeat the problem:

$ mysql -u user1 -p -h 127.0.0.1 -P 4008 db1
 
Enter password: 
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 MariaDB monitor.  Commands end with ; or \g.
 
Your MySQL connection id is 14130
 
Server version: 10.0.0 2.1.13-maxscale MariaDB Server
 
 
 
Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MySQL [db1]>

Comment by markus makela [ 2018-03-06 ]

Please check whether the MaxScale user has the SHOW DATABASES grant.

Comment by Johan Wikman [ 2018-03-26 ]

valerii Could you please check that the MaxScale user (that is, the user specified in the service section in the MaxScale configuration file) 

[Single Node Router]
 
type=service
 
router=readconnroute
 
servers=s1,s2,s3
 
user=maxscale
 
...

has the SHOW DATABASES grant.

https://github.com/mariadb-corporation/MaxScale/blob/2.2/Documentation/Tutorials/MaxScale-Tutorial.md#creating-database-users

Comment by Valerii Kravchuk [ 2018-03-26 ]

SHOW DATABASES grant was indeed missing for the user set for ReadConnRoute (user=maxscale in the initial description).

The need for this grant is clearly mentioned in https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale-14/maxscale-configuration-usage-scenarios/#service, so this is not a bug. Sorry for wasting your time.

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