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

schemarouter in 2.3.4 doesn't show all tables from all backends

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 2.3.4
    • 2.3.5
    • schemarouter
    • None
    • MXS-SPRINT-76, MXS-SPRINT-77

    Description

      schemarouter in 2.3 has table level granularity, this means that it is able to show under the same schema tables from different backends.
      This feature is very interesting and can be considered of the 'sharding' family.
      Nevertheless in 2.3.4 is not yet perfectly behaving.

      Having this config:

      [maxscale]
      threads=4
       
      [Splitter-Listener]
      type=listener
      service=Splitter-Service
      protocol=mariadbclient
      port=11111
      address=0.0.0.0
       
      [Splitter-Service]
      type=service
      router=schemarouter
      ignore_databases_regex=.*
      servers=replication-node1,db-cache1
      user=maxscale
      password=123
       
      [replication-node1]
      type=server
      address=192.168.1.100
      port=10221
      protocol=mariadbbackend
       
      [db-cache1]
      type=server
      address=192.168.1.100
      port=40138
      protocol=mariadbbackend
      

      Connecting to the service:

      MariaDB [test]> use test;
      Database changed
      MariaDB [test]> show tables;
      +----------------+
      | Tables_in_test |
      +----------------+
      | cache_t001     |
      | cache_t002     |
      +----------------+
      2 rows in set (0.012 sec)
       
      MariaDB [test]> Bye
       
      [root@dhcppc8 claudio]# mysql -umaxscale -p123 -h127.0.0.1 -P11111
      MariaDB [(none)]> use test;
      Database changed
      MariaDB [test]> show tables;
      +----------------+
      | Tables_in_test |
      +----------------+
      | AAA_T1         |
      | AAA_T2         |
      +----------------+
      2 rows in set (0.002 sec)
       
       
      MariaDB [test]> show tables from test;
      +------------+
      | Table      |
      +------------+
      | AAA_T2     |
      | AAA_T1     |
      | cache_t002 |
      | cache_t001 |
      +------------+
      4 rows in set (0.001 sec)
      

      And finally:

      MariaDB [test]> show tables;
      +----------------+
      | Tables_in_test |
      +----------------+
      | cache_t001     |
      | cache_t002     |
      +----------------+
      2 rows in set (0.002 sec)
       
      MariaDB [test]> SELECT * FROM cache_t001;
      +------+
      | id   |
      +------+
      |    1 |
      |    2 |
      |    3 |
      +------+
      3 rows in set (0.001 sec)
       
      MariaDB [test]> SELECT * FROM AAA_T1;
      ERROR 1146 (42S02): Table 'test.AAA_T1' doesn't exist
      MariaDB [test]> SELECT * FROM test.AAA_T1;
      +------+
      | id   |
      +------+
      |  111 |
      |  111 |
      |  111 |
      +------+
      3 rows in set (0.009 sec)
      

      So to 'trigger' the global view of the tables you need to fully qualify the table with schema name, otherwise it'll see the tables from one backend.

      Attachments

        Issue Links

          Activity

            People

              esa.korhonen Esa Korhonen
              claudio.nanni Claudio Nanni
              Votes:
              1 Vote for this issue
              Watchers:
              3 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.