Issue 1:
Question

I try to use ShardingProxy to my program ,and i found a problem that is :

Insert speed of ShardingProxy is very slow.

I write a program to transfer my old data via Jdbc( batch).

insert data for a whole day only one million data was inserted

Sharding-Proxy 4.0.0 (2020 Jan 13)

Is there any config problem ? (I use snow flake to generate id )

12 subtable(one table per month) just insert .

I have read the old related issue , If this is the parse problem , what is your plan to fix this?

Issue 2:
Which version of ShardingSphere did you use?

5.2.0

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

insert short and byte data less than 0 successfully with useServerPrepStmts=true and cachePrepStmts=true

Actual behavior

Data truncation: Out of range value for column

Reason analyze (If you can)

class MySQLInt2BinaryProtocolValue

@Override
public Object read(final MySQLPacketPayload payload) {
return payload.readInt2();
}

public int readInt2() {
return byteBuf.readUnsignedShortLE();
}

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

it is same as #15984 #10314 , has been fixed，but when i add useServerPrepStmts=true and cachePrepStmts=true to jdbc url，it reappeared.

i have to set the useServerPrepStmts=true , otherwise it will cause another problem #21518 ， blob data cannot be saved correctly

Example codes for reproduce this issue (such as a github link).

https://github.com/jitawangzi/ShardingSphereProxyBlobTest.git

all the code and configuration 、sql 、 etc are in the project.

run class Main directly

DataRecord record = new DataRecord();
byte[] oriArray = new byte[] { 1, 0, 99, 111, 109, 46, 108, 115, 46, 114, 112, 99, 46, 115, 101, 114, 118, 101, 114, 46, 82,
112, 99, 82, 101, 113, 117, 101, 115, -12, 49, 48, 46, 48, 46, 52, 46, 49, -75, 75, 74, 65, 104, 115, 100, 97, 107, 100,
104, 97, 115, 107, -28, 115, 101, 116, 68, 97, 116, -31, 1, 1, 91, 76, 106, 97, 118, 97, 46, 108, 97, 110, 103, 46, 79,
98, 106, 101, 99, 116, -69, 6, 9, -64, -39, -86, 20, 3, 115, 100, 104, 98, 115, 97, 104, 100, 98, 115, -31, 1, 2, 99,
111, 109, 46, 108, 115, 46, 114, 112, 99, 46, 115, 101, 114, 118, 101, 114, 46, 82, 112, 99, 82, 101, 115, 112, 111,
110, 115, -27, 0, 0, 0, 3, 115, 107, 104, 100, 98, 115, 107, 100, 117, 104, 97, 108, 100, 115, 106, 102, 100, 107, 106,
97, 102, 110, 97, 100, 102, -22, 10, 66, 61, -8, -5, 108, 25, 0, 0, 3, 2, 0, 3, 0, 0 };
record.setBlobData(oriArray);
record.setIntData(1);
// If set this, will cause an exception : Data truncation: Out of range value
record.setShortData((short) -3);

record.setUuid(UUID.randomUUID().toString());
mapper.insert(record);

DataRecord recordSelect = mapper.selectByPrimaryKey(record.getUuid());
System.out.println();
System.out.println("binary data equals result : " + Arrays.equals(record.getBlobData(), recordSelect.getBlobData()));
System.out.println();
System.out.println("int data equals result : " + record.getIntData().equals(recordSelect.getIntData()));

record.setShortData((short) -3); ,

the db url in jdbc.properties

dburl=jdbc:mysql://39.106.203.93:3307/test_sharding?autoReconnect=true&zeroDateTimeBehavior=convertToNull&characterEncoding=UTF-8&serverTimezone=UTC&useServerPrepStmts=true&cachePrepStmts=true

after remove useServerPrepStmts=true&cachePrepStmts=true , the short data insert correctly，but cause another problem:

binary data equals result : false

"config-sharding.yaml"

schemaName: test_sharding

dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:51234/test?serverTimezone=UTC&useSSL=false
username: sharding
password: sharding
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1

"server.yaml"

rules:
- !AUTHORITY
users:
- sharding@:sharding
provider:
type: ALL_PRIVILEGES_PERMITTED
- !TRANSACTION
defaultType: LOCAL
- !SQL_PARSER
sqlCommentParseEnabled: true
sqlStatementCache:
initialCapacity: 2000
maximumSize: 65535
parseTreeCache:
initialCapacity: 128
maximumSize: 1024

props:
max-connections-size-per-query: 3
kernel-executor-size: 16
proxy-frontend-flush-threshold: 128
proxy-opentracing-enabled: false
proxy-hint-enabled: false
sql-show: true
check-table-metadata-enabled: false
show-process-list-enabled: false
proxy-backend-query-fetch-size: -1
check-duplicate-table-enabled: false
proxy-frontend-executor-size: 0
proxy-backend-executor-suitable: OLAP
proxy-frontend-max-connections: 0
proxy-mysql-default-version: 8.0.26

table sql :

CREATE TABLE `data_record` (
`uuid` varchar(40) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`blob_data` blob,
`int_data` int DEFAULT NULL,
`short_data` smallint DEFAULT NULL,
`byte_data` tinyint DEFAULT NULL,
`boolean_data` tinyint(1) DEFAULT NULL,
`string_data` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci;