To terryManu #4946

nevereverever · 2020-03-26T00:49:20Z

Dear terryManu:

I've been using sharding proxy for two months,and try to use it in the project.I reported some questions,some of them were closed before they were solved, and some of them got good feedback.No one will reply to those who have been shut down. I'm here to retell the definite problems I have encountered.Sharding proxy is an excellent open source product, but there are still many problems. I have fixed some bugs in my own project. I hope you can also pay attention to it

（1）I use this sharding rule to split my table,but using ""（Double quotation marks） will route to all table node:
this is the sharding rule:

@Slf4j
public class SimpleHashShardingAlgorithm implements PreciseShardingAlgorithm<String> {
    @Override
    public String doSharding(final Collection<String> availableTargetNames, final PreciseShardingValue<String> shardingValue) {
        String columnName = shardingValue.getColumnName();
        String tableName = shardingValue.getLogicTableName();
        String value = shardingValue.getValue();
        int shardingCount = availableTargetNames.size();
        int hashNum = ShardingUtils.hash(value, shardingCount);
        Iterator it = availableTargetNames.iterator();
        String targetName;
        do {
            if (!it.hasNext()) {
                throw new UnsupportedOperationException();
            }
            targetName = (String) it.next();
        } while (!targetName.endsWith(hashNum + ""));
        log.debug("simpleHashShardingAlgorithm--shardingCount:{},columnName:{},tableName:{},columnValue:{},recent return target is : {}",
                new Object[]{shardingCount, columnName, tableName, value, targetName});
        return targetName;
    }

(2)preparedstatement mode can greatly enhance performance, but there is a big bug when using multithreading has thread problems.Because of using English, I can't describe this problem very well,I fixed this bug by using ThreadLocal in MySQLBinaryStatement. I'm sure you'll see what I mean.
This is my code:

@RequiredArgsConstructor
@Getter
@Setter
public final class MySQLBinaryStatement {
    
    private final String sql;
    
    private final int parametersCount;
    
    //private List<MySQLBinaryStatementParameterType> parameterTypes;
    ThreadLocal<List<MySQLBinaryStatementParameterType>> mapCurrentParameterTypes = new ThreadLocal<>();

    public List<MySQLBinaryStatementParameterType> getParameterTypes() {
        return mapCurrentParameterTypes.get();
    }

    public void setParameterTypes(List<MySQLBinaryStatementParameterType> parameterTypes) {
        mapCurrentParameterTypes.set(parameterTypes);
    }
}

(3)And,when using preparedstatement in jdbc,if the sql is large enough,parameter will be cutted lead to 'java.sql.SQLException: Parameter index out of bounds. 22465 is not between valid values of 1 and 22464'.I debugger it,find preparedstatement returns different with single Mysql,parameterCount is wrong.
you sincerely
YoungLu

The text was updated successfully, but these errors were encountered:

nevereverever · 2020-03-26T00:51:08Z

@terrymanu

kimmking · 2020-03-26T01:18:46Z

Hi, @nevereverever thx for your attention.

What this log actually print:

log.debug("simpleHashShardingAlgorithm--shardingCount:{},columnName:{},tableName:{},columnValue:{},recent return target is : {}",
new Object[]{shardingCount, columnName, tableName, value, targetName});

Can you make a little demo to reproduce this bug?
This message isn't thrown by ShardingSphere, actually by mysql. You know, mostly your codes have some implicit bugs.

nevereverever · 2020-03-26T02:00:18Z

@kimmking

1.when using '' ,it print:

