Issue 1:
Feature Request

4.0.0-RC3 version not support ON DUPLICATE KEY UPDATE parameters

For English only, other languages will not accept.

Please pay attention on issues you submitted, because we maybe need more details.

If no response more than 7 days and we cannot make decision by current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Is your feature request related to a problem?

#3210

#1756

Describe the feature you would like.

ON DUPLICATE KEY UPDATE support parameters

this is my implement：

add 3 files ：

OnDuplicateKeyValuesExtractor.java

package org.apache.shardingsphere.core.parse.core.extractor.impl.dml.insert;

import java.util.ArrayList;

import java.util.Collection;

import java.util.Collections;

import java.util.LinkedList;

import java.util.List;

import java.util.Map;

import com.google.common.base.Optional;

import org.antlr.v4.runtime.ParserRuleContext;

import org.apache.shardingsphere.core.parse.core.extractor.api.CollectionSQLSegmentExtractor;

import org.apache.shardingsphere.core.parse.core.extractor.impl.common.expression.ExpressionExtractor;

import org.apache.shardingsphere.core.parse.core.extractor.util.ExtractorUtils;

import org.apache.shardingsphere.core.parse.core.extractor.util.RuleName;

import org.apache.shardingsphere.core.parse.sql.segment.dml.assignment.OnDuplicateKeyValuesSegment;

import org.apache.shardingsphere.core.parse.sql.segment.dml.expr.ExpressionSegment;

/**

On duplicate key values extractor.

@author sonny.zhao

*/

public final class OnDuplicateKeyValuesExtractor implements CollectionSQLSegmentExtractor {

private final ExpressionExtractor expressionExtractor = new ExpressionExtractor();

@OverRide

public Collection extract(final ParserRuleContext ancestorNode, final Map<ParserRuleContext, Integer> parameterMarkerIndexes) {

Optional onDuplicateKeyValuesClauseNode = ExtractorUtils.findFirstChildNode(ancestorNode, RuleName.ON_DUPLICATE_KEY_CLAUSE);

if (!onDuplicateKeyValuesClauseNode.isPresent()) {

return Collections.emptyList();

}

Collection result = new LinkedList<>();

result.add(new OnDuplicateKeyValuesSegment(onDuplicateKeyValuesClauseNode.get().getStart().getStartIndex(), onDuplicateKeyValuesClauseNode.get().getStop().getStopIndex(), extractExpressionSegments(onDuplicateKeyValuesClauseNode.get(), parameterMarkerIndexes)));

return result;

}

private List extractExpressionSegments(final ParserRuleContext assignmentValuesNode, final Map<ParserRuleContext, Integer> parameterMarkerIndexes) {

Collection parserRuleContexts = ExtractorUtils.getAllDescendantNodes(assignmentValuesNode, RuleName.PARAMETER_MARKER);

List result = new ArrayList<>(parserRuleContexts.size());

for (ParserRuleContext each : parserRuleContexts) {

Optional<? extends ExpressionSegment> expressionSegment = expressionExtractor.extract(each, parameterMarkerIndexes);

if (expressionSegment.isPresent()) {

result.add(expressionSegment.get());

}

}

return result;

}

}

OnDuplicateKeyValuesFiller.java

package org.apache.shardingsphere.core.parse.core.filler.impl.dml;

import org.apache.shardingsphere.core.parse.core.filler.SQLSegmentFiller;

import org.apache.shardingsphere.core.parse.sql.segment.dml.assignment.OnDuplicateKeyValuesSegment;

import org.apache.shardingsphere.core.parse.sql.statement.SQLStatement;

import org.apache.shardingsphere.core.parse.sql.statement.dml.InsertStatement;

/**

OnDuplicateKey values filler.

@author sonny.zhao

*/

public final class OnDuplicateKeyValuesFiller implements SQLSegmentFiller {

@OverRide

public void fill(final OnDuplicateKeyValuesSegment sqlSegment, final SQLStatement sqlStatement) {

((InsertStatement) sqlStatement).getOnDuplicateKeyValues().add(sqlSegment);

}

}

OnDuplicateKeyValueContext.java

package org.apache.shardingsphere.core.preprocessor.segment.insert;

import java.util.ArrayList;

import java.util.Collection;

import java.util.Collections;

import java.util.List;

import lombok.Getter;

import lombok.ToString;

import org.apache.shardingsphere.core.exception.ShardingException;

