shardingsphere: Can not update sharding key, logic table: [], column: []

I am using Spring Data JPA with Hibernate (with mysql), Spring Boot 2 and SS 4.1.1.

Things were working fine with 4.0.0-RC1. After i updated version to 4.1.1 i get the following exception when running an update queryy through session.update(entity) or entity.save() :-

org.apache.shardingsphere.core.exception.ShardingException: Can not update sharding key, logic table: [ACCOUNT_INFO], column: [org.apache.shardingsphere.sql.parser.sql.segment.dml.assignment.AssignmentSegment@5afa0b1a].

My Table ACCOUNT_INFO table has the following columns:-

id (PRIMARY KEY)
version (VERSION FOR OPTIMISTIC LOCKING)
account_id (THIS IS THE SHARD COLUMN CONFIGURED)
zip
name

The hibernate generated sql query for update looks like :-

update ACCOUNT_INFO set name=??, account_id=??, zip=??, version=?? where id = ?? and version = ??

This statement throws the exception when executed through DAL:- org.apache.shardingsphere.core.exception.ShardingException: Can not update sharding key, logic table: [ACCOUNT_INFO], column: [org.apache.shardingsphere.sql.parser.sql.segment.dml.assignment.AssignmentSegment@5afa0b1a]. This was not happening in 4.0.0-RC1.

I debugged a bit and I found that this expects account_id to be present in the where clause. Unfortunately, Spring Data JPA does not provide a way for me to specify what columns will be there in where clause. I do not want to write custom JPA query because then version management will have to be done manually and also will become tedious as my table has large columns.

I tried using the hint manager but it didn’t work :-

try(HintManager hintManager = HintManager.getInstance()) {
          hintManager
              .addDatabaseShardingValue("ACCOUNT_INFO", accountEntity.getAccountId());
          hintManager.addTableShardingValue("ACCOUNT_INFO", accountEntity.getAccountId());

          session.update(accountEntity);
          transaction.commit();
        }

How do i solve this ??

About this issue

Original URL
State: closed
Created 4 years ago
Reactions: 2
Comments: 42 (20 by maintainers)

Most upvoted comments

Thanks for your feedback. @heqiao @sabz90 I read the conversation above, and gave my thinking here.

First, the annotation @Column(name = "xxx", updatable = false) seems one solution to avoid SET shardingKey=xxxand the exception Can not update sharding key accordingly.

Second, how to understand But this will execute the update query in all the shards :) Which is not optimal.? Does it mean that if @Column(name = "xxx", updatable = false) is added, the where clause will not contain shardingKey=xx, which will cause all shards updating?

Third, Do you think the following improvement is reasonable and in your expectation?

if (no shardingKey in SET Clause)
    then PASS
else if (the value of shardingKey in SET Clause == the value of shardingKey in WHERE Clause)
    then PASS
else
    then an exception

tristaZero on Oct 26, 2020

There is indeed a workaround - Embed the primary key and sharding key on the entity level. So the sharding key is disguised as part of JPA’s id. Not pretty, but works… However, it’s definitely not something that I could promote to coworkers “Hey we should use this cool sharding tool, and it works with JPA only if you carefully configure it”

heqiao on Oct 22, 2020

Hi, @heqiao Thanks for your kind explanation.

Actually, the idea I mentioned before is not an optimal solution for SET shardingKey. If users really want to update shardingKey, shardingsphere should execute that update and move the actual row to the new target table. But for now, we can take it as a better solution to balance updating safety and JPA case.

tristaZero on Oct 26, 2020

@lMasterSparkl Sorry, I’m not very familiar with JPA, this may require you to study how JPA is used. Looking forward to your good news.

strongduanmu on Mar 17, 2022

IMO, DONT UPDATE Sharding key SHOULD be a principle in SharidngSphere instead of an implicit configuration to point it.

Agreed. The uses cases for updating sharding keys are rare and probably should reconsider the sharding strategy if it did happen. However, to be clear, this question is not asking for providing capability of updating sharding keys like #8006 . The title of the issue can be misleading.

This issue is about correctly interpreting JPA generated update sqls as sharding key is included in set clause with value unchanged, thus causing misunderstanding from sharding sphere that sharding key is being updated. It’s not.

I just wanted to clarify in case anyone was misled by the title.

heqiao on Nov 6, 2020

shardingsphere: Can not update sharding key, logic table: [**], column: [**]

About this issue

Most upvoted comments

shardingsphere: Can not update sharding key, logic table: [], column: []