Skip to content

EncrytPreparedStament lost paremeters when using inser on duplicate key update, "No value specified for paramer 8" #8375

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
Jaskey opened this issue Nov 27, 2020 · 7 comments
Closed

EncrytPreparedStament lost paremeters when using inser on duplicate key update, "No value specified for paramer 8" #8375

Jaskey opened this issue Nov 27, 2020 · 7 comments
Labels
type: duplicate

Comments

@Jaskey
Copy link

Jaskey commented Nov 27, 2020 •
edited
Loading

Bug Report

version: <apache.shardingsphere.version>4.0.1</apache.shardingsphere.version>

when we try to use insert on duplicate key update statement with sharding sphere's encrption feature, we found an sql grammar exception though we are trying to insert to a single table in a single db(we do not use sharding items).

As the debug shows, the paremeter after rewrite has been lost,
image

The original parementer are 14， but after rewrite, only half have been remains.

The problem code is :

    private SQLUnit getSQLUnit(final String sql) {
        EncryptConnection connection = preparedStatementGenerator.connection;
        SQLStatement sqlStatement = connection.getRuntimeContext().getParseEngine().parse(sql, true);
        RelationMetas relationMetas = getRelationMetas(connection.getRuntimeContext().getTableMetas());
        sqlStatementContext = SQLStatementContextFactory.newInstance(relationMetas, sql, getParameters(), sqlStatement);
        SQLRewriteContext sqlRewriteContext = new SQLRewriteContext(relationMetas, sqlStatementContext, sql, getParameters());
        boolean isQueryWithCipherColumn = connection.getRuntimeContext().getProps().<Boolean>getValue(ShardingPropertiesConstant.QUERY_WITH_CIPHER_COLUMN);
        new EncryptSQLRewriteContextDecorator(connection.getRuntimeContext().getRule(), isQueryWithCipherColumn).decorate(sqlRewriteContext);
        sqlRewriteContext.generateSQLTokens();
        SQLRewriteResult sqlRewriteResult = new DefaultSQLRewriteEngine().rewrite(sqlRewriteContext);
        showSQL(sqlRewriteResult.getSql());
        return new SQLUnit(sqlRewriteResult.getSql(), sqlRewriteResult.getParameters());
    }

Thus, an exception throws:

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: java.sql.SQLException: No value specified for parameter 8
### The error may exist in file [D:\fin-project\finz-pay\dubhe-pay-dao\target\classes\sql\HeytapPayOrderInfoExtMapper.xml]
### The error may involve com.oppo.finance.duhbepay.dao.mapper.HeytapPayOrderInfoExtMapper.insertOnDuplicateKeyUpdateSelective-Inline
### The error occurred while setting parameters
### SQL: insert into heytap_pay_order_info_ext      ( trade_no,                       trade_province,                       trade_city,                       trade_district,                       recipient_province,                       recipient_city,                       recipient_district )       values ( ?,                       ?,                       ?,                       ?,                       ?,                       ?,                       ? )      ON DUPLICATE KEY UPDATE      trade_no = ?,                       trade_province = ?,                       trade_city = ?,                       trade_district = ?,                       recipient_province = ?,                       recipient_city = ?,                       recipient_district = ?
### Cause: java.sql.SQLException: No value specified for parameter 8
; bad SQL grammar []; nested exception is java.sql.SQLException: No value specified for parameter 8
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81) ~[spring-jdbc-5.0.7.RELEASE.jar:5.0.7.RELEASE]
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:74) ~[mybatis-spring-2.0.2.jar:2.0.2]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440) ~[mybatis-spring-2.0.2.jar:2.0.2]
	at com.sun.proxy.$Proxy118.insert(Unknown Source) ~[?:?]
	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.$Proxy157.insertOnDuplicateKeyUpdateSelective(Unknown Source) ~[?:?]
	at com.oppo.finance.finzpay.core.rocketmq.handler.HeyTapPayOrderExtBuildHandler.rebuildExtRecordFromSource(HeyTapPayOrderExtBuildHandler.java:162) ~[classes/:?]
	at com.oppo.finance.finzpay.core.rocketmq.handler.HeyTapPayOrderExtBuildHandler.bizOperation(HeyTapPayOrderExtBuildHandler.java:79) ~[classes/:?]
	at com.oppo.finance.finzpay.core.rocketmq.handler.HeyTapPayOrderExtBuildHandler.bizOperation(HeyTapPayOrderExtBuildHandler.java:47) ~[classes/:?]
	at com.oppo.finance.qb.commons.mq.QBRMQHandlerTemplate.consumeMessage(QBRMQHandlerTemplate.java:26) ~[qb-commons-utils-1.0.7-SNAPSHOT.jar:?]
	at com.oppo.finance.finzpay.core.rocketmq.listener.MessageListenerOrderly.doHandleMsg(MessageListenerOrderly.java:37) ~[classes/:?]
	at com.oppo.finance.qb.commons.mq.QBOrderlyRMQListener.lambda$consumeMessage$20(QBOrderlyRMQListener.java:45) ~[qb-commons-utils-1.0.7-SNAPSHOT.jar:?]
	at com.oppo.finance.qb.commons.util.Tracer.doWithTraceEntry(Tracer.java:101) ~[qb-commons-utils-1.0.7-SNAPSHOT.jar:?]
	at com.oppo.finance.qb.commons.mq.QBOrderlyRMQListener.consumeMessage(QBOrderlyRMQListener.java:42) ~[qb-commons-utils-1.0.7-SNAPSHOT.jar:?]
	at org.apache.rocketmq.client.impl.consumer.ConsumeMessageOrderlyService$ConsumeRequest.run(ConsumeMessageOrderlyService.java:469) ~[rocketmq-client-4.1.0-incubating.jar:4.1.0-incubating]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) ~[?:1.8.0_73]
	at java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:266) ~[?:1.8.0_73]
	at java.util.concurrent.FutureTask.run(FutureTask.java) ~[?:1.8.0_73]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) ~[?:1.8.0_73]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) ~[?:1.8.0_73]
	at java.lang.Thread.run(Thread.java:745) [?:1.8.0_73]
