OB虚拟表的问题

OceanBase 技术问题
#1

V4版本中

mysql> SHOW TABLES FROM __all_virtual;
ERROR 1049 (42000): Unknown database '__all_virtual'
mysql> 
mysql> DESC __all_virtual_proxy_schema;
+---------------------------+---------------+------+-----+---------+-------+
| Field                     | Type          | Null | Key | Default | Extra |
+---------------------------+---------------+------+-----+---------+-------+
| tenant_name               | varchar(128)  | NO   | PRI | NULL    |       |
| database_name             | varchar(128)  | NO   | PRI | NULL    |       |
| table_name                | varchar(256)  | NO   | PRI | NULL    |       |
| tablet_id                 | bigint(20)    | NO   | PRI | NULL    |       |
| svr_ip                    | varchar(46)   | NO   | PRI | NULL    |       |
| sql_port                  | bigint(20)    | NO   | PRI | NULL    |       |
| table_id                  | bigint(20)    | NO   |     | NULL    |       |
| role                      | bigint(20)    | NO   |     | NULL    |       |
| part_num                  | bigint(20)    | NO   |     | NULL    |       |
| replica_num               | bigint(20)    | NO   |     | NULL    |       |
| table_type                | bigint(20)    | NO   |     | NULL    |       |
| schema_version            | bigint(20)    | NO   |     | NULL    |       |
| replica_type              | bigint(20)    | NO   |     | NULL    |       |
| dup_replica_type          | bigint(20)    | NO   |     | NULL    |       |
| memstore_percent          | bigint(20)    | NO   |     | NULL    |       |
| spare1                    | bigint(20)    | NO   |     | NULL    |       |
| spare2                    | bigint(20)    | NO   |     | NULL    |       |
| spare3                    | bigint(20)    | NO   |     | NULL    |       |
| spare4                    | varchar(4096) | NO   |     | NULL    |       |
| spare5                    | varchar(4096) | NO   |     | NULL    |       |
| spare6                    | varchar(4096) | NO   |     | NULL    |       |
| complex_table_type        | bigint(20)    | NO   |     | NULL    |       |
| level1_decoded_db_name    | varchar(128)  | NO   |     | NULL    |       |
| level1_decoded_table_name | varchar(256)  | NO   |     | NULL    |       |
| level2_decoded_db_name    | varchar(128)  | NO   |     | NULL    |       |
| level2_decoded_table_name | varchar(256)  | NO   |     | NULL    |       |
+---------------------------+---------------+------+-----+---------+-------+
26 rows in set (0.01 sec)

mysql> DESC __all_dummy;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| key   | bigint(20) | NO   | PRI | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> select * from oceanbase.___all_virtual_proxy_schema;
ERROR 1146 (42S02): Table 'oceanbase.___all_virtual_proxy_schema' doesn't exist
mysql> select * from oceanbase.__all_dummy;
Empty set (0.00 sec)

为什么这两个虚拟表不存在呢？

1 个赞
#3

___all_virtual_proxy_schema使用sys租户查询链接端口是2883么
__all_dummy你查出来不是空的么

1 个赞
#4

好像不是这样

root@ubuntu:/home/wangxuexian2# obclient -uroot@sys#obdemo -h10.12.xx.xx -P2883 -p                     
Enter password: 
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 854675
Server version: OceanBase_CE 4.3.0.1 (r100000242024032211-0193a343bc60b4699ec47792c3fc4ce166a182f9) (Built Mar 22 2024 13:07:59)

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [(none)]> select * from oceanbase.___all_virtual_proxy_schema;
ERROR 1146 (42S02): Table 'oceanbase.___all_virtual_proxy_schema' doesn't exist
obclient [(none)]> 
obclient [(none)]> 

root@ubuntu:/home/wangxuexian2# obclient -uroot@proxysys#obdemo -h10.12.xx.xx -P2883 -p --proxy-mode -c
Enter password: 
Welcome to the OceanBase.  Commands end with ; or \g.
Your OceanBase connection id is 627599
Server version: 

Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

obclient [(none)]> select * from oceanbase.___all_virtual_proxy_schema;
ERROR 4016 (HY000): Internal error
obclient [(none)]>
#6

image
image1527×332 17.1 KB

参考这里查询该虚拟视图需要where条件 tenant_name table_name database_name不为空

#7

明白了 (tenant,database,table) 查询这个表，这个三元组必须带上。