OB 资源池大小写导致的bug

意大利面拌42号混凝土 · 2023 年11 月 30 日 14:57

【使用环境】测试环境
【 OB or 其他组件】OBSERVER
【使用版本】4.1
【问题描述】
创建租户的时候存在字符串的字母相同，但是只是大小写不同的资源池，创建租户时报错，

obclient [oceanbase]> CREATE TENANT IF NOT EXISTS sp1 CHARSET='utf8mb4', ZONE_LIST=('obzone1','obzone2','obzone3'), PRIMARY_ZONE='obzone1,obzone2,obzone3;', RESOURCE_POOL_LIST=('Aaaaaaaa_pool_obzone1','Aaaaaaaa_pool_obzone2','Aaaaaaaa_pool_obzone3') , LOCALITY='FULL{1}@obzone1,FULL{1}@obzone2,FULL{1}@obzone3', COLLATE = utf8mb4_general_ci SET VARIABLES ob_tcp_invited_nodes = '%',ob_compatibility_mode = 'MySQL'
    -> ;
ERROR 1242 (21000): Subquery returns more than 1 row

通过SQL 语句查询资源池信息，发现查出来的结果不区分大小写

obclient [oceanbase]> select * from dba_ob_resource_pools where name='Aaaaaaaa_pool_obzone1';
+------------------+-----------------------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
| RESOURCE_POOL_ID | NAME                  | TENANT_ID | CREATE_TIME                | MODIFY_TIME                | UNIT_COUNT | UNIT_CONFIG_ID | ZONE_LIST | REPLICA_TYPE |
+------------------+-----------------------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
|             1001 | aaaaAAAA_pool_obzone1 |      1002 | 2023-11-30 10:56:22.064387 | 2023-11-30 10:56:24.336200 |          1 |           1001 | obzone1   | FULL         |
|             1004 | Aaaaaaaa_pool_obzone1 |      NULL | 2023-11-30 11:10:10.538214 | 2023-11-30 14:49:20.423892 |          1 |           1004 | obzone1   | FULL         |
+------------------+-----------------------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+
2 rows in set (0.022 sec)

意大利面拌42号混凝土 · 2023 年11 月 30 日 15:03

应该是oceanbase.__all_resource_pool 这张表的字符排序问题，导致查询的时候没有区分大小写查出两个相同的资源池名字

和顺 · 2023 年11 月 30 日 15:13

麻烦重新执行一下创建租户的语句，然后把 rs节点 select svr_ip from dba_ob_servers where with_rootserver=‘yes’; 最新的 rootservice.log日志附件发一下。

意大利面拌42号混凝土 · 2023 年11 月 30 日 15:43

rootservice.log (94.8 KB)
附件已上传，执行的时候没有看到rootservice.log里面有增加日志，就客户端工具上返回上面的报错提示

和顺 · 2023 年11 月 30 日 16:13

感谢反馈，目前看这个应该是一个bug，麻烦方便的话可以在github上提个issue .

意大利面拌42号混凝土 · 2023 年11 月 30 日 18:15

github.com/oceanbase/oceanbase

[Bug]: ERROR 1242 (21000): Subquery returns more than 1 row

opened 10:14AM - 30 Nov 23 UTC

zsp108

type: bug

