preface
Recently I used ShardingSphere to implement mysql data sub-table in my work. Here I record some pits encountered.
ShardingSphere profile
Apache ShardingSphere is an open source ecosystem of distributed database solutions, providing standardized data level extension, distributed transactions and distributed governance.
Update fragment field problem
Sharding-jdbc is used this time, and only table, not library (avoid the distributed transaction problem caused by cross-library, of course, ShardingSphere also supports XA, Seata and other distributed transactions).
The following error occurs when the UPDATE statement is executed to update a fragment field:
org.apache.shardingsphere.underlying.common.exception.ShardingSphereException: Can not update sharding key, logic table: [order], column: [org.apache.shardingsphere.sql.parser.sql.segment.dml.assignment.AssignmentSegment@2b877a54]. at org.apache.shardingsphere.sharding.route.engine.validator.impl.ShardingUpdateStatementValidator.validate(ShardingUpdateS tatementValidator.java:59) at org.apache.shardingsphere.sharding.route.engine.validator.impl.ShardingUpdateStatementValidator.validate(ShardingUpdateS tatementValidator.java:42) at org.apache.shardingsphere.sharding.route.engine.ShardingRouteDecorator.lambda$decorate$0(ShardingRouteDecorator.java:61) at java.util.Optional.ifPresent(Optional.java:159) ...Copy the codeFind the source code according to the call stack
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(); // set declaration for (AssignmentSegment each: sqlStatement.getSetAssignment().getAssignments()) { String shardingColumn = each.getColumn().getIdentifier().getValue(); if (shardingRule.isShardingColumn(shardingColumn, TableName)) {/ / when the set statement section for the shard key into the 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. The get () equals (shardingValue. The get ())) {/ / when the set clause set the new value and the old values are the same in the where clause can jump out the continue; } // throw new ShardingSphereException("Can not update sharding key, logic table: [%s], column: [%s].", tableName, each); }}}Copy the codeWhen a sharding field is updated, exceptions are not raised only if the new value set in the set clause is equal to the old value given in the WHERE clause.
Between and resolves the problem
Since it was to transform an old project at that time, the original business codes, SQL statements and so on were not moved when the transformation was made. The original DataSource was directly replaced with the newly configured ShardingDataSource.
However, during the testing phase, a strange error was detected:
ava.lang.ClassCastException: org.apache.shardingsphere.sql.parser.sql.segment.dml.item.ExpressionProjectionSegment cannot be cast to org.apache.shardingsphere.sql.parser.sql.segment.dml.column.ColumnSegment at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.createBetweenSegment(MySQLVisitor.java:351) at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitPredicate(MySQLVisitor.java:313) at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitPredicate(MySQLVisitor.java:121) at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$PredicateContext.accept(MySQLStatementParser.java:1169 0) at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18) at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitBooleanPrimary(MySQLVisitor.java:273) at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitBooleanPrimary(MySQLVisitor.java:121) at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$BooleanPrimaryContext.accept(MySQLStatementParser.java :11463) at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18) at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitExpr(MySQLVisitor.java:258) at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitExpr(MySQLVisitor.java:121) at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$ExprContext.accept(MySQLStatementParser.java:11241) at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18) at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitExpr(MySQLVisitor.java:261) at org.apache.shardingsphere.sql.parser.mysql.visitor.MySQLVisitor.visitExpr(MySQLVisitor.java:121) at org.apache.shardingsphere.sql.parser.autogen.MySQLStatementParser$ExprContext.accept(MySQLStatementParser.java:11241) at org.antlr.v4.runtime.tree.AbstractParseTreeVisitor.visit(AbstractParseTreeVisitor.java:18)Copy the codeBusiness SQL looks like this:
select * from order where compId = 2 and DATE_FORMAT(updateTime,'%Y-%m-%d') between DATE_FORMAT('20210616','%Y-%m-%d') and DATE_FORMAT('20210617','%Y-%m-%d')
Notice that in the between and clause, we use a function
Direct Source Location Wave (current version: 4.1.1) :
private PredicateSegment createBetweenSegment(final PredicateContext ctx) { ColumnSegment column = (ColumnSegment) visit(ctx.bitExpr(0)); ExpressionSegment between = (ExpressionSegment) visit(ctx.bitexpr (1)); ExpressionSegment and = (ExpressionSegment) visit(ctx.predicate()); return new PredicateSegment(ctx.getStart().getStartIndex(), ctx.getStop().getStopIndex(), column, new PredicateBetweenRightValue(between, and)); }Copy the codeInterrupt point debugging:
Direct call visit (CTX bitExpr (0)) found that the returned type is ExpressionProjectionSegment, casts for ColumnSegment result in an error.
This part of the code is the process of the SQL parsing engine parsing the SQL statement into an abstract syntax tree. In createBetweenSegment, the ColumnSegment before the between keyword is considered to be a ColumnSegment by default. Access to the syntax tree node as expression (ExpressionProjectionSegment).
The solution at the time was to rewrite SQL without using expressions to circumvent this problem, which was later found to have been fixed in the latest version (5.0.0-alpha).
private BetweenExpression createBetweenSegment(final PredicateContext ctx) { ExpressionSegment left = (ExpressionSegment) visit(ctx.bitExpr(0)); ExpressionSegment between = (ExpressionSegment) visit(ctx.bitExpr(1)); ExpressionSegment and = (ExpressionSegment) visit(ctx.predicate()); boolean not = null ! = ctx.NOT(); return new BetweenExpression(ctx.start.getStartIndex(), ctx.stop.getStopIndex(), left, between, and, not); }Copy the codeWhen parsing “BETWEEN”, the default value is “between”.
conclusion
1. When a shard field is updated using the UPDATE statement, only the shard field is allowed to be set to the same value as the old value (the old value must be in the WHERE clause)
2. Expressions are not supported before the between keyword in version 4.1.1. Support is provided in 5.0.0-alpha