[MXS-77] "Unknown database" when connecting via schemarouter and providing database name Created: 2015-04-01  Updated: 2015-08-21  Resolved: 2015-04-02

Status: Closed
Project: MariaDB MaxScale
Component/s: Core, schemarouter
Affects Version/s: 1.1.0
Fix Version/s: 1.1.0

Type: Bug Priority: Blocker
Reporter: Kolbe Kegel (Inactive) Assignee: Timofey Turenko
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

CentOS 6


 Description   

It seems that authentication fails when I try to connect to a schemarouter service using the mysql client with a default database on the command line.

[skysql@max1 ~]$ mysql -h 127.0.0.1 -P3310 -u kolbe -e 'select database()' shard1
 
ERROR 1049 (42000): Unknown database 'shard1'
 
[skysql@max1 ~]$ mysql -h 127.0.0.1 -P3310 -u kolbe -e 'select database()' shard2
 
+------------+
 
| database() |
 
+------------+
 
| shard2     |
 
+------------+

Those same credentials can indeed be used to connect directly to the backend instances:

MaxScale> show service "Shard Router"
 
Service 0x306a920
 
        Service:                                Shard Router
 
        Router:                                 schemarouter (0x7f8b150bc720)
 
        State:                                  Started
 
Server          Queries         State
 
mariadb2        36              RUNNING
 
mariadb1        17              RUNNING
 
        Started:                                Mon Mar 30 21:34:40 2015
 
        Root user access:                       Disabled
 
        Backend databases
 
                127.0.0.1:3308  Protocol: MySQLBackend
 
                127.0.0.1:3307  Protocol: MySQLBackend
 
        Users data:                             0x7f8afc000ab0
 
        Total connections:                      11
 
        Currently connected:                    2
 
MaxScale> show server mariadb1
 
Server 0x306b970 (mariadb1)
 
        Server:                         127.0.0.1
 
        Status:                         Running
 
        Protocol:                       MySQLBackend
 
        Port:                           3307
 
        Node Id:                        -1
 
        Master Id:                      -1
 
        Repl Depth:                     -1
 
        Number of connections:          10
 
        Current no. of conns:           1
 
        Current no. of operations:      0
 
MaxScale> show server mariadb2
 
Server 0x306b860 (mariadb2)
 
        Server:                         127.0.0.1
 
        Status:                         Running
 
        Protocol:                       MySQLBackend
 
        Port:                           3308
 
        Node Id:                        -1
 
        Master Id:                      -1
 
        Repl Depth:                     -1
 
        Number of connections:          10
 
        Current no. of conns:           1
 
        Current no. of operations:      0
 

[skysql@max1 ~]$ mysql -h 127.0.0.1 -P3307 -u kolbe -e 'select database()' shard1
 
+------------+
 
| database() |
 
+------------+
 
| shard1     |
 
+------------+
 
[skysql@max1 ~]$ mysql -h 127.0.0.1 -P3308 -u kolbe -e 'select database()' shard2
 
+------------+
 
| database() |
 
+------------+
 
| shard2     |
 
+------------+

Failure from trace log:

2015-03-31 03:39:01   schemarouter: Client logging in directly to a database 'shard2', postponing until databases have been mapped.
 
2015-03-31 03:39:01   Servers and connection counts:
 
2015-03-31 03:39:01   MaxScale connections : 1 (1) in   127.0.0.1:3308 RUNNING (only)
 
2015-03-31 03:39:01   MaxScale connections : 1 (1) in   127.0.0.1:3307 RUNNING (only)
 
2015-03-31 03:39:01   Connected RUNNING (only) in       127.0.0.1:3308
 
2015-03-31 03:39:01   Connected RUNNING (only) in       127.0.0.1:3307
 
2015-03-31 03:39:01   Started Shard Router client session [16] for 'kolbe' from 127.0.0.1
 
2015-03-31 03:39:01   [16]  schemarouter: <mariadb1, information_schema>
 
2015-03-31 03:39:01   [16]  schemarouter: <mariadb1, mysql>
 
2015-03-31 03:39:01   [16]  schemarouter: <mariadb1, performance_schema>
 
2015-03-31 03:39:01   [16]  schemarouter: <mariadb1, shard1>
 
2015-03-31 03:39:01   [16]  schemarouter: <mariadb1, test>
 
2015-03-31 03:39:01   [16]  schemarouter: <mariadb2, shard2>
 
2015-03-31 03:39:01   [16]  > Cmd: COM_QUERY, type: QUERY_TYPE_READ|QUERY_TYPE_SYSVAR_READ, stmt: select @@version_comment limit 1
 
2015-03-31 03:39:01   [16]  schemarouter: Using active database 'shard2'
 
2015-03-31 03:39:01   [16]  Route query to      127.0.0.1:3308 <
 
2015-03-31 03:39:01   [16]  schemarouter: returning reply [RSET] state [READY]  session [0x307fd60]
 
