You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/11/02 03:57:01 UTC
[GitHub] [shardingsphere] jitawangzi opened a new issue, #21902: Data truncation: Out of range value for column when set useServerPrepStmts=true to jdbc url.
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TOP-LH commented on issue #21902: Data truncation: Out of range value for column when set useServerPrepStmts=true to jdbc url.
Posted by GitBox <gi...@apache.org>.
TOP-LH commented on issue #21902:
URL: https://github.com/apache/shardingsphere/issues/21902#issuecomment-1331752522
> > > > Maybe we should convert the result of `MySQLInt2BinaryProtocolValue.read` to short.
> > >
> > >
> > > I tried changing this, but found this would affect unsigned type.
> >
> >
> > So I need to wait until 5.2.2 to fix this bug?
>
> You may use `int` to avoid this issue.
不好意思看错了, 我的问题是这个(https://github.com/apache/shardingsphere/issues/6861)
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #21902: Data truncation: Out of range value for column when set useServerPrepStmts=true to jdbc url.
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #21902:
URL: https://github.com/apache/shardingsphere/issues/21902#issuecomment-1301610262
Maybe we should convert the result of `MySQLInt2BinaryProtocolValue.read` to short.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #21902: Data truncation: Out of range value for column when set useServerPrepStmts=true to jdbc url.
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #21902:
URL: https://github.com/apache/shardingsphere/issues/21902#issuecomment-1331735206
> > > Maybe we should convert the result of `MySQLInt2BinaryProtocolValue.read` to short.
> >
> >
> > I tried changing this, but found this would affect unsigned type.
>
> So I need to wait until 5.2.2 to fix this bug?
You may use `int` to avoid this issue.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #21902: Data truncation: Out of range value for column when set useServerPrepStmts=true to jdbc url.
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #21902: Data truncation: Out of range value for column when set useServerPrepStmts=true to jdbc url.
URL: https://github.com/apache/shardingsphere/issues/21902
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #21902: Data truncation: Out of range value for column when set useServerPrepStmts=true to jdbc url.
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #21902:
URL: https://github.com/apache/shardingsphere/issues/21902#issuecomment-1308573182
> Maybe we should convert the result of `MySQLInt2BinaryProtocolValue.read` to short.
I tried changing this, but found this would affect unsigned type.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #21902: Data truncation: Out of range value for column when set useServerPrepStmts=true to jdbc url.
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #21902:
URL: https://github.com/apache/shardingsphere/issues/21902#issuecomment-1300793652
Hi @TeslaCN , could you take a look? I think you can help 😀
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #21902: Data truncation: Out of range value for column when set useServerPrepStmts=true to jdbc url.
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #21902:
URL: https://github.com/apache/shardingsphere/issues/21902#issuecomment-1301608205
I found the changes in https://github.com/apache/shardingsphere/pull/6048/files#diff-dc7e577da6c67af82ecb48b9ee713846b0da9e66a5a6233e9e809bfc333b4fa7
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #21902: Data truncation: Out of range value for column when set useServerPrepStmts=true to jdbc url.
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #21902:
URL: https://github.com/apache/shardingsphere/issues/21902#issuecomment-1337075391
ShardingSphere-Proxy is unable to handle unsigned type properly. This is not easy to fix. I'm removing milestone for now.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TOP-LH commented on issue #21902: Data truncation: Out of range value for column when set useServerPrepStmts=true to jdbc url.
Posted by GitBox <gi...@apache.org>.
TOP-LH commented on issue #21902:
URL: https://github.com/apache/shardingsphere/issues/21902#issuecomment-1331728424
> > Maybe we should convert the result of `MySQLInt2BinaryProtocolValue.read` to short.
>
> I tried changing this, but found this would affect unsigned type.
So I need to wait until 5.2.2 to fix this bug?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #21902: Data truncation: Out of range value for column when set useServerPrepStmts=true to jdbc url.
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #21902:
URL: https://github.com/apache/shardingsphere/issues/21902#issuecomment-1308261356
There is a simple way to reproduce this issue.
```java
public static void main(String[] args) throws Exception {
try (Connection connection = getConnection()) {
try (Statement statement = connection.createStatement()) {
statement.executeUpdate("drop table if exists mysql_int");
statement.executeUpdate("create table mysql_int (val_big bigint not null, val_int int not null, val_medium mediumint not null, val_small smallint, val_tiny tinyint not null )");
}
try (PreparedStatement preparedStatement = connection.prepareStatement("insert into mysql_int values (?, ?, ?, ?, ?)")) {
preparedStatement.setLong(1, -1);
preparedStatement.setInt(2, -1);
preparedStatement.setShort(3, (short) -1);
preparedStatement.setShort(4, (short) -1);
preparedStatement.setByte(5, (byte) -1);
System.out.println(preparedStatement.executeUpdate());
}
}
}
private static Connection getConnection() throws Exception {
return DriverManager.getConnection("jdbc:mysql://127.0.0.1:13306/freedom?useSSL=false&useServerPrepStmts=true", "root", "root");
}
```
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org