You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/01/03 02:54:25 UTC
[GitHub] [incubator-shardingsphere] xiyelife opened a new issue #3855:
Insert with encrypt does'not handle with the `on duplicate key update` 's
patameters.
xiyelife opened a new issue #3855: Insert with encrypt does'not handle with the `on duplicate key update` 's patameters.
URL: https://github.com/apache/incubator-shardingsphere/issues/3855
### Which version of ShardingSphere did you use?
sharding-jdbc-4.0.0-RC4-SNAPSHOT
### Which project did you use? Sharding-JDBC or Sharding-Proxy?
sharding-jdbc
### Expected behavior
`insert into user_info (cust_id, unique_id, name, ident, mobile, password, reg_time, modify_time, reg_source, reg_app_channel, reg_device_info, status, is_mobile_authed, complate_cust_info, time_inst, time_upd, has_checked, system) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now() , ?, ?, ?) ON DUPLICATE KEY UPDATE id = 1, ident = ?, regSource = ? `
the sql above can work fine.
### Actual behavior
```
2020-01-03 10:48:12.687 INFO 28800 --- [batis-jap-test0] ShardingSphere-SQL : Rule Type: encrypt
2020-01-03 10:48:12.688 INFO 28800 --- [batis-jap-test0] ShardingSphere-SQL : SQL: insert into user_info (cust_id, unique_id, name_enc, name,
ident_enc, ident, mobile_enc, mobile, password,
reg_time, modify_time, reg_source,
reg_app_channel, reg_device_info, status,
is_mobile_authed, complate_cust_info, time_inst,
time_upd, has_checked, system)
values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now(), ?, ?, ?)
ON DUPLICATE KEY UPDATE id = 1, ident = ?, regSource = ?
2020-01-03 10:48:13.563 INFO 28800 --- [batis-jap-test0] o.s.b.f.xml.XmlBeanDefinitionReader : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2020-01-03 10:48:13.616 INFO 28800 --- [batis-jap-test0] o.s.jdbc.support.SQLErrorCodesFactory : SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
2020-01-03 10:48:13.624 ERROR 28800 --- [batis-jap-test0] t.ThreadMybatisAndJPATest$Half :
org.springframework.jdbc.BadSqlGrammarException:
### Error updating database. Cause: java.sql.SQLException: No value specified for parameter 21
### The error may involve transparent.mybatis.mapper.UserInfoMapper.insertOnDuplicateKey-Inline
### The error occurred while setting parameters
### SQL: insert into user_info (cust_id, unique_id, name, ident, mobile, password, reg_time, modify_time, reg_source, reg_app_channel, reg_device_info, status, is_mobile_authed, complate_cust_info, time_inst, time_upd, has_checked, system) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now() , ?, ?, ?) ON DUPLICATE KEY UPDATE id = 1, ident = ?, regSource = ?
### Cause: java.sql.SQLException: No value specified for parameter 21
; bad SQL grammar []; nested exception is java.sql.SQLException: No value specified for parameter 21
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:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy107.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy116.insertOnDuplicateKey(Unknown Source)
at transparent.ThreadMybatisAndJPATest.testMybatis(ThreadMybatisAndJPATest.java:96)
at transparent.ThreadMybatisAndJPATest$Half.call(ThreadMybatisAndJPATest.java:227)
at transparent.ThreadMybatisAndJPATest$Half.call(ThreadMybatisAndJPATest.java:216)
at java.util.concurrent.FutureTask.run$$$capture(FutureTask.java:266)
at java.util.concurrent.FutureTask.run(FutureTask.java)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: No value specified for parameter 21
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:2211)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2191)
at com.mysql.jdbc.PreparedStatement.fillSendPacket(PreparedStatement.java:2121)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1162)
at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:493)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.EncryptPreparedStatement.execute(EncryptPreparedStatement.java:142)
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:497)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy120.execute(Unknown Source)
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 org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)
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:497)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
... 13 common frames omitted
```
### Reason analyze (If you can)
no preparedStatement parameter after `on duplicate key update` work fine.
such as this sql:
`insert into user_info (cust_id, unique_id, name, ident, mobile, password, reg_time, modify_time, reg_source, reg_app_channel, reg_device_info, status, is_mobile_authed, complate_cust_info, time_inst, time_upd, has_checked, system) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, now() , ?, ?, ?) ON DUPLICATE KEY UPDATE id = 1`
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
### Example codes for reproduce this issue (such as a github link).
----------------------------------------------------------------
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:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] xiyelife edited a comment on issue
#3855: Insert with encrypt does'not handle with the `on duplicate key
update` 's prepared statement patameters.
Posted by GitBox <gi...@apache.org>.
xiyelife edited a comment on issue #3855: Insert with encrypt does'not handle with the `on duplicate key update` 's prepared statement patameters.
URL: https://github.com/apache/incubator-shardingsphere/issues/3855#issuecomment-570444692
And don't support `INSERT IGNORE INTO ... ON DUPLICATE KEY UPDATE`
----------------------------------------------------------------
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:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] xiyelife commented on issue #3855:
Insert with encrypt does'not handle with the `on duplicate key update` 's
prepared statement patameters.
Posted by GitBox <gi...@apache.org>.
xiyelife commented on issue #3855: Insert with encrypt does'not handle with the `on duplicate key update` 's prepared statement patameters.
URL: https://github.com/apache/incubator-shardingsphere/issues/3855#issuecomment-570454890
@terrymanu Please assign this issue to me , I'll fix it, 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:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] xiyelife commented on issue #3855:
Insert with encrypt does'not handle with the `on duplicate key update` 's
prepared statement patameters.
Posted by GitBox <gi...@apache.org>.
xiyelife commented on issue #3855: Insert with encrypt does'not handle with the `on duplicate key update` 's prepared statement patameters.
URL: https://github.com/apache/incubator-shardingsphere/issues/3855#issuecomment-570444692
And don't support `INSERT IGNORE ... ON DUPLICATE KEY UPDATE`
----------------------------------------------------------------
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:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #3855:
Insert with encrypt does'not handle with the `on duplicate key update` 's
prepared statement patameters.
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #3855: Insert with encrypt does'not handle with the `on duplicate key update` 's prepared statement patameters.
URL: https://github.com/apache/incubator-shardingsphere/issues/3855#issuecomment-570454988
@xiyelife Thank you, assigned.
----------------------------------------------------------------
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:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu closed issue #3855: Insert
with encrypt does'not handle with the `on duplicate key update` 's prepared
statement patameters.
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #3855: Insert with encrypt does'not handle with the `on duplicate key update` 's prepared statement patameters.
URL: https://github.com/apache/incubator-shardingsphere/issues/3855
----------------------------------------------------------------
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:
users@infra.apache.org
With regards,
Apache Git Services