Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-77

"Unknown database" when connecting via schemarouter and providing database name

    XMLWordPrintable

    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:
            tturenko Timofey Turenko
            Reporter:
            kolbe Kolbe Kegel (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration

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