apache / apache/shardingsphere

sharding-proxy insert blob size is not correct

Open
#21,627 19 comments 0 reactions 1 assignee Claimed by @TeslaCN View on GitHub
db: MySQL in: proxy type: enhancement
Dominant language
Java
Stars
20.8k
Forks
6.9k
Avg merge
11h 35m
Merged PRs (30d)
326

Description

## Bug Report

### Which version of ShardingSphere did you use?

1. apache-shardingsphere-5.0.0-shardingsphere-proxy-bin
2. apache-shardingsphere-5.2.0-shardingsphere-proxy-bin

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

ShardingSphere-Proxy

### Behavior

![image](https://user-images.githubusercontent.com/2366973/196378119-f8dc70ce-5c06-42c0-844e-0bedb072c6fb.png)

above it, `id==1` insert by proxy and `id==2` insert by direct,maybe you think this tool(dbeaver) not support fine, i read this record and write to file,record(`id==1` )open fail

### Reason analyze (If you can)

config(seriver.yml) show sql, can see `insert into blob_clob(id,b_blob,c_clob) values(1,_binary'......','........')`,maybe get sql from packet ?

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

* **config-shadow.yml**
```yml
#....
######################################################################################################
#
# If you want to connect to MySQL, you should manually copy MySQL driver to lib directory.
#
######################################################################################################
#
databaseName: shadow_db
#
dataSources:
ds:
url: jdbc:mysql://192.168.1.253:3306/test?useUnicode=true&characterEncoding=UTF-8
username: test
password: test
# connectionTimeoutMilliseconds: 30000
# idleTimeoutMilliseconds: 60000
# maxLifetimeMilliseconds: 1800000
# maxPoolSize: 50
# minPoolSize: 1
#...
```
* **server.yml**
```yml
#...
#mode:
# type: Cluster
# repository:
# type: ZooKeeper
# props:
# namespace: governance_ds
# server-lists: localhost:2181
# retryIntervalMilliseconds: 500
# timeToLiveSeconds: 60
# maxRetries: 3
# operationTimeoutMilliseconds: 500
# overwrite: false
#
rules:
- !AUTHORITY
users:
- root@%:root
- sharding@:sharding
provider:
type: ALL_PERMITTED
- !TRANSACTION
defaultType: XA
providerType: Atomikos
- !SQL_PARSER
sqlCommentParseEnabled: true
sqlStatementCache:
initialCapacity: 2000
maximumSize: 65535
parseTreeCache:
initialCapacity: 128
maximumSize: 1024

props:
# max-connections-size-per-query: 1
# kernel-executor-size: 16 # Infinite by default.
# proxy-frontend-flush-threshold: 128 # The default value is 128.
# proxy-hint-enabled: false
sql-show: true
# check-table-metadata-enabled: false
#...
```
* **${shardingsphere-proxy}/bin/start.sh**
### Example codes for reproduce this issue (such as a github link).
```java
public class BlobClobTest {

private String url;
private Properties properties;

public static void main(String[] args) throws Exception {
BlobClobTest test = new BlobClobTest();
test.setUp();
// test.createTable();
test.writeStream();
}

public void setUp() throws ClassNotFoundException {
Class.forName("com.mysql.cj.jdbc.Driver");
url = "jdbc:mysql://localhost:3307/shadow_db?useUnicode=true&characterEncoding=UTF-8";
properties = new Properties();
properties.put("user", "root");
properties.put("password", "root");
}

public void createTable() {
String sql = "CREATE TABLE `blob_clob` (\n" +
" `id` int(11) DEFAULT NULL,\n" +
" `b_blob` mediumblob,\n" +
" `c_clob` mediumtext\n" +
") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;"; //mysql
try (Connection conn = DriverManager.getConnection(url, properties);
PreparedStatement stmt = conn.prepareStatement(sql)) {
System.out.println(stmt.executeUpdate());
} catch (SQLException e) {
e.printStackTrace();
}
}

public void writeStream() {
String sql = "insert into blob_clob(id,b_blob,c_clob) values(?,?,?)";
try (Connection conn = DriverManager.getConnection(url, properties);
PreparedStatement stmt = conn.prepareStatement(sql)) {
stmt.setInt(1, 1);
stmt.setBlob(2,
Files.newInputStream(Paths.get(System.getProperty("user.home"), "test", "test.png")));
stmt.setClob(3,
Files.newBufferedReader(Paths.get(System.getProperty("user.home"), "test", "test.txt")));
System.out.println(stmt.executeUpdate());
} catch (SQLException | IOException e) {
e.printStackTrace();
}
}

}
```

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.