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

[sabz90]

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 ??

[tristaZero]

@sabz90 Are you going to update the sharding key of logic table? FYI, If you updated the value of sharding key, it would be possible to move this row to another table or db.

[kimmking]

As @tristaZero said, sharding-key can't be update to another value cause it will change the distribution of data.
You also can delete the old key and insert a row with new key.

[sabz90]

@tristaZero @kimmking The query does not change the Sharding key of the table. If that value does not appear in SET statement, then the query will end up being executed on all shards which is not optimal. Another option is putting the sharding column in the where clause but that gets complicated as Hibernate does not allow including columns in the where clause.

maybe make this validation optional, based on some configuration?

[tristaZero]

The query does not change the Sharding key of the table. If that value does not appear in SET statement

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

Sorry? account_id is a sharding key and in set clause, isn't it?

[sabz90]

The query does not change the Sharding key of the table. If that value does not appear in SET statement

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

Sorry? account_id is a sharding key and in set clause, isn't it?

Yes, but the value is same as what was already present in the DB. But yes, there's no way for sharding sphere to know if the new value is equal to the old value, without it being specified in the where clause. Unfortunately, hibernate doesn't provide a way to include custom columns in the where clause

[tristaZero]

hibernate doesn't provide a way to include custom columns in the where clause

The main point here is that the sharding key appears in select items. If you do not want to update the sharding key, could you ignore it in the list of select items?

[heqiao]

@tristaZero @kimmking Please reopen this issue. I'm on the same boat with @sabz90 with same setup and same issue. I didn't attempt to update sharding key. When update with JPA/Hibernate, the sharding key is not included in the where clause and the error message stated Can not update sharding key. Moreover, it seems all similar issues were closed without a proper solution. such as #5596

[heqiao]

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]

Actually.... after a second read through of the above conversation, Trista's suggestion makes more sense. The set clause should not have sharding key. So the sharding key should not be updatable. And this solves the problem.

@Column(name = "xxx", updatable = false)

I think I need a Rubber Duck.

[sabz90]

Actually.... after a second read through of the above conversation, Trista's suggestion makes more sense. The set clause should not have sharding key. So the sharding key should not be updatable. And this solves the problem.

@Column(name = "xxx", updatable = false)

I think I need a Rubber Duck.

But this will execute the update query in all the shards :) Which is not optimal.
So even if the sharding column appears in the SET clause, the value may not have really changed... just that the field appears in SET clause.

Maybe there can be some annotation from sharding-sphere or some flag by which we can suppress that exception from SS because it's much more complex to explicitly include the sharding column in the where clause, especially when there's @Version involved.

[sabz90]

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"

It would be great if you could share an example!

[heqiao]

Actually.... after a second read through of the above conversation, Trista's suggestion makes more sense. The set clause should not have sharding key. So the sharding key should not be updatable. And this solves the problem.
@Column(name = "xxx", updatable = false)
I think I need a Rubber Duck.

But this will execute the update query in all the shards :) Which is not optimal.
So even if the sharding column appears in the SET clause, the value may not have really changed... just that the field appears in SET clause.

Maybe there can be some annotation from sharding-sphere or some flag by which we can suppress that exception from SS because it's much more complex to explicitly include the sharding column in the where clause, especially when there's @Version involved.

ah that's right. Thought I found a solution :(

[heqiao]

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"

It would be great if you could share an example!

@Embeddable
@Data
public class CompositeId implements Serializable {
    private Long id; // pk
    private String accountId; // sharding key
}

@Table("xxxx")
@Entity 
public class YourClass {
    @EmbeddedId
    private CompositeId compositeId;
}

Then you can do findByCompositeIdAccountId.

