ppj19891020 opened a new issue #1798: spring JPA simple sql update error URL: https://github.com/apache/incubator-shardingsphere/issues/1798 ## Bug Report spring jpa update domain fail. junit test ``` @Test public void testUpdateMessage(){ Long traceId = 3324610450175559130l; List<MessagePo> messagePos = messageRepository.getByTraceId(traceId); MessagePo messagePo = messagePos.get(0); messagePo.setStatus(ClickRequest.Status.CLICKED.name()); messageRepository.save(messagePo); } ``` ### Which version of ShardingSphere did you use? Version: 3.0.0 ### Which project did you use? Sharding-JDBC or Sharding-Proxy? Sharding-JDBC ### Expected behavior sql update success ### Actual behavior ``` 2019-01-22 18:46:19.471 INFO 24457 --- [ main] Sharding-Sphere-SQL : Rule Type: sharding 2019-01-22 18:46:19.471 INFO 24457 --- [ main] Sharding-Sphere-SQL : Logic SQL: select messagepo0_.id as id1_0_, messagepo0_.appVersion as appVersi2_0_, messagepo0_.clickTime as clickTim3_0_, messagepo0_.devicePlatform as devicePl4_0_, messagepo0_.deviceToken as deviceTo5_0_, messagepo0_.jobId as jobId6_0_, messagepo0_.osVersion as osVersio7_0_, messagepo0_.sendTime as sendTime8_0_, messagepo0_.status as status9_0_, messagepo0_.traceId as traceId10_0_, messagepo0_.userId as userId11_0_ from push_message messagepo0_ where messagepo0_.traceId=? 2019-01-22 18:46:19.471 INFO 24457 --- [ main] Sharding-Sphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=push_message, alias=Optional.of(messagepo0_))]), conditions=Conditions(orCondition=OrCondition(andConditions=[AndCondition(conditions=[Condition(column=Column(name=traceId, tableName=push_message), operator=EQUAL, positionValueMap={}, positionIndexMap={0=0})])])), sqlTokens=[TableToken(skippedSchemaNameLength=0, originalLiterals=push_message)], parametersIndex=1)), containStar=false, selectListLastPosition=411, groupByLastPosition=0, items=[CommonSelectItem(expression=messagepo0_.devicePlatform, alias=Optional.of(devicePl4_0_)), CommonSelectItem(expression=messagepo0_.deviceToken, alias=Optional.of(deviceTo5_0_)), CommonSelectItem(expression=messagepo0_.status, alias=Optional.of(status9_0_)), CommonSelectItem(expression=messagepo0_.appVersion, alias=Optional.of(appVersi2_0_)), CommonSelectItem(expression=messagepo0_.osVersion, alias=Optional.of(osVersio7_0_)), CommonSelectItem(expression=messagepo0_.userId, alias=Optional.of(userId11_0_)), CommonSelectItem(expression=messagepo0_.clickTime, alias=Optional.of(clickTim3_0_)), CommonSelectItem(expression=messagepo0_.jobId, alias=Optional.of(jobId6_0_)), CommonSelectItem(expression=messagepo0_.traceId, alias=Optional.of(traceId10_0_)), CommonSelectItem(expression=messagepo0_.id, alias=Optional.of(id1_0_)), CommonSelectItem(expression=messagepo0_.sendTime, alias=Optional.of(sendTime8_0_))], groupByItems=[], orderByItems=[], limit=null, subQueryStatement=null) 2019-01-22 18:46:19.472 INFO 24457 --- [ main] Sharding-Sphere-SQL : Actual SQL: ds2 ::: select messagepo0_.id as id1_0_, messagepo0_.appVersion as appVersi2_0_, messagepo0_.clickTime as clickTim3_0_, messagepo0_.devicePlatform as devicePl4_0_, messagepo0_.deviceToken as deviceTo5_0_, messagepo0_.jobId as jobId6_0_, messagepo0_.osVersion as osVersio7_0_, messagepo0_.sendTime as sendTime8_0_, messagepo0_.status as status9_0_, messagepo0_.traceId as traceId10_0_, messagepo0_.userId as userId11_0_ from push_message messagepo0_ where messagepo0_.traceId=? ::: [[3324610450175559130]] 2019-01-22 18:46:20.171 INFO 24457 --- [ main] Sharding-Sphere-SQL : Rule Type: sharding 2019-01-22 18:46:20.171 INFO 24457 --- [ main] Sharding-Sphere-SQL : Logic SQL: select messagepo0_.id as id1_0_0_, messagepo0_.appVersion as appVersi2_0_0_, messagepo0_.clickTime as clickTim3_0_0_, messagepo0_.devicePlatform as devicePl4_0_0_, messagepo0_.deviceToken as deviceTo5_0_0_, messagepo0_.jobId as jobId6_0_0_, messagepo0_.osVersion as osVersio7_0_0_, messagepo0_.sendTime as sendTime8_0_0_, messagepo0_.status as status9_0_0_, messagepo0_.traceId as traceId10_0_0_, messagepo0_.userId as userId11_0_0_ from push_message messagepo0_ where messagepo0_.id=? 2019-01-22 18:46:20.171 INFO 24457 --- [ main] Sharding-Sphere-SQL : SQLStatement: SelectStatement(super=DQLStatement(super=AbstractSQLStatement(type=DQL, tables=Tables(tables=[Table(name=push_message, alias=Optional.of(messagepo0_))]), conditions=Conditions(orCondition=OrCondition(andConditions=[])), sqlTokens=[TableToken(skippedSchemaNameLength=0, originalLiterals=push_message)], parametersIndex=1)), containStar=false, selectListLastPosition=433, groupByLastPosition=0, items=[CommonSelectItem(expression=messagepo0_.osVersion, alias=Optional.of(osVersio7_0_0_)), CommonSelectItem(expression=messagepo0_.sendTime, alias=Optional.of(sendTime8_0_0_)), CommonSelectItem(expression=messagepo0_.userId, alias=Optional.of(userId11_0_0_)), CommonSelectItem(expression=messagepo0_.status, alias=Optional.of(status9_0_0_)), CommonSelectItem(expression=messagepo0_.id, alias=Optional.of(id1_0_0_)), CommonSelectItem(expression=messagepo0_.traceId, alias=Optional.of(traceId10_0_0_)), CommonSelectItem(expression=messagepo0_.appVersion, alias=Optional.of(appVersi2_0_0_)), CommonSelectItem(expression=messagepo0_.clickTime, alias=Optional.of(clickTim3_0_0_)), CommonSelectItem(expression=messagepo0_.jobId, alias=Optional.of(jobId6_0_0_)), CommonSelectItem(expression=messagepo0_.deviceToken, alias=Optional.of(deviceTo5_0_0_)), CommonSelectItem(expression=messagepo0_.devicePlatform, alias=Optional.of(devicePl4_0_0_))], groupByItems=[], orderByItems=[], limit=null, subQueryStatement=null) 2019-01-22 18:46:20.171 INFO 24457 --- [ main] Sharding-Sphere-SQL : Actual SQL: ds0 ::: select messagepo0_.id as id1_0_0_, messagepo0_.appVersion as appVersi2_0_0_, messagepo0_.clickTime as clickTim3_0_0_, messagepo0_.devicePlatform as devicePl4_0_0_, messagepo0_.deviceToken as deviceTo5_0_0_, messagepo0_.jobId as jobId6_0_0_, messagepo0_.osVersion as osVersio7_0_0_, messagepo0_.sendTime as sendTime8_0_0_, messagepo0_.status as status9_0_0_, messagepo0_.traceId as traceId10_0_0_, messagepo0_.userId as userId11_0_0_ from push_message messagepo0_ where messagepo0_.id=? ::: [[4657956192256]] 2019-01-22 18:46:20.171 INFO 24457 --- [ main] Sharding-Sphere-SQL : Actual SQL: ds1 ::: select messagepo0_.id as id1_0_0_, messagepo0_.appVersion as appVersi2_0_0_, messagepo0_.clickTime as clickTim3_0_0_, messagepo0_.devicePlatform as devicePl4_0_0_, messagepo0_.deviceToken as deviceTo5_0_0_, messagepo0_.jobId as jobId6_0_0_, messagepo0_.osVersion as osVersio7_0_0_, messagepo0_.sendTime as sendTime8_0_0_, messagepo0_.status as status9_0_0_, messagepo0_.traceId as traceId10_0_0_, messagepo0_.userId as userId11_0_0_ from push_message messagepo0_ where messagepo0_.id=? ::: [[4657956192256]] 2019-01-22 18:46:20.171 INFO 24457 --- [ main] Sharding-Sphere-SQL : Actual SQL: ds2 ::: select messagepo0_.id as id1_0_0_, messagepo0_.appVersion as appVersi2_0_0_, messagepo0_.clickTime as clickTim3_0_0_, messagepo0_.devicePlatform as devicePl4_0_0_, messagepo0_.deviceToken as deviceTo5_0_0_, messagepo0_.jobId as jobId6_0_0_, messagepo0_.osVersion as osVersio7_0_0_, messagepo0_.sendTime as sendTime8_0_0_, messagepo0_.status as status9_0_0_, messagepo0_.traceId as traceId10_0_0_, messagepo0_.userId as userId11_0_0_ from push_message messagepo0_ where messagepo0_.id=? ::: [[4657956192256]] 2019-01-22 18:46:20.171 INFO 24457 --- [ main] Sharding-Sphere-SQL : Actual SQL: ds3 ::: select messagepo0_.id as id1_0_0_, messagepo0_.appVersion as appVersi2_0_0_, messagepo0_.clickTime as clickTim3_0_0_, messagepo0_.devicePlatform as devicePl4_0_0_, messagepo0_.deviceToken as deviceTo5_0_0_, messagepo0_.jobId as jobId6_0_0_, messagepo0_.osVersion as osVersio7_0_0_, messagepo0_.sendTime as sendTime8_0_0_, messagepo0_.status as status9_0_0_, messagepo0_.traceId as traceId10_0_0_, messagepo0_.userId as userId11_0_0_ from push_message messagepo0_ where messagepo0_.id=? ::: [[4657956192256]] 2019-01-22 18:46:20.188 INFO 24457 --- [ main] o.h.e.internal.DefaultLoadEventListener : HHH000327: Error performing load command : org.hibernate.HibernateException: More than one row with the given identifier was found: 4657956192256, for class: com.dxy.platform.push.sharding.domain.MessagePo org.springframework.orm.jpa.JpaSystemException: More than one row with the given identifier was found: 4657956192256, for class: com.dxy.platform.push.sharding.domain.MessagePo; nested exception is org.hibernate.HibernateException: More than one row with the given identifier was found: 4657956192256, for class: com.dxy.platform.push.sharding.domain.MessagePo at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:333) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:525) at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:209) at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) 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.data.repository.core.support.SurroundingTransactionDetectorMethodInterceptor.invoke(SurroundingTransactionDetectorMethodInterceptor.java:57) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) at com.sun.proxy.$Proxy128.save(Unknown Source) ``` ### Reason analyze (If you can) ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. table: push_message KeyGeneratorColumnName:traceId ``` @Bean(name = "shardingDataSource") public DataSource shardingDataSource() throws SQLException { // 配置真实数据源 Map<String, DataSource> dataSourceMap = new HashMap<>(); dataSourceMap.put("ds0",sharding01DataSource()); dataSourceMap.put("ds1",sharding02DataSource()); dataSourceMap.put("ds2",sharding03DataSource()); dataSourceMap.put("ds3",sharding04DataSource()); // 配置Order表规则 TableRuleConfiguration orderTableRuleConfig = new TableRuleConfiguration(); orderTableRuleConfig.setLogicTable("push_message"); orderTableRuleConfig.setActualDataNodes("ds${0..3}.push_message"); //分布式主键 orderTableRuleConfig.setKeyGeneratorColumnName("id"); orderTableRuleConfig.setKeyGenerator(shardingKeygen); // 配置分库 + 分表策略 orderTableRuleConfig.setDatabaseShardingStrategyConfig(new InlineShardingStrategyConfiguration("traceId", "ds${traceId % 4}")); // 配置分片规则 ShardingRuleConfiguration shardingRuleConfig = new ShardingRuleConfiguration(); shardingRuleConfig.getTableRuleConfigs().add(orderTableRuleConfig); //配置项 Properties properties = new Properties(); properties.setProperty("sql.show","true"); DataSource dataSource = ShardingDataSourceFactory.createDataSource( dataSourceMap, shardingRuleConfig, new ConcurrentHashMap(), properties); return dataSource; } ```
---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