2015-03-31 03:39:01   [16]  > Cmd: COM_QUERY, type: QUERY_TYPE_READ, stmt: select database()
 
2015-03-31 03:39:01   [16]  schemarouter: Using active database 'shard2'
 
2015-03-31 03:39:01   [16]  Route query to      127.0.0.1:3308 <
 
2015-03-31 03:39:01   [16]  schemarouter: returning reply [RSET] state [READY]  session [0x307fd60]
 
2015-03-31 03:39:01   [16]  > Cmd: COM_QUIT, type: QUERY_TYPE_SESSION_WRITE, stmt:
 
2015-03-31 03:39:01   [16]  Session write, routing to all servers.
 
2015-03-31 03:39:01   [16]  Route query to slave        127.0.0.1:3308
 
2015-03-31 03:39:01   [16]  Route query to slave        127.0.0.1:3307 <
 
2015-03-31 03:39:01   [16]  Stopped Shard Router client session [16]

It looks strange that the trace log starts off with "Client logging in directly to a database 'shard2'". What's going on there?

Here's the excerpt from the debug log:

2015-03-31 03:43:52   140235203655424 [poll_waitevents] epoll_wait found 1 fds
 
2015-03-31 03:43:52   140235203655424 [poll_waitevents] event 1 dcb 0x2fbb470 role DCB_ROLE_SERVICE_LISTENER
 
2015-03-31 03:43:52   140235203655424 [poll_waitevents] Accept in fd 15
 
2015-03-31 03:43:52   140235203655424 [dcb_write] Wrote 87 Bytes to dcb 0x7f8af8000e60 in state DCB_STATE_ALLOC fd 25
 
2015-03-31 03:43:52   140235203655424 [dcb_set_state_nomutex] dcb 0x7f8af8000e60 fd 25 DCB_STATE_ALLOC -> DCB_STATE_POLLING
 
2015-03-31 03:43:52   140235203655424 [poll_add_dcb] Added dcb 0x7f8af8000e60 in state DCB_STATE_POLLING to poll set.
 
2015-03-31 03:43:52   140235203655424 [gw_MySQLAccept] Added dcb 0x7f8af8000e60 for fd 25 to epoll set.
 
2015-03-31 03:43:52   140235203655424 [poll_waitevents] epoll_wait found 1 fds
 
2015-03-31 03:43:52   140235203655424 [poll_waitevents] event 4 dcb 0x7f8af8000e60 role DCB_ROLE_REQUEST_HANDLER
 
2015-03-31 03:43:52   140235203655424 [poll_waitevents] epoll_wait found 1 fds
 
2015-03-31 03:43:52   140235203655424 [poll_waitevents] event 5 dcb 0x7f8af8000e60 role DCB_ROLE_REQUEST_HANDLER
 
2015-03-31 03:43:52   140235203655424 [poll_waitevents] Read in dcb 0x7f8af8000e60 fd 25
 
2015-03-31 03:43:52   140235203655424 [dcb_read] Read 72 bytes from dcb 0x7f8af8000e60 in state DCB_STATE_POLLING fd 25.
 
2015-03-31 03:43:52   Receiving connection from 'kolbe' to database 'shard1'.
 
2015-03-31 03:43:52   140235203655424 [MySQL Client Auth], checking user [kolbe@127.0.0.1] db: shard1
 
2015-03-31 03:43:52   Dbusers : Loading data from backend database [127.0.0.1:3308] for service [Shard Router]
 
2015-03-31 03:43:52   Shard Router: Adding database information_schema to the resouce hash.
 
2015-03-31 03:43:52   Shard Router: Adding database mysql to the resouce hash.
 
2015-03-31 03:43:52   Shard Router: Adding database performance_schema to the resouce hash.
 
2015-03-31 03:43:52   Shard Router: Adding database shard2 to the resouce hash.
 
2015-03-31 03:43:52   Shard Router: Adding database test to the resouce hash.
 
2015-03-31 03:43:52   Loaded 5 MySQL Database Names for service [Shard Router]
 
2015-03-31 03:43:52   Shard Router: User maxuser@127.0.0.1 for database ANY added to service user table.
 
2015-03-31 03:43:52   Shard Router: User kolbe@127.0.0.1 for database no db added to service user table.
 
2015-03-31 03:43:52   140235203655424 [replace_mysql_users] users' tables not switched, checksum is the same
 
2015-03-31 03:43:52   140235203655424 [MySQL Client Auth], checking user [kolbe@127.0.0.1] db: shard1
 
2015-03-31 03:43:52   140235203655424 [dcb_write] Wrote 38 Bytes to dcb 0x7f8af8000e60 in state DCB_STATE_POLLING fd 25
 
2015-03-31 03:43:52   25 [gw_read_client_event] after gw_mysql_do_authentication, fd 226473728, state = MYSQL_AUTH_FAILED.
 
