apache / apache/incubator-seata

bug: PreparedStatementProxy work unexpected

Open
#3,262 5 comments 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
26k
Forks
8.8k
Avg merge
1d 8h
Merged PRs (30d)
4

Description

- [ ] I have searched the [issues](https://github.com/seata/seata/issues) of this repository and believe that this is not a duplicate.

### Ⅰ. Issue Description

下面的代码会报错:

```java
@GetMapping("/test")
@GlobalTransactional
public void test() throws SQLException {
ConnectionProxy connection = dataSource.getConnection();
System.out.println(connection);
PreparedStatement targetPreparedStatement = connection.prepareStatement("update user set address = 'fdafdsa1fasd' where address=? limit ?");
for (int i = 0; i < 10; i ++) {
targetPreparedStatement.setString(1, "fdafdsafasd");
targetPreparedStatement.setInt(2, 1);
targetPreparedStatement.executeUpdate();
}
}
```

报错信息如下:

```log
2020-11-06 05:59:59.001 INFO 319808 --- [nio-8432-exec-7] i.seata.tm.api.DefaultGlobalTransaction : [192.168.80.134:8091:68098703474843648] rollback status: Rollbacked
2020-11-06 05:59:59.003 ERROR 319808 --- [nio-8432-exec-7] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is java.sql.SQLException: Parameter index out of range (5 > number of parameters, which is 3).] with root cause

java.sql.SQLException: Parameter index out of range (5 > number of parameters, which is 3).
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.20.jar:8.0.20]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.20.jar:8.0.20]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89) ~[mysql-connector-java-8.0.20.jar:8.0.20]
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63) ~[mysql-connector-java-8.0.20.jar:8.0.20]
at com.mysql.cj.jdbc.ClientPreparedStatement.checkBounds(ClientPreparedStatement.java:1372) ~[mysql-connector-java-8.0.20.jar:8.0.20]
at com.mysql.cj.jdbc.ClientPreparedStatement.getCoreParameterIndex(ClientPreparedStatement.java:1385) ~[mysql-connector-java-8.0.20.jar:8.0.20]
at com.mysql.cj.jdbc.ClientPreparedStatement.setObject(ClientPreparedStatement.java:1669) ~[mysql-connector-java-8.0.20.jar:8.0.20]
at com.alibaba.druid.pool.DruidPooledPreparedStatement.setObject(DruidPooledPreparedStatement.java:480) ~[druid-1.1.23.jar:1.1.23]
at io.seata.rm.datasource.exec.BaseTransactionalExecutor.buildTableRecords(BaseTransactionalExecutor.java:352) ~[seata-all-1.4.0-SNAPSHOT.jar:1.4.0-SNAPSHOT]
at io.seata.rm.datasource.exec.UpdateExecutor.beforeImage(UpdateExecutor.java:72) ~[seata-all-1.4.0-SNAPSHOT.jar:1.4.0-SNAPSHOT]
at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.executeAutoCommitFalse(AbstractDMLBaseExecutor.java:101) ~[seata-all-1.4.0-SNAPSHOT.jar:1.4.0-SNAPSHOT]
at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.lambda$executeAutoCommitTrue$2(AbstractDMLBaseExecutor.java:142) ~[seata-all-1.4.0-SNAPSHOT.jar:1.4.0-SNAPSHOT]
at io.seata.rm.datasource.ConnectionProxy$LockRetryPolicy.doRetryOnLockConflict(ConnectionProxy.java:302) ~[seata-all-1.4.0-SNAPSHOT.jar:1.4.0-SNAPSHOT]
at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor$LockRetryPolicy.execute(AbstractDMLBaseExecutor.java:186) ~[seata-all-1.4.0-SNAPSHOT.jar:1.4.0-SNAPSHOT]
at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.executeAutoCommitTrue(AbstractDMLBaseExecutor.java:141) ~[seata-all-1.4.0-SNAPSHOT.jar:1.4.0-SNAPSHOT]
at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.doExecute(AbstractDMLBaseExecutor.java:84) ~[seata-all-1.4.0-SNAPSHOT.jar:1.4.0-SNAPSHOT]
at io.seata.rm.datasource.exec.BaseTransactionalExecutor.execute(BaseTransactionalExecutor.java:113) ~[seata-all-1.4.0-SNAPSHOT.jar:1.4.0-SNAPSHOT]
at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:111) ~[seata-all-1.4.0-SNAPSHOT.jar:1.4.0-SNAPSHOT]
at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:50) ~[seata-all-1.4.0-SNAPSHOT.jar:1.4.0-SNAPSHOT]
at io.seata.rm.datasource.PreparedStatementProxy.executeUpdate(PreparedStatementProxy.java:65) ~[seata-all-1.4.0-SNAPSHOT.jar:1.4.0-SNAPSHOT]
at cn.caohd.txproviderserver.controller.UserController.test(UserController.java:183) ~[classes/:na]
...
```

追踪了一下 paramAppenderList 和 parametersHolder 相关的代码,问题主要出现在parametersHolder上,parametersHolder的parameters是`Map>`类型的,然后setParamByIndex的时候的代码是这样的

```java
/**
* Sets param by index.
*
* @param index the index
* @param x the x
*/
protected void setParamByIndex(int index, Object x) {
CollectionUtils.computeIfAbsent(parameters, index, e -> new ArrayList<>())
.add(x);
}
```

也就是说,我每一次setParameter的时候,都相当于在原列表里面追加了一个元素。

然后在Executor需要去查询beforeImage的时候的代码如下:

```java
/**
* build a BeforeImage
*
* @param tableMeta the tableMeta
* @param selectSQL the selectSQL
* @param paramAppenderList the paramAppender list
* @return a tableRecords
* @throws SQLException the sql exception
*/
protected TableRecords buildTableRecords(TableMeta tableMeta, String selectSQL, ArrayList> paramAppenderList) throws SQLException {
ResultSet rs = null;
try (PreparedStatement ps = statementProxy.getConnection().prepareStatement(selectSQL)) {
if (CollectionUtils.isNotEmpty(paramAppenderList)) {
for (int i = 0, ts = paramAppenderList.size(); i < ts; i++) {
List paramAppender = paramAppenderList.get(i);
for (int j = 0, ds = paramAppender.size(); j < ds; j++) {
ps.setObject(i * ds + j + 1, paramAppender.get(j));
}
}
}
rs = ps.executeQuery();
return TableRecords.buildRecords(tableMeta, rs);
} finally {
IOUtil.close(rs);
}
}
```

那么当我第二次进入循环的时候,由于我的paramAppenderList里面包含了两次setParameter的结果,然后这里的setObject又是用的`i * ds + j + 1`,就会导致索引越界报错。

... 调试了一下发现还有其他问题。。。不过问题的根源应该都是在setParamByIndex上

### Ⅱ. Describe what happened

If there is an exception, please attach the exception trace:

```
Just paste your stack trace here!
```

### Ⅲ. Describe what you expected to happen

### Ⅳ. How to reproduce it (as minimally and precisely as possible)

1. xxx
2. xxx
3. xxx

### Ⅴ. Anything else we need to know?

### Ⅵ. Environment:

- JDK version :
- OS :
- Others:

Contributor guide

Open the contributing guide

Research direction

Start with PreparedStatementProxy and trace parametersHolder/setParamByIndex into BaseTransactionalExecutor.buildTableRecords and UpdateExecutor.beforeImage. Reproduce the repeated setParameter loop from the issue and inspect how parameter indexes are built for the before-image query. Done means the example no longer raises a parameter-index SQLException and repeated executions use the correct parameters.

Written by the indexing model from the issue text.

Assessment

Tech stack
java
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.