**Describe the bug** select * from __all_resource_pool where name ='aaaAA' no…t case sensitive **Environment** - OS Version and CPU Arch(`uname -a`): ```bash Linux 10-10-210-148 4.19.113-300.el7.x86_64 #1 SMP Mon Mar 30 21:50:38 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux ``` - OB Version(`LD_LIBRARY_PATH=../lib:$LD_LIBRARY_PATH ./observer -V`): **Fast Reproduce Steps(Required)** Steps to reproduce the behavior: ```sql create resource unit sp_unit min_cpu=1,MAX_CPU=1,MEMORY_SIZE=1073741824,log_disk_size=3221225472; create resource pool aaaaAAAA_pool_obzone1 unit='sp_unit',unit_num=1,ZONE_LIST=('obzone1','obzone2','obzone3'); create resource pool Aaaaaaaa_pool_obzone1 unit='sp_unit',unit_num=1,ZONE_LIST=('obzone1','obzone2','obzone3'); obclient [oceanbase]> CREATE TENANT IF NOT EXISTS sp1 CHARSET='utf8mb4', ZONE_LIST=('obzone1','obzone2','obzone3'), PRIMARY_ZONE='obzone1,obzone2,obzone3;', RESOURCE_POOL_LIST=('Aaaaaaaa_pool_obzone1') , LOCALITY='FULL{1}@obzone1,FULL{1}@obzone2,FULL{1}@obzone3', COLLATE = utf8mb4_general_ci SET VARIABLES ob_tcp_invited_nodes = '%',ob_compatibility_mode = 'MySQL' -> ; ERROR 1242 (21000): Subquery returns more than 1 row obclient [oceanbase]> select * from dba_ob_resource_pools where name='Aaaaaaaa_pool_obzone1'; +------------------+-----------------------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+ | RESOURCE_POOL_ID | NAME | TENANT_ID | CREATE_TIME | MODIFY_TIME | UNIT_COUNT | UNIT_CONFIG_ID | ZONE_LIST | REPLICA_TYPE | +------------------+-----------------------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+ | 1001 | aaaaAAAA_pool_obzone1 | 1002 | 2023-11-30 10:56:22.064387 | 2023-11-30 10:56:24.336200 | 1 | 1001 | obzone1 | FULL | | 1004 | Aaaaaaaa_pool_obzone1 | NULL | 2023-11-30 11:10:10.538214 | 2023-11-30 14:49:20.423892 | 1 | 1004 | obzone1 | FULL | +------------------+-----------------------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+ 2 rows in set (0.022 sec) ``` **Expected behavior** ```sql obclient [oceanbase]> select * from dba_ob_resource_pools where name='Aaaaaaaa_pool_obzone1'; +------------------+-----------------------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+ | RESOURCE_POOL_ID | NAME | TENANT_ID | CREATE_TIME | MODIFY_TIME | UNIT_COUNT | UNIT_CONFIG_ID | ZONE_LIST | REPLICA_TYPE | +------------------+-----------------------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+ | 1004 | Aaaaaaaa_pool_obzone1 | NULL | 2023-11-30 11:10:10.538214 | 2023-11-30 14:49:20.423892 | 1 | 1004 | obzone1 | FULL | +------------------+-----------------------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+ 2 rows in set (0.022 sec) ``` **Actual Behavior** ```sql obclient [oceanbase]> select * from dba_ob_resource_pools where name='Aaaaaaaa_pool_obzone1'; +------------------+-----------------------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+ | RESOURCE_POOL_ID | NAME | TENANT_ID | CREATE_TIME | MODIFY_TIME | UNIT_COUNT | UNIT_CONFIG_ID | ZONE_LIST | REPLICA_TYPE | +------------------+-----------------------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+ | 1001 | aaaaAAAA_pool_obzone1 | 1002 | 2023-11-30 10:56:22.064387 | 2023-11-30 10:56:24.336200 | 1 | 1001 | obzone1 | FULL | | 1004 | Aaaaaaaa_pool_obzone1 | NULL | 2023-11-30 11:10:10.538214 | 2023-11-30 14:49:20.423892 | 1 | 1004 | obzone1 | FULL | +------------------+-----------------------+-----------+----------------------------+----------------------------+------------+----------------+-----------+--------------+ 2 rows in set (0.022 sec) ``` **Additional context** OB version :OceanBase_CE 4.1.0.0 (r100000192023032010-0265dfc6d00ff4f0ff4ad2710504a18962abaef6) (Built Mar 20 2023 10:12:05)

已提交