 [DEBUG] 09:37:18.846 [pool-7-thread-1] c.p.p.s.r.s.SimpleHashShardingAlgorithm - simpleHashShardingAlgorithm--shardingCount:2,columnName:personid,tableName:tbperson,columnValue:1,recent return target is : ds_1

if using "",this log will not print,result in mysql command line:

INSERT INTO `sharding_db`.`tbperson`(`personId`, `name`, `lastUpdateTime`, `account`) VALUES ("2", "oho", "2020-02-12 20:47:10", 3.00)
> Affected rows: 2
> 时间: 0.023s

2.yes.But my company cannot upload pictures due to firewall.
In my client,it reports

Error updating database.  Cause: java.sql.SQLException: 2Unknown exception: [null]

I find the nullException in this code:
MySQLComStmtExecutePacket.java

private List<Object> getParameters(final MySQLPacketPayload payload, final int parametersCount) throws SQLException {
        List<Object> result = new ArrayList<>(parametersCount);
        for (int parameterIndex = 0; parameterIndex < parametersCount; parameterIndex++) {
            MySQLBinaryProtocolValue binaryProtocolValue = MySQLBinaryProtocolValueFactory.getBinaryProtocolValue(binaryStatement.getParameterTypes().get(parameterIndex).getColumnType());
            result.add(nullBitmap.isNullParameter(parameterIndex) ? null : binaryProtocolValue.read(payload));
        }
        return result;
    }

binaryProtocolValue.read(payload)

3.this message is throw by my code,I know it,but preparedstatement is build by shardingsphere.
My table has 44 columns,if use insert... values(?,?),(?,?),(?,?),(?,?),(?,?)...,(?,?) to much(? more than 65535) will throw index out of bounds.
I'll point out the differences for you：

single Mysql:

ps = {JDBC42PreparedStatement@2402} "com.mysql.jdbc.JDBC42PreparedStatement@4c88d27e: replace into psn_insu_rlts_d_bak (ADMDVS_NAME,CLCT_WAY,CRT_INSU_DATE,OPTINS,EMP_FOM,INSU_OPTINS,HI_TYPE,CLCT_RULE_TYPE_CODG,CRTE_OPTINS,CLCTSTD_CRTF_RULE_CODG,INSUTYPE_RETR_FLAG,OPT_TIME,INSUTYPE,AS_CLCT_MONTH,UPDT_TIME,PAUS_INSU_DATE,EMP_NO,CRTER_NAME,PSN_INSU_STAS,ACCT_CRTN_YM,CRT_PATC_JOB_DATE,INSU_PSN_MGT_EID,CERTNO,FST_INSU_YM,OPTER_NAME,CRTER,QUTS_TYPE,PSN_INSU_RLTS_ID,FM_ID,PSN_CERT_TYPE,BEGN_YM,POOLAREA,INSU_IDET,PSN_NO,PSN_TYPE,END_YM,PSN_CLCT_STAS,MAX_ACCTPRD,THISTIME_BEGN_DATE,PSN_INSU_DATE,SYS_CODE,CRTE_TIME,ADMDVS,OPTER) values (** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** NOT SPECIFIED **,** "
 batchHasPlainStatements = false
 dbmd = {JDBC4DatabaseMetaData@2406} 
 firstCharOfStmt = 'R' 82
 isLoadDataQuery = false
 isNull = {boolean[88000]@2407} 
 isStream = {boolean[88000]@2408} 
 numberOfExecutions = 0
 originalSql = "replace into psn_insu_rlts_d_bak (ADMDVS_NAME,CLCT_WAY,CRT_INSU_DATE,OPTINS,EMP_FOM,INSU_OPTINS,HI_TYPE,CLCT_RULE_TYPE_CODG,CRTE_OPTINS,CLCTSTD_CRTF_RULE_CODG,INSUTYPE_RETR_FLAG,OPT_TIME,INSUTYPE,AS_CLCT_MONTH,UPDT_TIME,PAUS_INSU_DATE,EMP_NO,CRTER_NAME,PSN_INSU_STAS,ACCT_CRTN_YM,CRT_PATC_JOB_DATE,INSU_PSN_MGT_EID,CERTNO,FST_INSU_YM,OPTER_NAME,CRTER,QUTS_TYPE,PSN_INSU_RLTS_ID,FM_ID,PSN_CERT_TYPE,BEGN_YM,POOLAREA,INSU_IDET,PSN_NO,PSN_TYPE,END_YM,PSN_CLCT_STAS,MAX_ACCTPRD,THISTIME_BEGN_DATE,PSN_INSU_DATE,SYS_CODE,CRTE_TIME,ADMDVS,OPTER) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),(?,"
 parameterCount = 88000
 parameterMetaData = null
 parameterStreams = {InputStream[88000]@2409} 
 parameterValues = {byte[88000][]@2411} 
 parameterTypes = {int[88000]@2413} 
 parseInfo = {PreparedStatement$ParseInfo@2414} 
 pstmtResultMetaData = null
 staticSqlStrings = {byte[88001][]@2415} 
 streamConvertBuf = null
 streamLengths = {int[88000]@2416} 
 tsdf = null
 ddf = null
 tdf = null
 useTrueBoolean = true
 usingAnsiMode = false
 batchedValuesClause = null
 doPingInstead = false
 compensateForOnDuplicateKeyUpdate = false
 charsetEncoder = null
 batchCommandIndex = -1
 serverSupportsFracSecs = true
 rewrittenBatchSize = 0
 cancelTimeoutMutex = {Object@2417} 
 wasCancelled = false
 wasCancelledByTimeout = false
 batchedArgs = null
 charConverter = null
 charEncoding = "UTF-8"
 connection = {JDBC4Connection@2385} 
 physicalConnection = null
 connectionId = 84151
 currentCatalog = "db02"
 doEscapeProcessing = true
 eventSink = null
 fetchSize = 0
 isClosed = false
 lastInsertId = -1
 maxFieldSize = 67108864
 maxRows = -1
 openResults = {HashSet@2420}  size = 0
 pedantic = false
 pointOfOrigin = null
 profileSQL = false
 results = null
 generatedKeysResults = null
 resultSetConcurrency = 1007
 resultSetType = 1003
 statementId = 0
 timeoutInMillis = 0
 updateCount = -1
 useUsageAdvisor = false
 warningChain = null
 clearWarningsCalled = false
 holdResultsOpenOverClose = false
 batchedGeneratedKeys = null
 retrieveGeneratedKeys = false
 continueBatchOnError = true
 pingTarget = null
 useLegacyDatetimeCode = true
 sendFractionalSeconds = true
 exceptionInterceptor = null
 lastQueryIsOnDupKeyUpdate = false
 statementExecuting = {AtomicBoolean@2421} "false"
 isImplicitlyClosingResults = false
 originalResultSetType = 0
 originalFetchSize = 0
 isPoolable = true
 localInfileInputStream = null
 version5013OrNewer = true
 closeOnCompletion = false

sharding-proxy:

ps = {JDBC42ServerPreparedStatement@2398} "com.mysql.jdbc.ServerPreparedStatement[1] - replace into psn_insu_rlts_d_bak (ADMDVS_NAME,CLCT_WAY,CRT_INSU_DATE,OPTINS,EMP_FOM,INSU_OPTINS,HI_TYPE,CLCT_RULE_TYPE_CODG,CRTE_OPTINS,CLCTSTD_CRTF_RULE_CODG,INSUTYPE_RETR_FLAG,OPT_TIME,INSUTYPE,AS_CLCT_MONTH,UPDT_TIME,PAUS_INSU_DATE,EMP_NO,CRTER_NAME,PSN_INSU_STAS,ACCT_CRTN_YM,CRT_PATC_JOB_DATE,INSU_PSN_MGT_EID,CERTNO,FST_INSU_YM,OPTER_NAME,CRTER,QUTS_TYPE,PSN_INSU_RLTS_ID,FM_ID,PSN_CERT_TYPE,BEGN_YM,POOLAREA,INSU_IDET,PSN_NO,PSN_TYPE,END_YM,PSN_CLCT_STAS,MAX_ACCTPRD,THISTIME_BEGN_DATE,PSN_INSU_DATE,SYS_CODE,CRTE_TIME,ADMDVS,OPTER) values (null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null),(null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,n"
 hasOnDuplicateKeyUpdate = false
 detectedLongParameterSwitch = false
 fieldCount = 0
 invalid = false
 invalidationException = null
 outByteBuffer = null
 parameterBindings = {ServerPreparedStatement$BindValue[22464]@2404} 
 parameterFields = {Field[22464]@2406} 
 resultFields = null
 sendTypesToServer = false
 serverStatementId = 1
 stringTypeCode = 253
 serverNeedsResetBeforeEachExecution = false
 isCached = false
 useAutoSlowLog = true
 serverTzCalendar = null
 defaultTzCalendar = null
 hasCheckedRewrite = false
 canRewrite = false
 locationOfOnDuplicateKeyUpdate = -2
 batchHasPlainStatements = false
 dbmd = null
 firstCharOfStmt = 'R' 82
 isLoadDataQuery = false
 isNull = null
 isStream = null
 numberOfExecutions = 0
 originalSql = "replace into psn_insu_rlts_d_bak (ADMDVS_NAME,CLCT_WAY,CRT_INSU_DATE,OPTINS,EMP_FOM,INSU_OPTINS,HI_TYPE,CLCT_RULE_TYPE_CODG,CRTE_OPTINS,CLCTSTD_CRTF_RULE_CODG,INSUTYPE_RETR_FLAG,OPT_TIME,INSUTYPE,AS_CLCT_MONTH,UPDT_TIME,PAUS_INSU_DATE,EMP_NO,CRTER_NAME,PSN_INSU_STAS,ACCT_CRTN_YM,CRT_PATC_JOB_DATE,INSU_PSN_MGT_EID,CERTNO,FST_INSU_YM,OPTER_NAME,CRTER,QUTS_TYPE,PSN_INSU_RLTS_ID,FM_ID,PSN_CERT_TYPE,BEGN_YM,POOLAREA,INSU_IDET,PSN_NO,PSN_TYPE,END_YM,PSN_CLCT_STAS,MAX_ACCTPRD,THISTIME_BEGN_DATE,PSN_INSU_DATE,SYS_CODE,CRTE_TIME,ADMDVS,OPTER) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?),(?,"
 parameterCount = 22464
 parameterMetaData = null
 parameterStreams = null
 parameterValues = null
 parameterTypes = {int[22464]@2408} 
 parseInfo = null
 pstmtResultMetaData = null
 staticSqlStrings = null
 streamConvertBuf = null
 streamLengths = null
 tsdf = null
 ddf = null
 tdf = null
 useTrueBoolean = true
 usingAnsiMode = false
 batchedValuesClause = null
 doPingInstead = false
 compensateForOnDuplicateKeyUpdate = false
 charsetEncoder = null
 batchCommandIndex = -1
 serverSupportsFracSecs = true
 rewrittenBatchSize = 0
 cancelTimeoutMutex = {Object@2409} 
 wasCancelled = false
 wasCancelledByTimeout = false
 batchedArgs = null
 charConverter = null
 charEncoding = "UTF-8"
 connection = {JDBC4Connection@2387} 
 physicalConnection = null
 connectionId = 2
 currentCatalog = "sharding_db"
 doEscapeProcessing = true
 eventSink = null
 fetchSize = 0
 isClosed = false
 lastInsertId = -1
 maxFieldSize = 67108864
 maxRows = -1
 openResults = {HashSet@2412}  size = 0
 pedantic = false
 pointOfOrigin = null
 profileSQL = false
 results = null
 generatedKeysResults = null
 resultSetConcurrency = 1007
 resultSetType = 1003
 statementId = 0
 timeoutInMillis = 0
 updateCount = -1
 useUsageAdvisor = false
 warningChain = null
 clearWarningsCalled = false
 holdResultsOpenOverClose = false
 batchedGeneratedKeys = null
 retrieveGeneratedKeys = false
 continueBatchOnError = true
 pingTarget = null
 useLegacyDatetimeCode = true
 sendFractionalSeconds = true
 exceptionInterceptor = null
 lastQueryIsOnDupKeyUpdate = false
 statementExecuting = {AtomicBoolean@2413} "false"
 isImplicitlyClosingResults = false
 originalResultSetType = 0
 originalFetchSize = 0
 isPoolable = true
 localInfileInputStream = null
 version5013OrNewer = true
 closeOnCompletion = false

this parameterparameterCount = 88000(single MySQL), parameterCount = 22464(sharding-proxy)

nevereverever · 2020-03-26T02:05:56Z

By the way,I know using long values is not a good way to use it in mysql,I tend to use batch,but this is to restore mybatis foreach.

terrymanu · 2020-03-26T03:27:15Z

Thank you the feedback. It is better to send mailing list for this issue.
I think we have already provide the reason before issue closed, and we can discuss details for opened issue.
Some issues we closed because no necessary information provided.
Can we close this issue and talk details in each issue?

It is better to aggregate the information in same issue, separate into serval issues are difficult to search by users.

terrymanu · 2020-03-26T03:33:08Z

The title of the issue is not user friendly and maybe no value for other users.
This project is not my personal project, we just face to all users and contributors.

So I just close this issue, please add more necessary information in the suitable issues.
Thank you very much.

terrymanu added the type: discussion label Mar 26, 2020

terrymanu closed this as completed Mar 26, 2020

terrymanu added status: invalid and removed type: discussion labels Mar 26, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

To terryManu #4946

To terryManu #4946

nevereverever commented Mar 26, 2020 •

edited

nevereverever commented Mar 26, 2020

kimmking commented Mar 26, 2020

nevereverever commented Mar 26, 2020

nevereverever commented Mar 26, 2020

terrymanu commented Mar 26, 2020 •

edited

terrymanu commented Mar 26, 2020

To terryManu #4946

To terryManu #4946

Comments

nevereverever commented Mar 26, 2020 • edited

nevereverever commented Mar 26, 2020

kimmking commented Mar 26, 2020

nevereverever commented Mar 26, 2020

nevereverever commented Mar 26, 2020

terrymanu commented Mar 26, 2020 • edited

terrymanu commented Mar 26, 2020

nevereverever commented Mar 26, 2020 •

edited

terrymanu commented Mar 26, 2020 •

edited