apache / apache/incubator-seata

批量插入语句插入异常

Open
#3,157 2 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
批量插入语句中含非占位符,如函数IFNULL,插入报错。

### Ⅱ. Describe what happened
批量插入语句:
insert into flow_table (id, account_no, user_no, user_name)
values (?, IFNULL(?, 0), IFNULL(?, 0), IFNULL(?, '')),
(?, IFNULL(?, 0), IFNULL(?, 0), IFNULL(?, '')),
(?, IFNULL(?, 0), IFNULL(?, 0), IFNULL(?, '')),
(?, IFNULL(?, 0), IFNULL(?, 0), IFNULL(?, ''));

报错:
Cause: java.sql.SQLException: Cannot convert class java.lang.String to SQL type requested due to java.lang.NumberFormatException - For input string: "C22233"
@jsbxyyx

```
java.lang.NumberFormatException: For input string: "C22233"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65) ~[na:1.8.0_31]
at java.lang.Long.parseLong(Long.java:589) ~[na:1.8.0_31]
at java.lang.Long.valueOf(Long.java:803) ~[na:1.8.0_31]
at com.mysql.cj.jdbc.PreparedStatement.setNumericObject(PreparedStatement.java:3372) ~[mysql-connector-java-6.0.6.jar:6.0.6]
at com.mysql.cj.jdbc.PreparedStatement.setObject(PreparedStatement.java:3629) ~[mysql-connector-java-6.0.6.jar:6.0.6]
at com.mysql.cj.jdbc.PreparedStatement.setObject(PreparedStatement.java:3778) ~[mysql-connector-java-6.0.6.jar:6.0.6]
at com.mysql.cj.jdbc.PreparedStatement.setObject(PreparedStatement.java:3540) ~[mysql-connector-java-6.0.6.jar:6.0.6]
at com.alibaba.druid.pool.DruidPooledPreparedStatement.setObject(DruidPooledPreparedStatement.java:469) ~[druid-1.1.18.jar:1.1.18]
at io.seata.rm.datasource.exec.BaseTransactionalExecutor.buildTableRecords(BaseTransactionalExecutor.java:397) ~[seata-all-1.3.0.jar:1.3.0]
at io.seata.rm.datasource.exec.BaseInsertExecutor.afterImage(BaseInsertExecutor.java:77) ~[seata-all-1.3.0.jar:1.3.0]
at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.executeAutoCommitFalse(AbstractDMLBaseExecutor.java:94) ~[seata-all-1.3.0.jar:1.3.0]
at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.doExecute(AbstractDMLBaseExecutor.java:76) ~[seata-all-1.3.0.jar:1.3.0]
at io.seata.rm.datasource.exec.BaseTransactionalExecutor.execute(BaseTransactionalExecutor.java:115) ~[seata-all-1.3.0.jar:1.3.0]
at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:113) ~[seata-all-1.3.0.jar:1.3.0]
at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:51) ~[seata-all-1.3.0.jar:1.3.0]
at io.seata.rm.datasource.PreparedStatementProxy.execute(PreparedStatementProxy.java:55) ~[seata-all-1.3.0.jar:1.3.0]
at sun.reflect.GeneratedMethodAccessor158.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_31]
at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0_31]
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59) ~[mybatis-3.5.2.jar:3.5.2]
at com.sun.proxy.$Proxy214.execute(Unknown Source) ~[na:na]
at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[mybatis-3.5.2.jar:3.5.2]
at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.2.jar:3.5.2]
at sun.reflect.GeneratedMethodAccessor244.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_31]
at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0_31]
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.5.2.jar:3.5.2]
at com.sun.proxy.$Proxy212.update(Unknown Source) ~[na:na]
at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.2.jar:3.5.2]
at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.2.jar:3.5.2]
at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.2.jar:3.5.2]
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.2.jar:3.5.2]
at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184) ~[mybatis-3.5.2.jar:3.5.2]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_31]
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_31]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_31]
at java.lang.reflect.Method.invoke(Method.java:483) ~[na:1.8.0_31]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.2.jar:2.0.2]
at com.sun.proxy.$Proxy137.insert(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:271) ~[mybatis-spring-2.0.2.jar:2.0.2]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:62) ~[mybatis-3.5.2.jar:3.5.2]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:57) ~[mybatis-3.5.2.jar:3.5.2]
at com.sun.proxy.$Proxy161.batchInsert(Unknown Source) ~[na:na]
```

### Ⅲ. Describe what you expected to happen
插入成功

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

1. 主键非数据库自动生成
2. 批量插入中含非占位符列值

Contributor guide

Open the contributing guide

Research direction

Start at BaseTransactionalExecutor.buildTableRecords, then follow BaseInsertExecutor.afterImage using the reported batch INSERT with IFNULL expressions and non-generated primary keys. Reproduce the NumberFormatException with the provided SQL and verify that the batch insert completes successfully without incorrect parameter binding.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, mysql
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.