Oceanbase 企业版Oracle模式tablegroup使用异常

OB Cloud
#1

使用环境

  • 本地测试环境

使用版本

obclient  Ver 1.2.6 Distrib 5.7.24, for Linux (x86_64) using  EditLine wrapper

Connection id:		787342
Current database:	TEST
Current user:		SYS
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		OceanBase 3.2.3.0 (r20220429172811-141f0018b07e9f8d269bb5f6dbd020cd419eb3fc) (Built Apr 29 2022 17:39:23)
Protocol version:	10
Connection:		127.0.0.1 via TCP/IP
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
TCP port:		2883
Active			--------------

问题描述

  • 在Oracle模式下，创建tablegroup后创建表引用tablegroup报错00600的错误

复现路径

-- 创建tablegroup
SYS[TEST]> create tablegroup tgorder partition by hash partitions 3;
Query OK, 0 rows affected (2.03 sec)

-- 引用tablegroup报错
SYS[TEST]> create table t3 (c1 int,c2 int) tablegroup='tgorder' partition by hash(c1) partitions 3;
ORA-00600: internal error code, arguments: -5151, tablegroup not exist

-- sys租户下看不到创建成功的tablegroup 
root@127.0.0.1[oceanbase]> select tenant_id,tablegroup_id,tablegroup_name,comment from __all_tablegroup;
+-----------+---------------+-----------------+-------------------+
| tenant_id | tablegroup_id | tablegroup_name | comment           |
+-----------+---------------+-----------------+-------------------+
|         1 | 1099511627777 | oceanbase       | system tablegroup |
+-----------+---------------+-----------------+-------------------+
1 row in set (0.28 sec)

-- 尝试再次创建tablegroup报错，说明tablegroup确实存在
SYS[TEST]> create tablegroup tgorder partition by hash partitions 3;
ORA-00600: internal error code, arguments: -5159, tablegroup already exist

在MySQL模式下验证功能正常

root@127.0.0.1[oceanbase]> use test;
Database changed
root@127.0.0.1[test]> create tablegroup tgorder partition by hash partitions 3;
Query OK, 0 rows affected (3.40 sec)

root@127.0.0.1[test]> create table tt (c1 int,c2 int) tablegroup='tgorder' partition by hash(c1) partitions 3;
Query OK, 0 rows affected (5.67 sec)

## 通过租户自己的__all_tablegroup能查到tablegroup的定义
root@127.0.0.1[oceanbase]> select tenant_id,tablegroup_id,tablegroup_name,comment from __all_tablegroup;
+-----------+---------------+-----------------+-------------------+
| tenant_id | tablegroup_id | tablegroup_name | comment           |
+-----------+---------------+-----------------+-------------------+
|         0 |             1 | oceanbase       | system tablegroup |
|         0 |  549755814889 | tgorder         |                   |
+-----------+---------------+-----------------+-------------------+
2 rows in set (0.09 sec)

## 通过sys租户也查不到tablegroup的定义
root@127.0.0.1[oceanbase]> select tenant_id,tablegroup_id,tablegroup_name,comment from __all_tablegroup;
+-----------+---------------+-----------------+-------------------+
| tenant_id | tablegroup_id | tablegroup_name | comment           |
+-----------+---------------+-----------------+-------------------+
|         1 | 1099511627777 | oceanbase       | system tablegroup |
+-----------+---------------+-----------------+-------------------+
1 row in set (0.41 sec)

影响面

  1. Oracle模式下无法使用tablegroup功能
  2. sys租户下__all_tablegroup表没正常记录table_group的定义
  3. Oracle模式下如何查看已定义的tablegroup，没找到Oracle模式下tablegroup的元数据表。
#3

1、
SYS[TEST]> create table t3 (c1 int,c2 int) tablegroup=‘tgorder’ partition by hash(c1) partitions 3;
将这个引号去除
create table t3 (c1 int,c2 int) tablegroup=tgorder partition by hash(c1) partitions 3;
2、__all_tablegroup是查当前租户的，查其他租户的话使用__all_virtual_tablegroup
select * from __all_virtual_tablegroup
3、查看tablegroup定义可以用如下语句
SHOW TABLEGROUPS;
SHOW CREATE TABLEGROUP tgorder;

1 个赞
#4

感谢解答，用提供的方式进行验证正常。

-- 查看当前租户所有表组
SYS[TEST]> show tablegroups;
+-----------------+------------+---------------+
| TABLEGROUP_NAME | TABLE_NAME | DATABASE_NAME |
+-----------------+------------+---------------+
| TGORDER         | NULL       | NULL          |
| oceanbase       | NULL       | NULL          |
+-----------------+------------+---------------+
2 rows in set (0.03 sec)

-- 创建表并绑定表组
SYS[TEST]> create table t3 (c1 int,c2 int) tablegroup=tgorder partition by hash(c1) partitions 3;
Query OK, 0 rows affected (2.40 sec)

-- -- 查看当前租户所有表组，已进行表组关联
SYS[TEST]> show tablegroups;
+-----------------+------------+---------------+
| TABLEGROUP_NAME | TABLE_NAME | DATABASE_NAME |
+-----------------+------------+---------------+
| TGORDER         | T3         | TEST          |
| oceanbase       | NULL       | NULL          |
+-----------------+------------+---------------+
2 rows in set (0.01 sec)


-- sys租户查看所有用户表组
root@127.0.0.1[oceanbase]> select tenant_id,tablegroup_id,tablegroup_name from __all_virtual_tablegroup;
+-----------+------------------+-----------------+
| tenant_id | tablegroup_id    | tablegroup_name |
+-----------+------------------+-----------------+
|         1 |    1099511627777 | oceanbase       |
|      1007 | 1107208209170433 | oceanbase       |
|      1008 | 1108307720798209 | oceanbase       |
|      1010 | 1110506744053761 | oceanbase       |
|      1010 | 1111056499868650 | TGORDER         |
|      1011 | 1111606255681537 | oceanbase       |
|      1011 | 1112156011496425 | tgorder         |
+-----------+------------------+-----------------+
7 rows in set (0.89 sec)
#5

Oceanbase For MySQL Mode 表组指定时带单引号和不带单引号简单验证

-- 创建表组
root@127.0.0.1[test]> create tablegroup tgorder partition by hash partitions 3;
Query OK, 0 rows affected (4.05 sec)

-- 创建表绑定表组，tablegroup=tgorder不加单引号，可创建成功
root@127.0.0.1[test]> create table tt (c1 int,c2 int) tablegroup=tgorder partition by hash(c1) partitions 3;
Query OK, 0 rows affected (5.44 sec)

-- 删除表
root@127.0.0.1[test]> drop table tt;
drop tablegrupQuery OK, 0 rows affected (3.27 sec)

-- 重新创建表，tablegroup=tgorder加单引号，可创建成功
root@127.0.0.1[test]> create table tt (c1 int,c2 int) tablegroup='tgorder' partition by hash(c1) partitions 3;
Query OK, 0 rows affected (5.96 sec)

-- 查看表组信息
root@127.0.0.1[test]> show tablegroups;
+-----------------+------------+---------------+
| Tablegroup_name | Table_name | Database_name |
+-----------------+------------+---------------+
| oceanbase       | NULL       | NULL          |
| tgorder         | tt         | test          |
+-----------------+------------+---------------+
2 rows in set (0.56 sec)

基本结论

  1. 在OceanBase For MySQL模式下，创建表时指定表组有单引号和没单引号都可创建成功
  2. 在OceanBase For Oracle模式下，创建表时指定表组必须不带单引号才能成功
1 个赞