jitawangzi opened a new issue, #21902: URL: https://github.com/apache/shardingsphere/issues/21902
### 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 data less than 0 successfully **with useServerPrepStmts=true and cachePrepStmts=true** ### Actual behavior Data truncation: Out of range value for column ### 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; ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
