problem
When I use mybatis foreach update, I occasionally get a bad sql error. After the first error, all subsequent executions of this sql will fail. When I restart the application, everything returns to normal. I am pretty sure I have added the configuration of allowMultiQueries=true and allowMultiQueries=true
version
mybatis-3.5.6
log
==> Preparing: update online_strategy_detail set confirm_distribution_qty = case when confirm_distribution_qty is null then ? else confirm_distribution_qty + ? end , ordered_qty = case when ordered_qty is null then ? else ordered_qty + ? end where id = ? ;
==> Parameters: 10(Integer), 10(Integer), 10(Integer), 10(Integer), 5509013510124226434(Long)
==> Parameters: 20(Integer), 20(Integer), 20(Integer), 20(Integer), 5509013510124226440(Long)
==> Parameters: 5(Integer), 5(Integer), 5(Integer), 5(Integer), 5509013510124226446(Long)
==> Parameters: 20(Integer), 20(Integer), 20(Integer), 20(Integer), 5509013510124226422(Long)
==> Parameters: 20(Integer), 20(Integer), 20(Integer), 20(Integer), 5509013510124226428(Long)
Caused by: org.apache.ibatis.exceptions.PersistenceException:
### Error committing transaction. Cause: org.apache.ibatis.executor.BatchExecutorException: com.vivo.it.vwkc.strategy.manage.dao.online.OnlineStrategyDetailDao.doDeduct (batch index #1) failed.
Cause: java.sql.BatchUpdateException: 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 ';update online_strategy_detail set confi' at line 1
### Cause: org.apache.ibatis.executor.BatchExecutorException:
com.dao.online.OnlineStrategyDetailDao.doDeduct (batch index #1) failed. Cause: java.sql.BatchUpdateException: 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 ';update online_strategy_detail set confi' at line 1
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:226)
at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:217)
... 119 common frames
omitted Caused by: java.sql.BatchUpdateException: 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 ';update online_strategy_detail set confi' at line 1
at sun.reflect.GeneratedConstructorAccessor508.newInstance(Unknown Source)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at com.mysql.cj.util.Util.handleNewInstance(Util.java:192)
at com.mysql.cj.util.Util.getInstance(Util.java:167)
at com.mysql.cj.util.Util.getInstance(Util.java:174)
at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)
at com.mysql.cj.jdbc.ClientPreparedStatement.executePreparedBatchAsMultiStatement(ClientPreparedStatement.java:584)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeBatchInternal(ClientPreparedStatement.java:431)
at com.mysql.cj.jdbc.StatementImpl.executeBatch(StatementImpl.java:796)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.executeBatch(DruidPooledPreparedStatement.java:565)
at sun.reflect.GeneratedMethodAccessor7935.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:78)
at com.sun.proxy.$Proxy523.executeBatch(Unknown Source)
at org.apache.ibatis.executor.BatchExecutor.doFlushStatements(BatchExecutor.java:123) ... 129 common frames omitted Caused by: java.sql.SQLSyntaxErrorException: 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 ';update online_strategy_detail set confi' at line 1
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
at com.mysql.cj.jdbc.ClientPreparedStatement.execute(ClientPreparedStatement.java:370)
at com.mysql.cj.jdbc.ClientPreparedStatement.executePreparedBatchAsMultiStatement(ClientPreparedStatement.java:527) ... 138 common frames omitted
sql
<update id="doDeduct">
<foreach collection="list" item="item" separator=";" close=";">
update
online_strategy_detail
set
confirm_distribution_qty =
case
when confirm_distribution_qty is null then #{item.confirmDistributionQty}
else confirm_distribution_qty + #{item.confirmDistributionQty}
end ,
ordered_qty =
case
when ordered_qty is null then #{item.orderedQty}
else ordered_qty + #{item.orderedQty}
end
where id = #{item.id}
</foreach>
</update>
Comment From: harawata
Hello @piemon-nyah ,
The doc says, allowMultiQueries has no effect on batch operation.
So, if you want to use multiple queries, you cannot use BATCH executor (you may be aware, but "multiple queries" and "batch update" are two different things and cannot be used at the same time).
If you want to perform batch update, you need to write it properly (see this FAQ entry ).
Closing as it's not a bug.
Comment From: piemon-nyah
Hello @piemon-nyah ,
The doc says,
allowMultiQuerieshas no effect on batch operation. So, if you want to use multiple queries, you cannot use BATCH executor (you may be aware, but "multiple queries" and "batch update" are two different things and cannot be used at the same time). If you want to perform batch update, you need to write it properly (see this FAQ entry ).Closing as it's not a bug.
hello @harawata
My sql is written normally. My problem is that after the application runs for a while, there will be occasional sql errors, and all subsequent sql will report errors, but I don't make any changes, just restart the application and it will recover. It's not a problem with the sql syntax, and there is no problem with my parameters, because I only need to restart to recover.
Comment From: harawata
The error says "You have an error in your SQL syntax", so it is a problem with the syntax. Try enabling DEBUG level logging. There must be something wrong with the SQL.
It could be the length of the SQL. How many items in the "list"?
Comment From: piemon-nyah
The error says "You have an error in your SQL syntax", so it is a problem with the syntax. Try enabling DEBUG level logging. There must be something wrong with the SQL.
It could be the length of the SQL. How many items in the "list"?
@harawata only 5 ,this is my log
==> Preparing: update online_strategy_detail set confirm_distribution_qty = case when confirm_distribution_qty is null then ? else confirm_distribution_qty + ? end , ordered_qty = case when ordered_qty is null then ? else ordered_qty + ? end where id = ? ; ==> Parameters: 10(Integer), 10(Integer), 10(Integer), 10(Integer), 5509013510124226434(Long) ==> Parameters: 20(Integer), 20(Integer), 20(Integer), 20(Integer), 5509013510124226440(Long) ==> Parameters: 5(Integer), 5(Integer), 5(Integer), 5(Integer), 5509013510124226446(Long) ==> Parameters: 20(Integer), 20(Integer), 20(Integer), 20(Integer), 5509013510124226422(Long) ==> Parameters: 20(Integer), 20(Integer), 20(Integer), 20(Integer), 5509013510124226428(Long) Caused by: org.apache.ibatis.exceptions.PersistenceException:
Error committing transaction. Cause: org.apache.ibatis.executor.BatchExecutorException: com.vivo.it.vwkc.strategy.manage.dao.online.OnlineStrategyDetailDao.doDeduct (batch index #1) failed.
Cause: java.sql.BatchUpdateException: 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 ';update online_strategy_detail set confi' at line 1
Cause: org.apache.ibatis.executor.BatchExecutorException:
com.dao.online.OnlineStrategyDetailDao.doDeduct (batch index #1) failed. Cause: java.sql.BatchUpdateException: 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 ';update online_strategy_detail set confi' at line 1 at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30) at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:226) at org.apache.ibatis.session.defaults.DefaultSqlSession.commit(DefaultSqlSession.java:217)
I understand that it is a syntax problem. I see that there seems to be an extra semicolon in the log, but there is no empty space in the printed parameters. I tried to manually insert empty elements but the problem did not recur. It is an occasional situation, not a must.
Comment From: harawata
Please post the Java code that is preparing the parameters and executing the statement.
There is only one statement in the log, so <foreach> is not used, it seems.
Comment From: piemon-nyah
@harawata sorry The previous information missed foraech this is mapper
<update id="doDeduct">
<foreach collection="list" item="item" separator=";" close=";">
update
online_strategy_detail
set
confirm_distribution_qty =
case
when confirm_distribution_qty is null then #{item.confirmDistributionQty}
else confirm_distribution_qty + #{item.confirmDistributionQty}
end ,
ordered_qty =
case
when ordered_qty is null then #{item.orderedQty}
else ordered_qty + #{item.orderedQty}
end
where id = #{item.id}
</foreach>
</update>
this is Java Code
onlineStrategyDetailDao.doDeduct(deductList);
int doDeduct(@Param("list") List<OnlineStrategyDetailDo> deductList);
this is params
==> Parameters: 10(Integer), 10(Integer), 10(Integer), 10(Integer), 5509013510124226434(Long)
==> Parameters: 20(Integer), 20(Integer), 20(Integer), 20(Integer), 5509013510124226440(Long)
==> Parameters: 5(Integer), 5(Integer), 5(Integer), 5(Integer), 5509013510124226446(Long)
==> Parameters: 20(Integer), 20(Integer), 20(Integer), 20(Integer), 5509013510124226422(Long)
==> Parameters: 20(Integer), 20(Integer), 20(Integer), 20(Integer), 5509013510124226428(Long)
Comment From: harawata
It's weird. The log output does not match the code/mapper.
If the <foreach> is used, the log output should look something like this:
==> Preparing: update online_strategy_detail set confirm_distribution_qty = case when confirm_distribution_qty is null then ? else confirm_distribution_qty + ? end , ordered_qty = case when ordered_qty is null then ? else ordered_qty + ? end where id = ? ; update online_strategy_detail set confirm_distribution_qty = case when confirm_distribution_qty is null then ? else confirm_distribution_qty + ? end , ordered_qty = case when ordered_qty is null then ? else ordered_qty + ? end where id = ? ; (the same UPDATE statement repeats 3 more times...) ==> Parameters: 10(Integer), 10(Integer), 10(Integer), 10(Integer), 5509013510124226434(Long), 20(Integer), 20(Integer), 20(Integer), 20(Integer), 5509013510124226440(Long), 5(Integer), 5(Integer), 5(Integer), 5(Integer), 5509013510124226446(Long), 20(Integer), 20(Integer), 20(Integer), 20(Integer), 5509013510124226422(Long), 20(Integer), 20(Integer), 20(Integer), 20(Integer), 5509013510124226428(Long)
Aren't you using some kind of third party extension? Like mybatis-plus? If you are unsure, please post the entire stack trace.