wingkingbobo opened a new issue, #17888:
URL: https://github.com/apache/shardingsphere/issues/17888
## Bug Report
### Which version of ShardingSphere did you use?
5.1.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
execute prepareStatement like `INSERT INTO remark (userid, content, `time`)
VALUES (?, ?, ?) `
### Actual behavior
```
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error
in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near
'org.apache.shardingsphere.sharding.rewrite.token.pojo.ShardingInsertValuesToken@'
at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at
sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2495)
at
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1903)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1242)
at
com.wingkingbobo.splitdb.service.RemarkServiceImpl.testInsert(RemarkServiceImpl.java:107)
```
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
* steps
* create database `p`, `p1` by create table statement.
* run demo
* `jdbcUrl=JDBC_URL_MYSQL`: OK
* `jdbcUrl=JDBC_URL_MYSQL`: throw `MySQLSyntaxErrorException`
* demo
```java
private static final String JDBC_URL_MYSQL =
"jdbc:mysql://localhost:3306/p1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
private static final String JDBC_URL_SHARDING_PROXY =
"jdbc:mysql://localhost:3307/sharding_db?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai";
public static void main(String[] args) {
String jdbcUrl = JDBC_URL_SHARDING_PROXY;
try {
testInsert(createRemarkList(1).get(0), jdbcUrl);
// testBatchInsert(createRemarkList(1).get(0), jdbcUrl);
} catch(Exception e) {
e.printStacktrace();
}
}
private List<RemarkPO> createRemarkList(int count) {
List<RemarkPO> remarks = new ArrayList<>();
for (int i = 0; i < count; i++) {
RemarkPO remark = new RemarkPO();
remark.setUserId(4);
remark.setContent("test"+i);
remark.setTime(new Date());
remarks.add(remark);
}
return remarks;
}
private boolean testInsert(RemarkPO remark, String jdbcUrl) throws
Exception{
DriverManager.registerDriver(new Driver());
Connection connection = DriverManager.getConnection(jdbcUrl, "root",
"123456");
String sql = "INSERT INTO remark(userid, content, `time`) VALUES (?,
?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setLong(1, remark.getUserId());
statement.setString(2, remark.getContent());
statement.setDate(3, toSqlDate(remark.getTime()));
statement.execute();
return true;
}
private boolean testBatchInsert(List<RemarkPO> remarks, String jdbcUrl)
throws Exception {
if (remarks.isEmpty()) {
return false;
}
DriverManager.registerDriver(new Driver());
Connection connection = DriverManager.getConnection(jdbcUrl, "root",
"123456");
StringBuilder sb = new StringBuilder("INSERT INTO remark(userid,
content, `time`) VALUES ");
for (int i = 0; i < remarks.size(); i++) {
if (i != 0) {
sb.append(",");
}
sb.append(" (?, ?, ?)");
}
String sql = sb.toString();
PreparedStatement statement = connection.prepareStatement(sql);
int stride = 3;
for (int i = 0; i < remarks.size(); i++) {
int offset = stride * i;
RemarkPO remark = remarks.get(i);
statement.setLong(1 + offset, remark.getUserId());
statement.setString(2 + offset, remark.getContent());
statement.setDate(3 + offset, toSqlDate(remark.getTime()));
}
statement.execute();
return true;
}
private java.sql.Date toSqlDate(Date date) {
return new java.sql.Date(date.getTime());
}
```
```java
package com.test.po;
import java.util.Date;
public class RemarkPO {
private long id;
private long userId;
private String content;
private Date time;
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public long getUserId() {
return userId;
}
public void setUserId(long userId) {
this.userId = userId;
}
public String getContent() {
return content;
}
public void setContent(String content) {
this.content = content;
}
public Date getTime() {
return time;
}
public void setTime(Date time) {
this.time = time;
}
}
```
* server.yaml
```yaml
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: governance_ds
server-lists: localhost:2182
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
overwrite: false
rules:
- !AUTHORITY
users:
- root@%:123456
provider:
type: ALL_PRIVILEGES_PERMITTED
- !TRANSACTION
defaultType: LOCAL
props:
recoveryStoreUrl: jdbc:mysql://127.0.0.1:3306/jbossts
recoveryStoreDataSource: com.mysql.jdbc.jdbc2.optional.MysqlDataSource
recoveryStoreUser: root
recoveryStorePassword: 12345678
- !SQL_PARSER
sqlCommentParseEnabled: true
sqlStatementCache:
initialCapacity: 2000
maximumSize: 65535
concurrencyLevel: 4
parseTreeCache:
initialCapacity: 128
maximumSize: 1024
concurrencyLevel: 4
props:
max-connections-size-per-query: 1
kernel-executor-size: 16
proxy-frontend-flush-threshold: 128
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
sql-federation-enabled: false
proxy-backend-driver-type: JDBC
```
* config-sharding.yaml
```yaml
schemaName: sharding_db
dataSources:
p:
url:
jdbc:mysql://localhost:3306/p?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
p1:
url:
jdbc:mysql://localhost:3306/p1?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: 123456
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
rules:
- !SHARDING
tables:
remark:
actualDataNodes: p.remark,p$->{1..1}.remark
databaseStrategy:
standard:
shardingColumn: userid
shardingAlgorithmName: database_inline
shardingAlgorithms:
database_inline:
type: INLINE
props:
algorithm-expression:
p$->{userid<3?"":(String)((int)((userid-3)/2)+1)}
```
* create table statement
```sql
/*
Navicat Premium Data Transfer
Source Server : localhost
Source Server Type : MySQL
Source Server Version : 50708
Source Host : localhost:3306
Source Schema : p
Target Server Type : MySQL
Target Server Version : 50708
File Encoding : 65001
Date: 12/05/2022 16:42:31
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for remark
-- ----------------------------
DROP TABLE IF EXISTS `remark`;
CREATE TABLE `remark` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userid` int(11) NOT NULL,
`content` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`time` datetime NOT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE =
utf8_general_ci ROW_FORMAT = COMPACT;
SET FOREIGN_KEY_CHECKS = 1;
```
--
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]
