You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/03/26 00:49:21 UTC
[GitHub] [incubator-shardingsphere] nevereverever opened a new issue #4946:
To terryManu
nevereverever opened a new issue #4946: To terryManu
URL: https://github.com/apache/incubator-shardingsphere/issues/4946
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:
```java
@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:
```java
@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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu closed issue #4946: To
terryManu
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #4946: To terryManu
URL: https://github.com/apache/incubator-shardingsphere/issues/4946
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking commented on issue #4946: To
terryManu
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4946: To terryManu
URL: https://github.com/apache/incubator-shardingsphere/issues/4946#issuecomment-604173268
Hi, @nevereverever thx for your attention.
1. What this log actually print:
> log.debug("simpleHashShardingAlgorithm--shardingCount:{},columnName:{},tableName:{},columnValue:{},recent return target is : {}",
new Object[]{shardingCount, columnName, tableName, value, targetName});
2. Can you make a little demo to reproduce this bug?
3. This message isn't thrown by ShardingSphere, actually by mysql. You know, mostly your codes have some implicit bugs.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] nevereverever commented on issue #4946:
To terryManu
Posted by GitBox <gi...@apache.org>.
nevereverever commented on issue #4946: To terryManu
URL: https://github.com/apache/incubator-shardingsphere/issues/4946#issuecomment-604165480
@terrymanu
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu edited a comment on issue
#4946: To terryManu
Posted by GitBox <gi...@apache.org>.
terrymanu edited a comment on issue #4946: To terryManu
URL: https://github.com/apache/incubator-shardingsphere/issues/4946#issuecomment-604207517
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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] nevereverever commented on issue #4946:
To terryManu
Posted by GitBox <gi...@apache.org>.
nevereverever commented on issue #4946: To terryManu
URL: https://github.com/apache/incubator-shardingsphere/issues/4946#issuecomment-604184246
@kimmking
1.when using '' ,it print:
```java
[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:
```bash
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
```java
Error updating database. Cause: java.sql.SQLException: 2Unknown exception: [null]
```
I find the nullException in this code:
`MySQLComStmtExecutePacket.java`
```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:
```java
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:
```java
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 parameter`parameterCount = 88000`(single MySQL), `parameterCount = 22464`(sharding-proxy)
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #4946: To
terryManu
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #4946: To terryManu
URL: https://github.com/apache/incubator-shardingsphere/issues/4946#issuecomment-604209000
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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu commented on issue #4946: To
terryManu
Posted by GitBox <gi...@apache.org>.
terrymanu commented on issue #4946: To terryManu
URL: https://github.com/apache/incubator-shardingsphere/issues/4946#issuecomment-604207517
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?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] nevereverever commented on issue #4946:
To terryManu
Posted by GitBox <gi...@apache.org>.
nevereverever commented on issue #4946: To terryManu
URL: https://github.com/apache/incubator-shardingsphere/issues/4946#issuecomment-604185702
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`.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services