Caused by: java.sql.SQLException: No value specified for parameter 8
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.PreparedStatement.checkAllParametersSet(PreparedStatement.java:2211) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2191) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2121) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1162) ~[mysql-connector-java-5.1.46.jar:5.1.46]
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:498) ~[druid-1.1.10.jar:1.1.10]
	at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.EncryptPreparedStatement.execute(EncryptPreparedStatement.java:142) ~[sharding-jdbc-core-4.0.1.jar:4.0.1]
	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 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 sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:1.8.0_73]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_73]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_73]
	at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_73]
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.5.2.jar:3.5.2]
	at com.sun.proxy.$Proxy180.update(Unknown Source) ~[?:?]
	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) ~[?:1.8.0_73]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[?:1.8.0_73]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:1.8.0_73]
	at java.lang.reflect.Method.invoke(Method.java:497) ~[?:1.8.0_73]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.2.jar:2.0.2]
	... 20 more

FYI, this is the SQL we are using:

insert into heytap_pay_order_info_ext
     ( trade_no,
      
      
        trade_province,
      
      
        trade_city,
      
      
        trade_district,
      
      
        recipient_province,
      
      
        recipient_city,
      
      
        recipient_district ) 
     values ( ?,
      
      
        ?,
      
      
        ?,
      
      
        ?,
      
      
        ?,
      
      
        ?,
      
      
        ? ) 
    ON DUPLICATE KEY UPDATE
     trade_no = ?,
      
      
        trade_province = ?,
      
      
        trade_city = ?,
      
      
        trade_district = ?,
      
      
        recipient_province = ?,
      
      
        recipient_city = ?,
      
      
        recipient_district = ?
@kimmking
Copy link
Member

can you try 5.0.0-alpha

@Jaskey
Copy link
Author

Jaskey commented Nov 27, 2020

@kimmking

Would you pleae give me a confirm conclusion, we have run this application in a prod env, we can't easitly update an much higer version in our prod.

And as the issue shows #1756, a related issue have been solved in 4.0

@kimmking
Copy link
Member

@Jaskey but truely not.
Ref: #6367

@Jaskey
Copy link
Author

Jaskey commented Nov 27, 2020

@kimmking So what bug does#1756 fix? which is the lowest version can fix this bug.

@terrymanu
Copy link
Member

Duplicate with #1756, it is fixed on 4.0.0-RC2, please upgrade.
We have no plan to fix pervious version, please forgive me to request you upgrade the version.

@Jaskey
Copy link
Author

Jaskey commented Nov 30, 2020 •
edited
Loading

@terrymanu I use 4.0.1, isn't it higher than version 4.0.0-RC2?

@helloliubai
Copy link

version 4.0.0-RC2 并没有修复这个问题，4.0.1和4.0.0-RC2 中都仍然有这个问题

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
type: duplicate
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@kimmking @Jaskey @terrymanu @helloliubai