建了 table group 且是 key 分区 导入数据报错

OceanBase 技术问题
#1

image
image1920×348 145 KB

但是我看 bad 信息也没显示错误
image
image1920×677 385 KB

#3

上面的是你的程序的报错信息，OB的报错信息没贴吧。

你用诊断工具obdiag 分析一下OB日志：https://www.oceanbase.com/docs/common-obdiag-cn-1000000002200475

1 个赞
#4

我看的 bad 那个文件 里面没有爆错 事则是因为 2进制问题 加了 --ignore-unhex 就成功啦

1 个赞
#6

老师 analyze table 超时怎么整

image
image724×132 21.5 KB

1 个赞
#7

ob的版本发一下 具体的语句也发一下 obloader-obdumper的版本也发一下

1 个赞
#8

导入我自己解决啦 谢谢老师

1 个赞
#9

好的 可以分享一下 怎么解决的么？如果再有同类问题 也可以方便查看

1 个赞
#10

我一开始是用公有 ip 部署的集群，换成私有的就好了

1 个赞
#11

我又建了一个 table group 为 deep_group256
现在库里有 deep_group256 和 deep_group 2个group
下图的表在 deep_group256 也有一个但是名字不同，现在在 deep_group256 下建表为什么报错说 ERROR 1022 (23000): Can’t write; duplicate key in table ‘chat_message_256’

image
image1920×662 93.5 KB

1 个赞
#12

看着像建表sq有问题，能发一下看看么

1 个赞
#13

根据下面步骤 抓取一下日志信息
a. 登录系统租户，打开enable_rich_error_msg 参数
alter system set enable_rich_error_msg=true;
b. 登录业务租户，执行报错 SQL 语句，会直接返回执行节点 IP 和 trace_id 信息。
obclient [test]> select count(*) from t2;
ERROR 1146 (42S02): Table ‘test.t2’ doesn’t exist
[xx.xx.xx.1:2882] [2024-04-13 20:10:20.292087] [YB420BA1CC68-000615A0A8EA5E38-0-0]
c. 去 xx.xx.xx.1 节点过滤日志，如果最新日志无法过滤到，可以正则匹配多个日志进行过滤
[root@x.x.x.1 ~]$ grep “YB420BA1CC68-000615A0A8EA5E38-0-0” rootservice.log
[root@x.x.x.1 ~]$ grep “YB420BA1CC68-000615A0A8EA5E38-0-0” observer.log
d. 获取完日志信息后，关闭enable_rich_error_msg参数
alter system set enable_rich_error_msg=false;

2 个赞
#14

简单复现步骤
简单的 schema 没有问题

create tablegroup if not exists  debug1  binding true 

