[MXS-2337] schemarouter in 2.3.4 doesn't show all tables from all backends Created: 2019-02-18  Updated: 2020-08-25  Resolved: 2019-03-07

Status: Closed
Project: MariaDB MaxScale
Component/s: schemarouter
Affects Version/s: 2.3.4
Fix Version/s: 2.3.5

Type: Bug Priority: Major
Reporter: Claudio Nanni Assignee: Esa Korhonen
Resolution: Won't Fix Votes: 1
Labels: None

Issue Links:
Relates
relates to MXS-2345 Query classifier should collect datab... Closed
Sprint: 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.

 Comments   
Comment by Claudio Nanni [ 2019-02-19 ]

Also,

I add a table on one of the backends:

MariaDB [test]> show tables;
 
+----------------+
 
| Tables_in_test |
 
+----------------+
 
| cache_t001     |
 
| cache_t002     |
 
+----------------+
 
2 rows in set (0.00 sec)
 
 
 
MariaDB [test]> create table cache_x003 (id int);
 
Query OK, 0 rows affected (0.02 sec)

Connecto to MaxScale schemarouter service:

MariaDB [test]> show tables;
 
+----------------+
 
| Tables_in_test |
 
+----------------+
 
| cache_t001     |
 
| cache_t002     |
 
| cache_x003     |
 
+----------------+
 
3 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.000 sec)

After a few minutes what I think is a cache is not yet updated:

MariaDB [test]> SELECT NOW();show tables from test;
 
+---------------------+
 
| NOW()               |
 
+---------------------+
 
| 2019-02-19 11:29:01 |
 
+---------------------+
 
1 row in set (0.001 sec)
 
 
 
+------------+
 
| Table      |
 
+------------+
 
| AAA_T2     |
 
| AAA_T1     |
 
| cache_t002 |
 
| cache_t001 |
 
+------------+
 
4 rows in set (0.000 sec)
 
 
 
MariaDB [test]> SELECT NOW();show tables from test;
 
+---------------------+
 
| NOW()               |
 
+---------------------+
 
| 2019-02-19 11:39:02 |
 
+---------------------+
 
1 row in set (0.002 sec)
 
 
 
+------------+
 
| Table      |
 
+------------+
 
| AAA_T2     |
 
| AAA_T1     |
 
| cache_t002 |
 
| cache_t001 |
 
+------------+
 
4 rows in set (0.001 sec)

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