kettle to import data into shardingsphere-proxy:Parameter index out of bounds

[Huletian]

When I use kettle to import data into shardingsphere-proxy, some table wile appear:caused by : Java sql. SQLException: Parameter index out of bounds. 23393 is not between valid values of 1 and 23392。

But,I am sure that my orginal table is consistent with the target table,and this problem will not occur when I change the target to MySQL sub database.

I used kettle to write a simple transfer,a table input and table outpit, the number of table output copies is 20,and the number of each submission is 3000 , Of Course，after reducing those two items,this error will not occur,but it will be too slow,I hope to help solve it. I put my table creation statement below (sharding key: school_code)

`

CREATE TABLE edudiagnose_dw.dw_student ( school_code varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT 'bj12z' COMMENT '学校编码', school_term_id int(11) NOT NULL DEFAULT 152 COMMENT '学期编码', diagnostician_id char(13) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '诊断者ID', school_seq smallint(6) NOT NULL COMMENT '学校序号', id_card_no varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '身份证号码', stu_sid varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '学号', stu_uid varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '电子学籍号', last_school varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '上一个就读学校', father_career varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '父亲职业', father_edu_bk varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '父亲受教育程度', mother_career varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '母亲职业', mother_edu_bk varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '母亲受教育程度', group_tag1 varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分组标签1', group_tag2 varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分组标签2', group_tag3 varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分组标签3', group_tag4 varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分组标签4', group_tag5 varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分组标签5', group_tag6 varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分组标签6', group_tag7 varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分组标签7', group_tag8 varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分组标签8', group_tag9 varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分组标签9', group_tag10 varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '分组标签10', PRIMARY KEY (school_code, school_term_id, diagnostician_id, school_seq) USING BTREE, INDEX diagnostician_id(diagnostician_id) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; `

[strongduanmu]

@Huletian Can you provide more information——ShardingSphere version and your sharding configuration?

[Huletian]

@Huletian Can you provide more information——ShardingSphere version and your sharding configuration?

ShardingSphere version:5.0.0(Updated on 2022.03.11)

configuration:

server.yaml:

mode:
  type: Standalone
  repository:
    type: File
  overwrite: false
rules:
  - !AUTHORITY
    users:
      - root@%:root
      - sharding@:sharding
    provider:
      type: ALL_PRIVILEGES_PERMITTED
  - !TRANSACTION
    defaultType: XA
    providerType: Atomikos
  - !SQL_PARSER
    sqlCommentParseEnabled: true
props:
  sql-comment-parse-enabled: true
  check-table-metadata-enabled: false
  proxy-backend-query-fetch-size: 10000 
  proxy-backend-executor-suitable: OLTP
  proxy-frontend-max-connections: 0

DistSQL

CREATE sharding TABLE rule dw_student (
	datanodes ( "ds_${['ceshi1','ceshi2']}.dw_student" ),
	database_strategy (
		type = standard,
		sharding_column = school_code,
		sharding_algorithm (
			TYPE (
				NAME = inline,
			PROPERTIES ( "algorithm-expression" = "ds_${school_code}" )))) 
);

[TeslaCN]

Hi @Huletian Could you try the ShardingSphere 5.1.1? We did some enhancements about batched statements in https://github.com/apache/shardingsphere/issues/16124.

[Huletian]

Hi @Huletian Could you try the ShardingSphere 5.1.1? We did some enhancements about batched statements in #16124.

I tried 5.1.1, but some tables will still throw this exception. My concurrency is not very large, and only some tables will have this problem. I want to ask, is the problem caused by the column type of the table?

[TeslaCN]

Could you try finding out which SQL caused the exception?

[Huletian]

Could you try finding out which SQL caused the exception?

I also want to find the SQL that throws an exception, but I can't find it. Sorry

[github-actions[bot]]

Hello , this issue has not received a reply for several days. This issue is supposed to be closed.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.