mysql 5.7 迁移到 OceanBase 5.7.25 sql 不一直 修改 nacos 2.4.0 修改配置报错

OceanBase 技术问题
#1

OceanBase 版本 ：5.7.25-OceanBase_ActionDB_CE-v4.2.1.5

CREATE TABLE his_config_info (
id bigint(64) unsigned NOT NULL,
nid bigint(20) unsigned NOT NULL AUTO_INCREMENT,
data_id varchar(255) COLLATE utf8_bin NOT NULL,
group_id varchar(128) COLLATE utf8_bin NOT NULL,
app_name varchar(128) COLLATE utf8_bin DEFAULT NULL COMMENT ‘app_name’,
content longtext COLLATE utf8_bin NOT NULL,
md5 varchar(32) COLLATE utf8_bin DEFAULT NULL,
gmt_create datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
gmt_modified datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
src_user text COLLATE utf8_bin,
src_ip varchar(50) COLLATE utf8_bin DEFAULT NULL,
op_type char(10) COLLATE utf8_bin DEFAULT NULL,
tenant_id varchar(128) COLLATE utf8_bin DEFAULT ‘’ COMMENT ‘租户字段’,
encrypted_data_key varchar(1024) COLLATE utf8_bin NOT NULL DEFAULT ‘’ COMMENT ‘秘钥’,
PRIMARY KEY (nid),
KEY idx_gmt_create (gmt_create),
KEY idx_gmt_modified (gmt_modified),
KEY idx_did (data_id)
) ENGINE=InnoDB AUTO_INCREMENT=684 DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT=‘多租户改造’;

执行后变成
– nacos_config.his_config_info definition

CREATE TABLE his_config_info (
id bigint(64) unsigned NOT NULL,
nid bigint(20) unsigned NOT NULL AUTO_INCREMENT,
data_id varchar(255) COLLATE utf8mb4_bin NOT NULL,
group_id varchar(128) COLLATE utf8mb4_bin NOT NULL,
app_name varchar(128) COLLATE utf8mb4_bin DEFAULT NULL COMMENT ‘app_name’,
content longtext COLLATE utf8mb4_bin NOT NULL,
md5 varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
gmt_create datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
gmt_modified datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
src_user text COLLATE utf8mb4_bin DEFAULT NULL,
src_ip varchar(50) COLLATE utf8mb4_bin DEFAULT NULL,
op_type char(10) COLLATE utf8mb4_bin DEFAULT NULL,
tenant_id varchar(128) COLLATE utf8mb4_bin DEFAULT ‘’ COMMENT ‘租户字段’,
encrypted_data_key varchar(1024) COLLATE utf8mb4_bin NOT NULL DEFAULT ‘’ COMMENT ‘秘钥’,
PRIMARY KEY (nid),
KEY idx_gmt_create (gmt_create) BLOCK_SIZE 16384 LOCAL,
KEY idx_gmt_modified (gmt_modified) BLOCK_SIZE 16384 LOCAL,
KEY idx_did (data_id) BLOCK_SIZE 16384 LOCAL
) AUTO_INCREMENT = 686 AUTO_INCREMENT_MODE = ‘ORDER’ DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 3 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 COMMENT = ‘多租户改造’;

现在导致的问题的 nacos 修改报错

image
image1884×62 9.99 KB

image
image1135×467 6.56 KB

caused: PreparedStatementCallback;SQL[
INSERT INTO his_config_info(id,
data_id,
group_id,
tenant_id,
app_name,
content,
md5,
src_ip,
src_user,
gmt_modified,
op_type,
encrypted_data_key)VALUES(?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?,
?)
];0;nested exception is java.sql.SQLException: 0;caused: 0;caused: 0;

#4

找一条实际有值的语句测试一下
autocommit=0;
sql;
rollback; --报错不用执行这个

如果语句报错 根据下面的查询一下 日志信息
alter system set enable_rich_error_msg=true;
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]
[root@x.x.x.1 ~]$ grep “YB420BA1CC68-000615A0A8EA5E38-0-0” rootservice.log
[root@x.x.x.1 ~]$ grep “YB420BA1CC68-000615A0A8EA5E38-0-0” observer.log
alter system set enable_rich_error_msg=false;

#5

能给出具体的 案例吗，我这里操作不了 数据服务器，只能在客户端处理

#6

主要是我们也是根据你具体反映的问题 分析的 也是根据经验 给你合理的建议查询问题 我们也没有详细具体的方案
1、找出具体的语句，在数据库服务器上执行一下，测试看看，是插入的值有问题，还是sql的问题；
2、查看sql的执行时间，是直接报错，还是执行时间长报的错。
3、排查语句执行的日志信息
如果语句报错 根据下面的查询一下 日志信息
alter system set enable_rich_error_msg=true;
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]
[root@x.x.x.1 ~]$ grep “YB420BA1CC68-000615A0A8EA5E38-0-0” rootservice.log
[root@x.x.x.1 ~]$ grep “YB420BA1CC68-000615A0A8EA5E38-0-0” observer.log
alter system set enable_rich_error_msg=false