wingkingbobo opened a new issue, #17870:
URL: https://github.com/apache/shardingsphere/issues/17870
## 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
The following INSERT statement can run normally when calling MySQL directly,
but an exception is thrown after using Sharding-Proxy.
```
@Insert(value = {"<script>insert into ${tableName} (shapeid, userid,
groupid, create_userid, name, type, name_alias, " +
"stringvalue, doublevalue, intvalue, blobvalue, version,
datasize, createtime, modifytime, deleted) values" +
"<foreach item='item' index='index' collection='propertyExList'
separator=','>" +
"(#{item.builder.serverPID}, #{item.userID}, #{item.groupID},
#{item.userID}, " +
"#{item.builder.fieldName}, #{item.builder.type},
#{item.builder.fieldName2}, " +
"#{item.builder.stringValue}, #{item.builder.doubleValue},
#{item.builder.intValue}, " +
"#{item.blobValue}, #{item.builder.version}, #{item.dataSize},
" +
"#{item.builder.createTime}, #{item.builder.modifyTime},
#{item.builder.deleted})" +
"</foreach></script>"})
@Options(useGeneratedKeys=true,
keyProperty="propertyExList.builder.serverID", keyColumn="id")
boolean insertBatchProperty(@Param("tableName") String tableName,
@Param("propertyExList") List<PropertyEx>
propertyExList);
```
### Actual behavior
```
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: This
version of ShardingSphere-Proxy doesn't yet support this SQL. 'You have an
error in your SQL syntax'
### The error may exist in xxx/cn/syncserver/dao/UploadShapeDao.java (best
guess)
### The error may involve
xxx.cn.syncserver.dao.UploadShapeDao.insertBatchProperty-Inline
### The error occurred while setting parameters
### SQL: insert into shape_property_simple (shapeid, userid, groupid,
create_userid, name, type, name_alias, stringvalue, doublevalue, intvalue,
blobvalue, version, datasize, createtime, modifytime, deleted) values (?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) , (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?)
### Cause: java.sql.SQLSyntaxErrorException: This version of
ShardingSphere-Proxy doesn't yet support this SQL. 'You have an error in your
SQL syntax'
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException:
This version of ShardingSphere-Proxy doesn't yet support this SQL. 'You have an
error in your SQL syntax'
```
### Reason analyze (If you can)
I found that the following insert statement can be executed. The only
difference between it and the previous statements with errors is that it does
not contain ` (?,?,...)`.
```
[INFO ] 2022-05-23 15:20:37.162 [ShardingSphere-Command-0]
ShardingSphere-SQL - Actual SQL: syncdb1 ::: insert into shape_simple (layerid,
userid, create_userid, groupid, type, data, minlevel, maxlevel, version,
datasize, legend, createtime, modifytime, deleted, create_version) values (0,
10232, 10232, -1, 1,
x'0a1b09c240214c1bc45c4011ee44a401170b4440190000000000000000', 0, 0, 15, 120.0,
'', '2022-05-23 15:18:54', '2022-05-23 15:18:54', 0, 15)
```
--
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]