import org.apache.shardingsphere.core.parse.sql.segment.dml.expr.ExpressionSegment;

import org.apache.shardingsphere.core.parse.sql.segment.dml.expr.simple.LiteralExpressionSegment;

import org.apache.shardingsphere.core.parse.sql.segment.dml.expr.simple.ParameterMarkerExpressionSegment;

/**

OnDuplicateKey value context.

@author sonny.zhao

*/

@getter

@tostring

public final class OnDuplicateKeyValueContext {

private final int parametersCount;

private final List valueExpressions;

private final List parameters;

public OnDuplicateKeyValueContext(final Collection assignments, final List parameters, final int parametersOffset) {

parametersCount = calculateParametersCount(assignments);

valueExpressions = getValueExpressions(assignments);

this.parameters = getParameters(parameters, parametersOffset);

}

private int calculateParametersCount(final Collection assignments) {

int result = 0;

for (ExpressionSegment each : assignments) {

if (each instanceof ParameterMarkerExpressionSegment) {

result++;

}

}

return result;

}

private List getValueExpressions(final Collection assignments) {

List result = new ArrayList<>(assignments.size());

result.addAll(assignments);

return result;

}

private List getParameters(final List parameters, final int parametersOffset) {

if (0 == parametersCount) {

return Collections.emptyList();

}

List result = new ArrayList<>(parametersCount);

result.addAll(parameters.subList(parametersOffset, parametersOffset + parametersCount));

return result;

}

/**

Get value.

@param index index

@return value

*/

public Object getValue(final int index) {

ExpressionSegment valueExpression = valueExpressions.get(index);

return valueExpression instanceof ParameterMarkerExpressionSegment ? parameters.get(getParameterIndex(valueExpression)) : ((LiteralExpressionSegment) valueExpression).getLiterals();

}

private int getParameterIndex(final ExpressionSegment valueExpression) {

int result = 0;

for (ExpressionSegment each : valueExpressions) {

if (valueExpression == each) {

return result;

}

if (each instanceof ParameterMarkerExpressionSegment) {

result++;

}

}

throw new ShardingException("Can not get parameter index.");

}

}

modify 5 files

extractor-rule-definition.xml

<extractor-rule id="onDuplicateKeyValuesColumns" extractor-class="org.apache.shardingsphere.core.parse.core.extractor.impl.dml.insert.OnDuplicateKeyValuesExtractor" />

filler-rule-definition.xml

<filler-rule sql-segment-class="org.apache.shardingsphere.core.parse.sql.segment.dml.assignment.OnDuplicateKeyValuesSegment" filler-class="org.apache.shardingsphere.core.parse.core.filler.impl.dml.OnDuplicateKeyValuesFiller" />

sql-statement-rule-definition.xml

<sql-statement-rule context="insert" sql-statement-class="org.apache.shardingsphere.core.parse.sql.statement.dml.InsertStatement" extractor-rule-refs="table, columns, insertColumns, insertValues, setAssignments, onDuplicateKeyColumns, onDuplicateKeyValuesColumns" />

InsertStatement.java

private final Collection<OnDuplicateKeyValuesSegment> onDuplicateKeyValues = new LinkedList<>();

public List<List<ExpressionSegment>> getAllOnDuplicateKeyValueExpressions() {
return null == setAssignment ? OnDuplicateKeyValueExpressionsFromValues() : Collections.singletonList(getAllOnDuplicateKeyValueExpressionsFromSetAssignment());
}

private List<List<ExpressionSegment>> OnDuplicateKeyValueExpressionsFromValues() {
List<List<ExpressionSegment>> result = new ArrayList<>(onDuplicateKeyValues.size());
for (OnDuplicateKeyValuesSegment each : onDuplicateKeyValues) {
result.add(each.getValues());
}
return result;
}

private List<ExpressionSegment> getAllOnDuplicateKeyValueExpressionsFromSetAssignment() {
List<ExpressionSegment> result = new ArrayList<>(setAssignment.getAssignments().size());
for (AssignmentSegment each : setAssignment.getAssignments()) {
result.add(each.getValue());
}
return result;
}

InsertSQLStatementContext.java

private final List<OnDuplicateKeyValueContext> onDuplicateKeyValueContexts;