[tristaZero]

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?
```JAVA
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
````

[heqiao]

Thanks for the detailed response with a solution. @tristaZero

That's exactly what was happening. When updatable = false is being used, the where clause does not contain shardingKey=xx and will end up with all shards updating.

Additionally, the where clause only contains pk =yy no matter updatable = false is being used or not.

The logic in the third paragraph makes a lot sense to me. Just want to make sure the sharding key will be included in where clause in the PASS cases.

[tristaZero]

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]

Hi @strongduanmu
I am unsure whether you are interested in An improvement for checking update condition. Still, I guess you are the right one for it. :) Looking for your reply, thanks.

BTW, ShardingUpdateStatementValidator looks the target class for this issue.

[strongduanmu]

Hi @strongduanmu
I am unsure whether you are interested in An improvement for checking update condition. Still, I guess you are the right one for it. :) Looking for your reply, thanks.

BTW, ShardingUpdateStatementValidator looks the target class for this issue.

@tristaZero Of course, I am very willing to optimize this problem. 😀

[kimmking]

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

[heqiao]

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.

[tristaZero]

The title of the issue can be misleading.

+1, however, users may locate this issue by the concrete exception info, I guess.
Besides, as I said above, updating sharding key (comes with row migration) is supposed to be one of the capabilities for a perfect distributed database solution. Unfortunately, SS can not has better support for it but is moving towards it at present.
Hence, we can solve this issue by having users add an annotation for JPA and avoid this exception by digesting some cases inside SS.

[strongduanmu]

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 @heqiao I have checked the logic in ShardingUpdateStatementValidator, whether it is version 4.1.1 or version 5.0.0-alpha, it has already supported the verification logic of whether the sharding key is the same in the SET and WHERE clauses. The code is as follows:

/**
 * Sharding update statement validator.
 */
public final class ShardingUpdateStatementValidator implements ShardingStatementValidator<UpdateStatement> {

    @Override
    public void validate(final ShardingRule shardingRule, final UpdateStatement sqlStatement, final List<Object> parameters) {
        String tableName = sqlStatement.getTables().iterator().next().getTableName().getIdentifier().getValue();
        for (AssignmentSegment each : sqlStatement.getSetAssignment().getAssignments()) {
            String shardingColumn = each.getColumn().getIdentifier().getValue();
            if (shardingRule.isShardingColumn(shardingColumn, tableName)) {
                Optional<Object> shardingColumnSetAssignmentValue = getShardingColumnSetAssignmentValue(each, parameters);
                Optional<Object> shardingValue = Optional.empty();
                Optional<WhereSegment> whereSegmentOptional = sqlStatement.getWhere();
                if (whereSegmentOptional.isPresent()) {
                    shardingValue = getShardingValue(whereSegmentOptional.get(), parameters, shardingColumn);
                }
                if (shardingColumnSetAssignmentValue.isPresent() && shardingValue.isPresent() && shardingColumnSetAssignmentValue.get().equals(shardingValue.get())) {
                    continue;
                }
                throw new ShardingSphereException("Can not update sharding key, logic table: [%s], column: [%s].", tableName, each);
            }
        }
    }
}

[tristaZero]

@strongduanmu Nice!
Therefore, guys, another solution for this issue is to try 5.0.0-alpha. :)

[heqiao]

@strongduanmu Thank you for diving into it. @tristaZero My apologies that the proposed solution may not work as I originally agreed. The where clause doesn't contain the sharding key.

e.g. update ACCOUNT_INFO set name=??, account_id=??, zip=??, version=?? where id = ?? was the generated sql with account_id being the sharding key. The shardingValue is empty in the code @strongduanmu posted (ShardingUpdateStatementValidator.java).

So the logic may be

if (no shardingKey in SET Clause)
    then PASS
else if (no shardingKey in WHERE Clause)
    then append sharding column and value to WHERE Clause
else if (the value of shardingKey in SET Clause == the value of shardingKey in WHERE Clause)
    then PASS
else
    then an exception

[sabz90]

@strongduanmu Thank you for diving into it. @tristaZero My apologies that the proposed solution may not work as I originally agreed. The where clause doesn't contain the sharding key.

e.g. update ACCOUNT_INFO set name=??, account_id=??, zip=??, version=?? where id = ?? was the generated sql with account_id being the sharding key. The shardingValue is empty in the code @strongduanmu posted (ShardingUpdateStatementValidator.java).

So the logic may be

if (no shardingKey in SET Clause)
    then PASS
else if (no shardingKey in WHERE Clause)
    then append sharding column and value to WHERE Clause
else if (the value of shardingKey in SET Clause == the value of shardingKey in WHERE Clause)
    then PASS
else
    then an exception

The second condition will fix this issue but it is a risky option - we are just assuming that the user did not intend to put the sharding column in the SET clause.

else if (no shardingKey in WHERE Clause)
    then append sharding column and value to WHERE Clause

So a safer option would be to define an Annotation, something like "@ForceWhereClause" which should then indicate to sharding-sphere that it has to use the value from SET clause and either use that to figure out which is the target shard, or put in a where clause along in the final statement and let things happen naturally.

For now i have solved the problem by using CompositeKey @IdClass method , which makes hibernate include the column in where clause, but it's more of a hack since the sharding column is not really a composite key in the database level.

[heqiao]

@sabz90 your approach is probably more sustainable.

I also found drawbacks of CompositeKey like the id won't be generated with snowflake as there is no @id annotation.

[strongduanmu]

@strongduanmu Thank you for diving into it. @tristaZero My apologies that the proposed solution may not work as I originally agreed. The where clause doesn't contain the sharding key.

e.g. update ACCOUNT_INFO set name=??, account_id=??, zip=??, version=?? where id = ?? was the generated sql with account_id being the sharding key. The shardingValue is empty in the code @strongduanmu posted (ShardingUpdateStatementValidator.java).

So the logic may be

if (no shardingKey in SET Clause)
    then PASS
else if (no shardingKey in WHERE Clause)
    then append sharding column and value to WHERE Clause
else if (the value of shardingKey in SET Clause == the value of shardingKey in WHERE Clause)
    then PASS
else
    then an exception

@heqiao If sharding column is not included in the WHERE clause, can we remove the sharding column in the SET clause by setting the @Column(name = "xxx", updatable = false) annotation?

The logic you mentioned —— then append sharding column and value to WHERE Clause, I think it is not suitable for SS to deal with, this method will have a bad effect on other normal SQL.

[strongduanmu]

So a safer option would be to define an Annotation, something like "@ForceWhereClause" which should then indicate to sharding-sphere that it has to use the value from SET clause and either use that to figure out which is the target shard, or put in a where clause along in the final statement and let things happen naturally.

@sabz90 I think adding annotations is a good idea, but I think it is more appropriate for hibernate to provide this annotation.
Users can freely control which fields are added to the WHERE clause and which fields are added to the SET clause through hibernate annotations.

[strongduanmu]

Closed because there was no reply.