<h2 dir="auto">Question</h2>
<p dir="auto">I try to use ShardingProxy to my program ,and i found a problem that is :<br>
Insert speed of ShardingProxy is very slow.</p>
<p dir="auto">I write a program to transfer my old data via Jdbc( batch).</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="        insert data for a whole day only one million data was inserted"><pre class="notranslate"><code class="notranslate">        insert data for a whole day only one million data was inserted
</code></pre></div>
<p dir="auto">Sharding-Proxy  4.0.0 (2020 Jan 13)</p>
<ol dir="auto">
<li>Is there any config problem ? (I use snow flake to generate id )<br>
12 subtable(one table per month)  just insert .</li>
<li>I have read the old related issue ,  If this is the parse problem , what is your plan to fix this?</li>
</ol>
<h3 dir="auto">Which version of ShardingSphere did you use?</h3>
<p dir="auto">5.2.0</p>
<h3 dir="auto">Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?</h3>
<p dir="auto">ShardingSphere-Proxy</p>
<h3 dir="auto">Expected behavior</h3>
<p dir="auto">insert short and byte data less than 0 successfully  <strong>with   useServerPrepStmts=true and  cachePrepStmts=true</strong></p>
<h3 dir="auto">Actual behavior</h3>
<p dir="auto">Data truncation: Out of range value for column</p>
<h3 dir="auto">Reason analyze (If you can)</h3>
<p dir="auto">class  <code class="notranslate">MySQLInt2BinaryProtocolValue</code></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="    @Override
    public Object read(final MySQLPacketPayload payload) {
        return payload.readInt2();
    }"><pre class="notranslate"><code class="notranslate">    @Override
    public Object read(final MySQLPacketPayload payload) {
        return payload.readInt2();
    }
</code></pre></div>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="    public int readInt2() {
        return byteBuf.readUnsignedShortLE();
    }"><pre class="notranslate"><code class="notranslate">    public int readInt2() {
        return byteBuf.readUnsignedShortLE();
    }
</code></pre></div>
<h3 dir="auto">Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.</h3>
<p dir="auto">it is same   as  <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="1165944755" data-permission-text="Title is private" data-url="https://github.com/apache/shardingsphere/issues/15984" data-hovercard-type="issue" data-hovercard-url="/apache/shardingsphere/issues/15984/hovercard" href="https://github.com/apache/shardingsphere/issues/15984">#15984</a>   <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="889740187" data-permission-text="Title is private" data-url="https://github.com/apache/shardingsphere/issues/10314" data-hovercard-type="issue" data-hovercard-url="/apache/shardingsphere/issues/10314/hovercard" href="https://github.com/apache/shardingsphere/issues/10314">#10314</a> ,  has been fixed，but when i  add useServerPrepStmts=true and  cachePrepStmts=true  to jdbc url，it reappeared.<br>
i have to set the useServerPrepStmts=true , otherwise it will cause another problem   <a class="issue-link js-issue-link" data-error-text="Failed to load title" data-id="1405498520" data-permission-text="Title is private" data-url="https://github.com/apache/shardingsphere/issues/21518" data-hovercard-type="issue" data-hovercard-url="/apache/shardingsphere/issues/21518/hovercard" href="https://github.com/apache/shardingsphere/issues/21518">#21518</a>  ， blob data cannot be saved correctly</p>
<h3 dir="auto">Example codes for reproduce this issue (such as a github link).</h3>
<p dir="auto"><a href="https://github.com/jitawangzi/ShardingSphereProxyBlobTest.git">https://github.com/jitawangzi/ShardingSphereProxyBlobTest.git</a><br>
all the code and  configuration 、sql  、 etc   are  in  the  project.</p>
<p dir="auto">run class  <code class="notranslate">Main</code>  directly</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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(&quot;binary data equals result : &quot; + Arrays.equals(record.getBlobData(), recordSelect.getBlobData()));
System.out.println();
System.out.println(&quot;int data equals result : &quot; + record.getIntData().equals(recordSelect.getIntData()));"><pre class="notranslate"><code class="notranslate">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()));
</code></pre></div>
<p dir="auto"><strong>record.setShortData((short) -3);</strong> ,<br>
the db url in jdbc.properties<br>
<code class="notranslate">dburl=jdbc:mysql://39.106.203.93:3307/test_sharding?autoReconnect=true&amp;zeroDateTimeBehavior=convertToNull&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC&amp;useServerPrepStmts=true&amp;cachePrepStmts=true</code></p>
<p dir="auto">after  remove useServerPrepStmts=true&amp;cachePrepStmts=true ,  the short data insert  correctly，but  cause another problem:<br>
<strong>binary data equals result : false</strong></p>
<p dir="auto">"config-sharding.yaml"</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="schemaName: test_sharding

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:51234/test?serverTimezone=UTC&amp;useSSL=false
    username: sharding
    password: sharding
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1"><pre class="notranslate"><code class="notranslate">schemaName: test_sharding

dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:51234/test?serverTimezone=UTC&amp;useSSL=false
    username: sharding
    password: sharding
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
    minPoolSize: 1
</code></pre></div>
<p dir="auto">"server.yaml"</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="
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
"><pre class="notranslate"><code class="notranslate">
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

</code></pre></div>
<p dir="auto">table sql :</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="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;"><pre class="notranslate"><code class="notranslate">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;
</code></pre></div>