CREATE TABLE `debug_table` (
  `id` binary(16) NOT NULL,
  `name` VARCHAR(50) DEFAULT NULL,   -- 名称字段
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 创建时间
  PRIMARY KEY (`id`)                 -- 主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin  tablegroup='debug1' partition by key(id) partitions 256;

create tablegroup if not exists  debug2  binding true ;

CREATE TABLE `debug_table2` (
  `id` binary(16) NOT NULL,
  `name` VARCHAR(50) DEFAULT NULL,   -- 名称字段
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  -- 创建时间
  PRIMARY KEY (`id`)                 -- 主键
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin  tablegroup='debug2' partition by key(id) partitions 256;

反复尝试且有问题的 schema

create tablegroup if not exists test4 binding true ;


CREATE TABLE `test4` (
  `col1` binary(16) NOT NULL,
  `col2` varchar(48) NOT NULL,
  `col3` int NOT NULL,
  `col4` int DEFAULT NULL,
  `col5` enum('') DEFAULT NULL,
  `col6` enum('ASSISTANT','USER') NOT NULL,
  `col7` int NOT NULL,
  `col8` enum('WIP','FINISHED','CONTENT_FILTER','FAILED','INCOMPLETE','CONTEXT_LENGTH_EXCEEDED','TIMEOUT') NOT NULL,
  `col9` text DEFAULT NULL,
  `col10` binary(16) DEFAULT NULL,
  `col11` binary(16) DEFAULT NULL,
  `col12` enum('stop','length','content_filter','insufficient_system_resource','backend_err','backend_busy','context_length_exceeded','invalid_unicode_input','rate_limit_reached','generation_err','generation_timeout','manual_abort') DEFAULT NULL,
  `col13` binary(16) DEFAULT NULL,
  `col14` json DEFAULT NULL,
  `col15` tinyint(1) NOT NULL DEFAULT '0',
  `col16` float DEFAULT NULL,
  `col17` tinyint(1) NOT NULL DEFAULT '0',
  `col18` enum('INIT','SEARCHING','ANSWER','FINISHED','NO_RESULT','FAILED') DEFAULT NULL,
  `col19` binary(16) DEFAULT NULL,
  `col20` int NOT NULL DEFAULT '0',
  `col21` tinyint(1) NOT NULL DEFAULT '0',
  `col22` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `col23` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`col1`,`col3`) /*T![clustered_index] CLUSTERED */,
  KEY `fk_2` (`col11`),
  KEY `fk_3` (`col10`),
  CONSTRAINT `fk_1` FOREIGN KEY (`col1`) REFERENCES `chat_session` (`id`),
  CONSTRAINT `fk_2` FOREIGN KEY (`col11`) REFERENCES `chat_message_feedback` (`id`),
  CONSTRAINT `fk_3` FOREIGN KEY (`col10`) REFERENCES `chat_message_tip` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin  tablegroup='test4' partition by key(col1) partitions 4



create tablegroup if not exists test256 binding true ;

CREATE TABLE `test256` (
  `col1` binary(16) NOT NULL,
  `col2` varchar(48) NOT NULL,
  `col3` int NOT NULL,
  `col4` int DEFAULT NULL,
  `col5` enum('') DEFAULT NULL,
  `col6` enum('ASSISTANT','USER') NOT NULL,
  `col7` int NOT NULL,
  `col8` enum('WIP','FINISHED','CONTENT_FILTER','FAILED','INCOMPLETE','CONTEXT_LENGTH_EXCEEDED','TIMEOUT') NOT NULL,
  `col9` text DEFAULT NULL,
  `col10` binary(16) DEFAULT NULL,
  `col11` binary(16) DEFAULT NULL,
  `col12` enum('stop','length','content_filter','insufficient_system_resource','backend_err','backend_busy','context_length_exceeded','invalid_unicode_input','rate_limit_reached','generation_err','generation_timeout','manual_abort') DEFAULT NULL,
  `col13` binary(16) DEFAULT NULL,
  `col14` json DEFAULT NULL,
  `col15` tinyint(1) NOT NULL DEFAULT '0',
  `col16` float DEFAULT NULL,
  `col17` tinyint(1) NOT NULL DEFAULT '0',
  `col18` enum('INIT','SEARCHING','ANSWER','FINISHED','NO_RESULT','FAILED') DEFAULT NULL,
  `col19` binary(16) DEFAULT NULL,
  `col20` int NOT NULL DEFAULT '0',
  `col21` tinyint(1) NOT NULL DEFAULT '0',
  `col22` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `col23` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`col1`,`col3`) /*T![clustered_index] CLUSTERED */,
  KEY `fk_2` (`col11`),
  KEY `fk_3` (`col10`),
  CONSTRAINT `fk_1` FOREIGN KEY (`col1`) REFERENCES `chat_session` (`id`),
  CONSTRAINT `fk_2` FOREIGN KEY (`col11`) REFERENCES `chat_message_feedback` (`id`),
  CONSTRAINT `fk_3` FOREIGN KEY (`col10`) REFERENCES `chat_message_tip` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin  tablegroup='test256' partition by key(col1) partitions 256;

image
image1920×783 128 KB

#15

这边测试一下

#16

抓取的日志信息 也发一下

#17

就看到这个报错信息 日志我倒是没主意 但是步骤我都提供了 可稳定复现的

#18

版本号 查一下SHOW VARIABLES like ‘version_comment’;

#19 
mysql> SHOW VARIABLES like 'version_comment';
+-----------------+------------------------------------------------------------------------------------------------------------------+
| Variable_name   | Value                                                                                                            |
+-----------------+------------------------------------------------------------------------------------------------------------------+
| version_comment | OceanBase_CE 4.3.5.0 (r100000202024123117-5d6cb5cbc3f7c1ab6eb22e40abec8e160a8764d5) (Built Dec 31 2024 17:35:01) |
+-----------------+------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#20

这个信息 也查一下select * from oceanbase.gv$ob_servers;

#21

搂一搂详细日志看看

#22 
bclient(root@(none))[oceanbase]> select * from oceanbase.gv$ob_servers;
+--------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+--------------------+------------------+-------------------------+--------------+---------------------+-------------------------+-----------------------+
| SVR_IP       | SVR_PORT | ZONE  | SQL_PORT | CPU_CAPACITY | CPU_CAPACITY_MAX | CPU_ASSIGNED | CPU_ASSIGNED_MAX | MEM_CAPACITY | MEM_ASSIGNED | LOG_DISK_CAPACITY | LOG_DISK_ASSIGNED | LOG_DISK_IN_USE | DATA_DISK_CAPACITY | DATA_DISK_ASSIGNED | DATA_DISK_IN_USE | DATA_DISK_HEALTH_STATUS | MEMORY_LIMIT | DATA_DISK_ALLOCATED | DATA_DISK_ABNORMAL_TIME | SSL_CERT_EXPIRED_TIME |
+--------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+--------------------+------------------+-------------------------+--------------+---------------------+-------------------------+-----------------------+
| 10.138.1.138 |     2882 | zone1 |     2881 |           30 |               30 |           30 |               30 |  96636764160 |  91268055040 |      549755813888 |      281588793344 |    193005092864 |       549755813888 |               NULL |     418885140480 | NORMAL                  | 128849018880 |        549755813888 | NULL                    | NULL                  |
| 10.138.1.182 |     2882 | zone2 |     2881 |           30 |               30 |           30 |               30 |  96636764160 |  91268055040 |      549755813888 |      281588793344 |    193005092864 |       549755813888 |               NULL |     419776430080 | NORMAL                  | 128849018880 |        549755813888 | NULL                    | NULL                  |
| 10.138.1.222 |     2882 | zone3 |     2881 |           30 |               30 |           30 |               30 |  96636764160 |  91268055040 |      549755813888 |      281588793344 |    193005092864 |       549755813888 |               NULL |     419333931008 | NORMAL                  | 128849018880 |        549755813888 | NULL                    | NULL                  |
+--------------+----------+-------+----------+--------------+------------------+--------------+------------------+--------------+--------------+-------------------+-------------------+-----------------+--------------------+--------------------+------------------+-------------------------+--------------+---------------------+-------------------------+-----------------------+
3 rows in set (0.003 sec)