[MXS-77] "Unknown database" when connecting via schemarouter and providing database name - Jira

XML

Word

Printable

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Blocker
Resolution: Not a Bug
Affects Version/s: 1.1.0
Fix Version/s: 1.1.0
Component/s: Core, schemarouter
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'                                                                   |

+-------------------------------------------------------------------------------------------------------------------------+

Attachments

Activity

People

Assignee:: Timofey Turenko

Reporter:: Kolbe Kegel (Inactive)

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2015-04-01 06:48

Updated:: 2015-08-21 15:01

Resolved:: 2015-04-02 06:00

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.