2015-03-31 03:43:52   140235203655424 [dcb_close]
 
2015-03-31 03:43:52   140235203655424 [dcb_set_state_nomutex] dcb 0x7f8af8000e60 fd 25 DCB_STATE_POLLING -> DCB_STATE_NOPOLLING
 
2015-03-31 03:43:52   140235203655424 [dcb_close] Removed dcb 0x7f8af8000e60 in state DCB_STATE_NOPOLLING from poll set.
 
2015-03-31 03:43:52   140235203655424 [gw_client_close]
 
2015-03-31 03:43:52   140235203655424 [dcb_set_state_nomutex] dcb 0x7f8af8000e60 fd 25 DCB_STATE_NOPOLLING -> DCB_STATE_ZOMBIE
 
2015-03-31 03:43:53   140235193165568 [dcb_process_zombies] Remove dcb 0x7f8af8000e60 fd 25 in state DCB_STATE_ZOMBIE from the list of zombies.
 
2015-03-31 03:43:53   140235193165568 [dcb_process_zombies] Closed socket -1 on dcb 0x7f8af8000e60.
 
2015-03-31 03:43:53   140235193165568 [dcb_set_state_nomutex] dcb 0x7f8af8000e60 fd -1 DCB_STATE_ZOMBIE -> DCB_STATE_DISCONNECTED

Here are the grants for kolbe@127.0.0.1 and maxuser@127.0.0.1 (the user used by MaxScale to get privilege information) on each of the backends:

[skysql@max1 ~]$ mysql -h 127.0.0.1 -P3307 -u kolbe -e 'show grants'
 
+----------------------------------------------------+
 
| Grants for kolbe@127.0.0.1                         |
 
+----------------------------------------------------+
 
| GRANT ALL PRIVILEGES ON *.* TO 'kolbe'@'127.0.0.1' |
 
+----------------------------------------------------+
 
[skysql@max1 ~]$ mysql -h 127.0.0.1 -P3308 -u kolbe -e 'show grants'
 
+----------------------------------------------------+
 
| Grants for kolbe@127.0.0.1                         |
 
+----------------------------------------------------+
 
| GRANT ALL PRIVILEGES ON *.* TO 'kolbe'@'127.0.0.1' |
 
+----------------------------------------------------+
 
[skysql@max1 ~]$ mysql -h 127.0.0.1 -P3307 -u maxuser -pmaxpass -e 'show grants'
 
+-------------------------------------------------------------------------------------------------------------------------+
 
| Grants for maxuser@127.0.0.1                                                                                            |
 
+-------------------------------------------------------------------------------------------------------------------------+
 
| GRANT SHOW DATABASES ON *.* TO 'maxuser'@'127.0.0.1' IDENTIFIED BY PASSWORD '*7EC7BAE6F7C1EF61723FA75AC8E03E38AEECD8FB' |
 
| GRANT SELECT ON `mysql`.`user` TO 'maxuser'@'127.0.0.1'                                                                 |
 
| GRANT SELECT ON `mysql`.`db` TO 'maxuser'@'127.0.0.1'                                                                   |
 
+-------------------------------------------------------------------------------------------------------------------------+
 
[skysql@max1 ~]$ mysql -h 127.0.0.1 -P3308 -u maxuser -pmaxpass -e 'show grants'
 
+-------------------------------------------------------------------------------------------------------------------------+
 
| Grants for maxuser@127.0.0.1                                                                                            |
 
+-------------------------------------------------------------------------------------------------------------------------+
 
| GRANT SHOW DATABASES ON *.* TO 'maxuser'@'127.0.0.1' IDENTIFIED BY PASSWORD '*7EC7BAE6F7C1EF61723FA75AC8E03E38AEECD8FB' |
 
| GRANT SELECT ON `mysql`.`user` TO 'maxuser'@'127.0.0.1'                                                                 |
 
| GRANT SELECT ON `mysql`.`db` TO 'maxuser'@'127.0.0.1'                                                                   |
 
+-------------------------------------------------------------------------------------------------------------------------+



 Comments   
Comment by Kolbe Kegel (Inactive) [ 2015-04-01 ]

This fails for Perl, too, so it seems like it's a problem for any client that specifies a database name when connecting...

[skysql@max1 ~]$ perl -MDBI -e 'DBI->connect("DBI:mysql:host=127.0.0.1;port=3310;database=shard1", "kolbe", "kolbe")'
 
DBI connect('host=127.0.0.1;port=3310;database=shard1','kolbe',...) failed: Unknown database 'shard1' at -e line 1

Comment by markus makela [ 2015-04-01 ]

Could you retest this with auth_all_servers=1 parameter for the service?

Comment by markus makela [ 2015-04-01 ]

If you can, please attach the MaxScale.cnf file.

Comment by Kolbe Kegel (Inactive) [ 2015-04-02 ]

This issue does not occur when using auth_all_servers=1.

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