1、版本
3.2.4
2、架构
3fe+3be分开部署
3、背景
在starrocks中创建mysql外表，通过insert into select * 的方式把mysql的数据导入到starrocks中去。
涉及到四张表，有二张表导入过程中报错，另外二张表导入成功。表数据量在2KW+。报错表的错误信息如下：
1064 - mysql row data parse error, column_data_type=51
即使通过分段导入的方式也是同样的报错，但是load_tracking_logs没有任何明确的报错信息，无法定位和解决问题。
4、报错日志：
2024-04-03 10:32:52,511 WARN (starrocks-mysql-nio-pool-146|333685) [StmtExecutor.handleDMLStmt():2106] failed to handle stmt [insert into boss_success_auction WITH LABEL m5 select * from boss_success_auction_external] label: m5
com.starrocks.common.DdlException: mysql row data parse error, column_data_type=51
at com.starrocks.common.ErrorReport.reportDdlException(ErrorReport.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:1957) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.handleDMLStmtWithProfile(StmtExecutor.java:1747) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:648) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:395) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:589) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:883) ~[starrocks-fe.jar:?]
at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:69) ~[starrocks-fe.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:834) ~[?:?]
2024-04-03 10:32:52,514 INFO (starrocks-mysql-nio-pool-146|333685) [DatabaseTransactionMgr.abortTransaction():1349] transaction:[TransactionState. txn_id: 1457, label: m5, db id: 10400, table id list: 39698, callback id: -1, coordinator: FE: 172.18.20.51, transaction status: ABORTED, error replicas num: 0, replica ids: , prepare time: 1712111572325, write end time: -1, allow commit time: -1, commit time: -1, finish time: 1712111572512, total cost: 187ms, reason: mysql row data parse error, column_data_type=51
] successfully rollback
2024-04-03 10:32:52,519 INFO (starrocks-mysql-nio-pool-146|333685) [QeProcessorImpl.unregisterQuery():150] deregister query id = 7858d262-f162-11ee-bf10-ae0d10ee890a
2024-04-03 10:32:52,519 WARN (starrocks-mysql-nio-pool-146|333685) [StmtExecutor.handleDMLStmtWithProfile():1749] DML statement(insert into boss_success_auction WITH LABEL m5 select * from boss_success_auction_external) process failed.
com.starrocks.common.UserException: mysql row data parse error, column_data_type=51
at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:2154) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.handleDMLStmtWithProfile(StmtExecutor.java:1747) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:648) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:395) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:589) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:883) ~[starrocks-fe.jar:?]
at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:69) ~[starrocks-fe.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
at java.lang.Thread.run(Thread.java:834) ~[?:?]
Caused by: com.starrocks.common.DdlException: mysql row data parse error, column_data_type=51
at com.starrocks.common.ErrorReport.reportDdlException(ErrorReport.java:99) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.handleDMLStmt(StmtExecutor.java:1957) ~[starrocks-fe.jar:?]
... 9 more
2024-04-03 10:32:52,519 INFO (starrocks-mysql-nio-pool-146|333685) [StmtExecutor.execute():698] execute Exception, sql: insert into boss_success_auction WITH LABEL m5 select * from boss_success_auction_external, error: mysql row data parse error, column_data_type=51
2024-04-03 10:32:55,627 INFO (colocate group clone checker|106) [ColocateTableBalancer.matchGroups():903] finished to match colocate group. cost: 0 ms, in lock time: 0 ms
2024-04-03 10:32:57,448 INFO (tablet checker|35) [TabletChecker.doCheck():426] finished to check tablets. isUrgent: true, unhealthy/total/added/in_sched/not_ready: 0/0/0/0/0, cost: 0 ms, in lock time: 0 ms, wait time: 0ms
2024-04-03 10:32:57,452 INFO (tablet checker|35) [TabletChecker.doCheck():426] finished to check tablets. isUrgent: false, unhealthy/total/added/in_sched/not_ready: 0/2312/0/0/0, cost: 3 ms, in lock time: 3 ms, wait time: 0ms
2024-04-03 10:32:57,452 INFO (tablet checker|35) [TabletChecker.runAfterCatalogReady():214] TStat :
TStat num of tablet check round: 85782 (+1)
TStat cost of tablet check(ms): 262388 (+3)
TStat num of tablet checked in tablet checker: 191269020 (+2312)
TStat num of unhealthy tablet checked in tablet checker: 6 (+0)
TStat num of tablet being added to tablet scheduler: 6 (+0)
TStat num of tablet schedule round: 1715371 (+20)
TStat cost of tablet schedule(ms): 35771 (+0)
TStat num of tablet being scheduled: 6 (+0)
TStat num of tablet being scheduled succeeded: 6 (+0)
TStat num of tablet being scheduled failed: 0 (+0)
TStat num of tablet being scheduled discard: 0 (+0)
TStat num of tablet priority upgraded: 0 (+0)
TStat num of clone task: 6 (+0)
TStat num of clone task succeeded: 6 (+0)
TStat num of clone task failed: 0 (+0)
TStat num of clone task timeout: 0 (+0)
TStat num of replica missing error: 0 (+0)
TStat num of replica version missing error: 6 (+0)
TStat num of replica unavailable error: 0 (+0)
TStat num of replica redundant error: 0 (+0)
TStat num of replica missing in cluster error: 0 (+0)
TStat num of balance scheduled: 0 (+0)
TStat num of colocate replica mismatch: 0 (+0)
TStat num of colocate replica redundant: 0 (+0)
TStat num of colocate balancer running round: 0 (+0)
2024-04-03 10:33:02,453 INFO (nioEventLoopGroup-4-6|150) [HttpServerHandler.channelRead():107] receive http request. url: /metrics, thread id: 150, startTime: 1712111582446, latency: 6 ms
2024-04-03 10:33:02,453 INFO (nioEventLoopGroup-4-6|150) [HttpServerHandler.channelRead():109] receive http request. uri: /metrics?, thread id: 150, startTime: 1712111582446, latency: 6 ms
(END)
5、建表语句如下：
CREATE TABLE boss_success_auction (
id bigint(20) NOT NULL AUTO_INCREMENT ,
auction_id int(11) NOT NULL ,
auction_admin_id int(11) NOT NULL DEFAULT “0”,
auction_admin_name varchar(300) NULL ,
auction_distribution_id int(11) NULL ,
auction_distribution_time datetime NULL ,
auction_status smallint(6) NULL DEFAULT “400” ,
valid tinyint(4) NOT NULL DEFAULT “1” ,
auction_time datetime NULL ,
sure_list_time datetime NULL,
final_c_look_car_time datetime NULL DEFAULT “2000-01-01 00:00:00” ,
final_b_look_car_address varchar(300) NULL ,
final_dealer_id int(11) NULL ,
final_price int(11) NULL ,
mind_price int(11) NULL,
final_status tinyint(4) NULL DEFAULT “0” ,
zone_id int(11) NULL ,
final_b_look_car_time datetime NULL,
final_c_look_car_address varchar(300) NULL ,
rb_family varchar(300) NULL ,
brand varchar(300) NULL ,
source smallint(6) NULL,
auction_remake int(11) NULL ,
field_id int(11) NULL ,
license_number_new varchar(20) NULL,
bid_time datetime NULL ,
check_mode tinyint(4) NULL ,
reception_final_price int(11) NULL,
work_type tinyint(4) NULL DEFAULT “2” ,
update_time datetime NOT NULL ,
timeout_status tinyint(4) NULL DEFAULT “0” ,
request_buy_person_id int(11) NULL ,
bolt_saled_status tinyint(4) NULL DEFAULT “-1” ,
data_source_id int(11) NULL ,
channel_source_id int(11) NULL ,
audit_status tinyint(4) NULL DEFAULT “0”,
bid_num int(11) NULL DEFAULT “0”,
distribute_state int(11) NULL DEFAULT “0” ,
create_time datetime NOT NULL ,
__modify_time__ datetime NOT NULL
) ENGINE=OLAP
PRIMARY KEY(id)
DISTRIBUTED BY HASH(id)
ORDER BY(auction_status, bid_time)
PROPERTIES (
“replication_num” = “3”,
“in_memory” = “false”,
“enable_persistent_index” = “true”,
“replicated_storage” = “true”,
“compression” = “LZ4”
);;
外表结构如下：
CREATE TABLE boss_success_auction_external (
id bigint(20) NOT NULL AUTO_INCREMENT ,
auction_id int(11) NOT NULL ,
auction_admin_id int(11) NOT NULL DEFAULT “0”,
auction_admin_name varchar(300) NULL ,
auction_distribution_id int(11) NULL ,
auction_distribution_time datetime NULL ,
auction_status smallint(6) NULL DEFAULT “400” ,
valid tinyint(4) NOT NULL DEFAULT “1” ,
auction_time datetime NULL ,
sure_list_time datetime NULL,
final_c_look_car_time datetime NULL DEFAULT “2000-01-01 00:00:00” ,
final_b_look_car_address varchar(300) NULL ,
final_dealer_id int(11) NULL ,
final_price int(11) NULL ,
mind_price int(11) NULL,
final_status tinyint(4) NULL DEFAULT “0” ,
zone_id int(11) NULL ,
final_b_look_car_time datetime NULL,
final_c_look_car_address varchar(300) NULL ,
rb_family varchar(300) NULL ,
brand varchar(300) NULL ,
source smallint(6) NULL,
auction_remake int(11) NULL ,
field_id int(11) NULL ,
license_number_new varchar(20) NULL,
bid_time datetime NULL ,
check_mode tinyint(4) NULL ,
reception_final_price int(11) NULL,
work_type tinyint(4) NULL DEFAULT “2” ,
update_time datetime NOT NULL ,
timeout_status tinyint(4) NULL DEFAULT “0” ,
request_buy_person_id int(11) NULL ,
bolt_saled_status tinyint(4) NULL DEFAULT “-1” ,
data_source_id int(11) NULL ,
channel_source_id int(11) NULL ,
audit_status tinyint(4) NULL DEFAULT “0”,
bid_num int(11) NULL DEFAULT “0”,
distribute_state int(11) NULL DEFAULT “0” ,
create_time datetime NOT NULL ,
__modify_time__ datetime NOT NULL
)ENGINE=MYSQL
PROPERTIES (
“host” = “ip”,
“port” = “port”,
“user” = “user”,
“password” = “passwd”,
“database” = “test”,
“table” = “boss_success_auction_external”
);;
6、另外一张表开始任务直接报错，报错信息依然不明确
7、麻烦看看能否解决？故障信息是否可以打印更加明确？还是因为底层insert into select *的逻辑有问题？
补充：
mysql中的建表语句
CREATE TABLE BOSS_SUCCESS_AUCTION (
ID int NOT NULL AUTO_INCREMENT,
AUCTION_ID int NOT NULL ,
AUCTION_ADMIN_ID int NOT NULL DEFAULT ‘0’ ,
AUCTION_ADMIN_NAME varchar(30) DEFAULT NULL ,
AUCTION_DISTRIBUTION_ID int DEFAULT NULL,
AUCTION_DISTRIBUTION_TIME timestamp NULL DEFAULT NULL,
AUCTION_STATUS smallint DEFAULT ‘400’,
VALID tinyint NOT NULL DEFAULT ‘1’ ,
AUCTION_TIME timestamp NULL DEFAULT NULL ,
SURE_LIST_TIME timestamp NULL DEFAULT NULL COMMENT ‘邀约确认列表时间控制\n’,
FINAL_C_LOOK_CAR_TIME timestamp NULL DEFAULT ‘0000-00-00 00:00:00’ ,
FINAL_B_LOOK_CAR_ADDRESS varchar(150) DEFAULT NULL ,
FINAL_DEALER_ID int DEFAULT NULL ,
FINAL_PRICE int DEFAULT NULL ,
MIND_PRICE int DEFAULT NULL ,
FINAL_STATUS tinyint DEFAULT ‘0’ ,
ZONE_ID int DEFAULT NULL ,
FINAL_B_LOOK_CAR_TIME timestamp NULL DEFAULT NULL,
FINAL_C_LOOK_CAR_ADDRESS varchar(150) DEFAULT NULL ,
RB_FAMILY varchar(100) DEFAULT NULL ,
BRAND varchar(100) DEFAULT NULL ,
SOURCE smallint DEFAULT NULL,
AUCTION_REMAKE int DEFAULT NULL,
FIELD_ID int DEFAULT NULL ,
LICENSE_NUMBER_NEW varchar(20) DEFAULT NULL,
BID_TIME timestamp NULL DEFAULT NULL ,
CHECK_MODE tinyint DEFAULT NULL ,
RECEPTION_FINAL_PRICE int DEFAULT NULL ,
WORK_TYPE tinyint DEFAULT ‘2’ ,
UPDATE_TIME datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘更新时间’,
TIMEOUT_STATUS tinyint DEFAULT ‘0’ ,
REQUEST_BUY_PERSON_ID int DEFAULT NULL,
BOLT_SALED_STATUS tinyint DEFAULT ‘-1’ ,
DATA_SOURCE_ID int DEFAULT NULL ,
CHANNEL_SOURCE_ID int DEFAULT NULL,
AUDIT_STATUS tinyint DEFAULT ‘0’,
BID_NUM int DEFAULT ‘0’ ,
DISTRIBUTE_STATE int DEFAULT ‘0’,
CREATE_TIME datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
__MODIFY_TIME__ datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT ‘BI’,
PRIMARY KEY (ID),
KEY IDX_AUCTION_DISTRIBUTION_TIME (AUCTION_DISTRIBUTION_TIME,AUCTION_STATUS,ZONE_ID),
KEY IDX_RB_FAMILY (RB_FAMILY,BRAND,ZONE_ID,AUCTION_STATUS),
KEY IDX_BRAND_AUCTION_STATUS (BRAND,AUCTION_STATUS,ZONE_ID),
KEY idx_bid_time_AUCTION_ADMIN_ID (BID_TIME,AUCTION_ADMIN_ID,AUCTION_STATUS,AUCTION_TIME,TIMEOUT_STATUS),
KEY IDX_AUCTION_ADMIN_ID_ZONE (AUCTION_ADMIN_ID,ZONE_ID),
KEY IDX_AUCTION_STATUS_FINAL_DEALER_ID (AUCTION_STATUS,FINAL_DEALER_ID,ZONE_ID,BID_TIME),
KEY IDX_UNION_ID_FIELDID_AUCTIONID (AUCTION_ID,FIELD_ID),
KEY IDX_AUCTION_TIME (AUCTION_TIME),
KEY IDX_AUCTION_STATUS_li (LICENSE_NUMBER_NEW,AUCTION_STATUS,ZONE_ID),
KEY IDX_CREATE_TIME (CREATE_TIME) USING BTREE,
KEY IDX__MODIFY_TIME__ (__MODIFY_TIME__),
KEY idx_2 (AUCTION_ID,BID_TIME,FINAL_PRICE),
KEY IDX_VALID_BID_TIME (VALID,BID_TIME),
KEY IDX_VALID_BID_TIME_AUCTION_ID (VALID,BID_TIME,AUCTION_ID),
KEY IDX_VALID_BID_TIME_AUCTION_ID_CREATE_TIME (VALID,BID_TIME,AUCTION_ID,CREATE_TIME),
KEY IDX_AUCTION_STATUS_BID_TIME (AUCTION_STATUS,BID_TIME),
KEY IDX_BID_TIME_AUCTION_STATUS_ZONE_ID (BID_TIME,AUCTION_STATUS,ZONE_ID),
KEY IDX_AUCTION_STATUS_TIMEOUT_STATUS_ZONE_ID (AUCTION_STATUS,TIMEOUT_STATUS,ZONE_ID),
KEY IDX_AUCTION_STATUS_TIMEOUT_STATUS (AUCTION_STATUS,TIMEOUT_STATUS),
KEY IDX_AUCTION_STATUS (AUCTION_STATUS),
KEY IDX_UPDATE_TIME (UPDATE_TIME)
) ENGINE=InnoDB AUTO_INCREMENT=15111389 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ;