hollydragon commented on issue #5210: ON DUPLICATE KEY UPDATE 语句中参数丢失 URL: https://github.com/apache/incubator-shardingsphere/issues/5210#issuecomment-614517957 Hi, Thanks for replying. When I using sharding jdbc with MyBatis, by creating a insert or update statement: ``` <update id="insertOrUpdate" parameterType="org.imiplat.common.model.burying.DanceViewVisit"> insert into burying_dance_view_visit <trim prefix="(" suffix=")" suffixOverrides=","> <if test="viewFlag != null"> view_flag, </if> <if test="today != null"> today, </if> <if test="viewOrAction != null"> view_or_action, </if> <if test="viewName != null"> view_name, </if> <if test="weekday != null"> weekday, </if> <if test="visitCount != null"> visit_count, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="viewFlag != null"> #{viewFlag,jdbcType=VARCHAR}, </if> <if test="today != null"> #{today,jdbcType=DATE}, </if> <if test="viewOrAction != null"> #{viewOrAction,jdbcType=CHAR}, </if> <if test="viewName != null"> #{viewName,jdbcType=VARCHAR}, </if> <if test="weekday != null"> #{weekday,jdbcType=CHAR}, </if> <if test="visitCount != null"> #{visitCount,jdbcType=INTEGER}, </if> </trim> ON DUPLICATE KEY UPDATE <if test="visitCount != null"> visit_count = visit_count + #{visitCount,jdbcType=INTEGER}, </if> update_time = CURRENT_TIMESTAMP </update> ``` This will bring a error: ``` 2020-04-16 17:04:41,689 INFO (NettyServerHandler.java:76)- [DUBBO] The connection of /192.168.30.60:7952 -> /192.168.30.60:20910 is established., dubbo version: 2.7.6, current host: 192.168.30.60 2020-04-16 17:04:41,951 INFO (DynamicDataSourceAspect.java:98)- channel=haixin, channelEnum.getShardingValue()=1 line 38:39 no viable alternative at input 'CURRENT_TIMESTAMP' line 38:39 no viable alternative at input 'CURRENT_TIMESTAMP' 2020-04-16 17:04:43,167 INFO (SQLLogger.java:99)- Rule Type: sharding 2020-04-16 17:04:43,167 INFO (SQLLogger.java:99)- Logic SQL: insert into burying_dance_view_visit ( view_flag, today, view_or_action, view_name, weekday, visit_count ) values ( ?, ?, ?, ?, ?, ? ) ON DUPLICATE KEY UPDATE visit_count = visit_count + ?, update_time = CURRENT_TIMESTAMP 2020-04-16 17:04:43,167 INFO (SQLLogger.java:99)- SQLStatement: InsertSQLStatementContext(super=CommonSQLStatementContext(sqlStatement=org.apache.shardingsphere.sql.parser.sql.statement.dml.InsertStatement@4d0ac2cc, tablesContext=TablesContext(tables=[Table(name=burying_dance_view_visit, alias=Optional.absent())], schema=Optional.absent())), columnNames=[view_flag, today, view_or_action, view_name, weekday, visit_count], insertValueContexts=[InsertValueContext(parametersCount=6, valueExpressions=[ParameterMarkerExpressionSegment(startIndex=345, stopIndex=345, parameterMarkerIndex=0), ParameterMarkerExpressionSegment(startIndex=390, stopIndex=390, parameterMarkerIndex=1), ParameterMarkerExpressionSegment(startIndex=435, stopIndex=435, parameterMarkerIndex=2), ParameterMarkerExpressionSegment(startIndex=480, stopIndex=480, parameterMarkerIndex=3), ParameterMarkerExpressionSegment(startIndex=525, stopIndex=525, parameterMarkerIndex=4), ParameterMarkerExpressionSegment(startIndex=570, stopIndex=570, parameterMarkerIndex=5)], parameters=[view_hot_update, 2020-04-16, view, 热更界面, 周四, 1])]) 2020-04-16 17:04:43,168 INFO (SQLLogger.java:99)- Actual SQL: ds-0 ::: insert into burying_dance_view_visit ( view_flag, today, view_or_action, view_name, weekday, visit_count ) values (?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE visit_count = visit_count + ?, update_time = CURRENT_TIMESTAMP ::: [view_hot_update, 2020-04-16, view, 热更界面, 周四, 1] 2020-04-16 17:04:43,247 INFO (XmlBeanDefinitionReader.java:317)- Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml] 2020-04-16 17:04:43,280 INFO (SQLErrorCodesFactory.java:127)- SQLErrorCodes loaded: [DB2, Derby, H2, HDB, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase] 2020-04-16 17:04:43,299 ERROR (ExceptionFilter.java:79)- [DUBBO] Got unchecked and undeclared exception which called by 192.168.30.60. service: org.imiplat.dubbo.api.service.burying.DanceViewVisitService, method: insertOrUpdate, exception: org.springframework.jdbc.BadSqlGrammarException: ### Error updating database. Cause: java.sql.SQLException: No value specified for parameter 7 ### The error may involve org.imiplat.dal.dao.mapper.burying.DanceViewVisitMapper.insertOrUpdate-Inline ### The error occurred while setting parameters ### SQL: insert into burying_dance_view_visit ( view_flag, today, view_or_action, view_name, weekday, visit_count ) values ( ?, ?, ?, ?, ?, ? ) ON DUPLICATE KEY UPDATE visit_count = visit_count + ?, update_time = CURRENT_TIMESTAMP ### Cause: java.sql.SQLException: No value specified for parameter 7 ; bad SQL grammar []; nested exception is java.sql.SQLException: No value specified for parameter 7, dubbo version: 2.7.6, current host: 192.168.30.60 org.springframework.jdbc.BadSqlGrammarException: ### Error updating database. Cause: java.sql.SQLException: No value specified for parameter 7 ### The error may involve org.imiplat.dal.dao.mapper.burying.DanceViewVisitMapper.insertOrUpdate-Inline ### The error occurred while setting parameters ### SQL: insert into burying_dance_view_visit ( view_flag, today, view_or_action, view_name, weekday, visit_count ) values ( ?, ?, ?, ?, ?, ? ) ON DUPLICATE KEY UPDATE visit_count = visit_count + ?, update_time = CURRENT_TIMESTAMP ### Cause: java.sql.SQLException: No value specified for parameter 7 ; bad SQL grammar []; nested exception is java.sql.SQLException: No value specified for parameter 7 at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:99) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447) at com.sun.proxy.$Proxy85.update(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:295) at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:63) at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) at com.sun.proxy.$Proxy109.insertOrUpdate(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:47) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.aspectj.AspectJAfterThrowingAdvice.invoke(AspectJAfterThrowingAdvice.java:62) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) at com.sun.proxy.$Proxy110.insertOrUpdate(Unknown Source) at org.imiplat.dubbo.service.impl.burying.DanceViewVisitServiceImpl.insertOrUpdate(DanceViewVisitServiceImpl.java:128) at org.apache.dubbo.common.bytecode.Wrapper109.invokeMethod(Wrapper109.java) at org.apache.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:47) at org.apache.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:84) at org.apache.dubbo.config.invoker.DelegateProviderMetaDataInvoker.invoke(DelegateProviderMetaDataInvoker.java:56) at org.apache.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:56) at org.apache.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:52) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) at org.apache.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:89) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) at org.apache.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:44) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) at org.apache.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:77) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) at org.apache.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:118) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) at org.apache.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:152) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) at org.apache.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) at org.apache.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:41) at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) at org.apache.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:145) at org.apache.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:100) at org.apache.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:175) at org.apache.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:51) at org.apache.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:57) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) at java.lang.Thread.run(Thread.java:748) Caused by: java.sql.SQLException: No value specified for parameter 7 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2265) at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2245) at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2175) at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1216) at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497) at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:64) at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:60) at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:79) at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:61) at org.apache.shardingsphere.core.execute.engine.ShardingExecuteEngine.syncGroupExecute(ShardingExecuteEngine.java:125) at org.apache.shardingsphere.core.execute.engine.ShardingExecuteEngine.parallelExecute(ShardingExecuteEngine.java:102) at org.apache.shardingsphere.core.execute.engine.ShardingExecuteEngine.groupExecute(ShardingExecuteEngine.java:83) at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteTemplate.executeGroup(SQLExecuteTemplate.java:73) at org.apache.shardingsphere.core.execute.sql.execute.SQLExecuteTemplate.executeGroup(SQLExecuteTemplate.java:56) at org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:140) at org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:153) at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:192) at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:46) at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434) ... 52 common frames omitted ``` It figures that, the args in update sub-clause loosed/dropped, even if the same arg is used at insert sub-clause. This usage without sharding jdbc is ok, after using with sharding-jdbc, it is error. And, all of my method imiplementation with insert or update clause, will occur this error. Hope help, thanks!
---------------------------------------------------------------- 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. For queries about this service, please contact Infrastructure at: [email protected] With regards, Apache Git Services