public InsertSQLStatementContext(final TableMetas tableMetas, final List<Object> parameters, final InsertStatement sqlStatement) {
super(sqlStatement);
columnNames = sqlStatement.useDefaultColumns() ? tableMetas.getAllColumnNames(getTablesContext().getSingleTableName()) : sqlStatement.getColumnNames();
insertValueContexts = getInsertValueContexts(parameters);
onDuplicateKeyValueContexts = getOnDuplicateKeyValueContexts(parameters); // add this row
}

private List<OnDuplicateKeyValueContext> getOnDuplicateKeyValueContexts(final List<Object> parameters) {
List<OnDuplicateKeyValueContext> result = new LinkedList<>();
int parametersOffset = 0;
for (Collection<ExpressionSegment> each : ((InsertStatement) getSqlStatement()).getAllOnDuplicateKeyValueExpressions()) {
OnDuplicateKeyValueContext onDuplicateKeyValueContext = new OnDuplicateKeyValueContext(each, parameters, parametersOffset);
result.add(onDuplicateKeyValueContext);
parametersOffset += onDuplicateKeyValueContext.getParametersCount();
}
return result;
}

// this look like not grace , but two List not compatibility shardingConditions
public List<List<Object>> getGroupedParameters() {
List<List<Object>> result = new LinkedList<>();
List<Object> parameters = new ArrayList<>();

for (InsertValueContext each : insertValueContexts) {
parameters.addAll(each.getParameters());
}

for (OnDuplicateKeyValueContext each : onDuplicateKeyValueContexts) {
parameters.addAll(each.getParameters());
}
result.add(parameters);
return result;
}

it worked , but not perfect ,please reference

Issue 2:
Which version of ShardingSphere did you use?

master.

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy.

Expected behavior

first time set proxy props take effect.

Actual behavior

not take effect.

Reason analyze (If you can)

Maybe less one event.

Intuitive performance

Proxy configuration

server.yaml:

mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: database_discovery
server-lists: localhost:2181
retryIntervalMilliseconds: 500
timeToLiveSeconds: 60
maxRetries: 3
operationTimeoutMilliseconds: 500
overwrite: true

rules:
- !AUTHORITY
users:
- root@%:root
- sharding@:sharding
provider:
type: ALL_PRIVILEGES_PERMITTED
# - !TRANSACTION
# defaultType: XA
# providerType: Atomikos
# - !SQL_PARSER
# sqlCommentParseEnabled: true
# sqlStatementCache:
# initialCapacity: 2000
# maximumSize: 65535
# concurrencyLevel: 4
# parserTreeCache:
# initialCapacity: 128
# maximumSize: 1024
# concurrencyLevel: 4

#props:
# max-connections-size-per-query: 1
# kernel-executor-size: 16 # Infinite by default.
# proxy-frontend-flush-threshold: 128 # The default value is 128.
# proxy-opentracing-enabled: false
# proxy-hint-enabled: false
# sql-show: true
# check-table-metadata-enabled: false
# show-process-list-enabled: false
# # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
# # The default value is -1, which means set the minimum value for different JDBC drivers.
# proxy-backend-query-fetch-size: -1
# check-duplicate-table-enabled: false
# sql-comment-parse-enabled: false
# proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
# # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
# # if client connections are more than proxy-frontend-netty-executor-size, especially executing slow SQL.
# proxy-backend-executor-suitable: OLAP
# proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
# sql-federation-enabled: false

config-database-discovery.yaml:

schemaName: database_discovery_db

dataSources:
ds_0:
url: jdbc:mysql://127.0.0.1:13306/demo_primary_ds?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_1:
url: jdbc:mysql://127.0.0.1:13307/demo_primary_ds?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
ds_2:
url: jdbc:mysql://127.0.0.1:13308/demo_primary_ds?serverTimezone=UTC&useSSL=false
username: root
password: 123456
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1

rules:
- !READWRITE_SPLITTING
dataSources:
rd_rs:
autoAwareDataSourceName: pr_ds
- !DB_DISCOVERY
dataSources:
pr_ds:
dataSourceNames:
- ds_0
- ds_1
- ds_2
discoveryHeartbeatName: mgr-heartbeat
discoveryTypeName: mgr
discoveryHeartbeats:
mgr-heartbeat:
props:
keep-alive-cron: '0/5 * * * * ?'
discoveryTypes:
mgr:
type: MGR
props:
group-name: b13df29e-90b6-11e8-8d1b-525400fc3996