wmfxly opened a new issue, #32033:
URL: https://github.com/apache/shardingsphere/issues/32033
Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy 5.5.0
Expected behavior
I want to migrate data from the original database to the target database for
table operation,
according to the creation time create_time table, 3 tables per month.
table algorithm
public class DateYYYYMMThreeTablePreciseAlgorithm implements
StandardShardingAlgorithm<Date> {
private final Logger log =
LoggerFactory.getLogger(DateYYYYMMThreeTablePreciseAlgorithm.class);
private Properties props=new Properties();
@Override
public String doSharding(Collection<String> availableTargetNames,
PreciseShardingValue<Date> shardingValue) {
String suffix = "";
try {
suffix = parseOne(shardingValue.getValue());
} catch (Exception e) {
log.error("Rana{}",shardingValue.getValue());
throw new RuntimeException("querey time error [" +
shardingValue.getValue() + "]!");
}
for (String each : availableTargetNames) {
if (each.endsWith("_" + suffix)) {
return each;
}
}
throw new UnsupportedOperationException();
}
@Override
public Collection<String> doSharding(Collection<String>
availableTargetNames, RangeShardingValue<Date> shardingValue) {
Collection<String> result = new LinkedHashSet<>();
Range<Date> ranges = shardingValue.getValueRange();
try {
String tablePrefix = "";
for (String each : availableTargetNames) {
tablePrefix = each.substring(0, each.lastIndexOf("_"));
break;
}
String startDay = "";
if (ranges.hasLowerBound()) {
startDay = ranges.lowerEndpoint().toString().substring(0,
10);
} else {
startDay = parseStartDay(availableTargetNames);
}
String endDay = "";
if (ranges.hasUpperBound()) {
endDay = ranges.upperEndpoint().toString().substring(0, 10);
} else {
endDay = parseLastDay(availableTargetNames);
}
Date startTime = DateUtils.parseDate(startDay, "yyyy-MM-dd");
Date endTime = DateUtils.parseDate(endDay, "yyyy-MM-dd");
Calendar cal = Calendar.getInstance();
while (startTime.getTime() <= endTime.getTime()) {
result.add(tablePrefix + "_" + parseOne(startTime));
// set start time
cal.setTime(startTime);
cal.add(Calendar.DATE, 10);
startTime = cal.getTime();
}
String endResult = tablePrefix + "_" + parseOne(endTime);
if (!result.contains(endResult)) {
result.add(endResult);
}
} catch (ParseException e) {
log.error("doSharding ParseException", e);
}
return result;
}
/**
* 0-9 store table 0，10-19 store table 1，20-31 store table 12
*
* @param date
* @return
*/
private static String parseOne(Date date) {
String tableName = "";
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyyMM");
tableName += sdf1.format(date);
SimpleDateFormat sdf2 = new SimpleDateFormat("dd");
int day = Integer.parseInt(sdf2.format(date));
if (0 < day && day < 10) {
tableName += "0";
} else if (10 <= day && day < 20) {
tableName += "1";
} else if (20 <= day && day <= 31) {
tableName += "2";
} else {
throw new RuntimeException("query time[" + new
SimpleDateFormat("yyyyMMddHHmmss").format(date) + "]no config table!");
}
return tableName;
}
private String parseStartDay(Collection<String> availableTargetNames) {
String startDay = "";
for (String each : availableTargetNames) {
String suffix = each.substring(each.lastIndexOf("_") + 1);
startDay = suffix.substring(0, 4) + "-" + suffix.substring(4, 6)
+ "-01";
break;
}
return startDay;
}
private String parseLastDay(Collection<String> availableTargetNames) {
String lastDay = "";
for (String each : availableTargetNames) {
String suffix = each.substring(each.lastIndexOf("_") + 1);
lastDay = suffix.substring(0, 4) + "-" + suffix.substring(4, 6)
+ "-31";
}
return lastDay;
}
@Override
public String getType() {
return "DATEYYYYMM_THREE_MOD";
}
}
MIGRATION tabel data success.
Actual behavior
Caused by: java.sql.SQLException: Can not get 36 connections one time,
partition succeed connection(10) have released. Please consider increasing the
'maxPoolSize' of the data sources or decreasing the
'max-connections-size-per-query' in properties.
More details: HikariPool-5 - Connection is not available, request timed out
after 30009ms.
at
org.apache.shardingsphere.infra.exception.core.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:76)
at
org.apache.shardingsphere.driver.jdbc.core.connection.DriverDatabaseConnectionManager.createConnections(DriverDatabaseConnectionManager.java:409)
at
org.apache.shardingsphere.driver.jdbc.core.connection.DriverDatabaseConnectionManager.createConnections(DriverDatabaseConnectionManager.java:393)
at
org.apache.shardingsphere.driver.jdbc.core.connection.DriverDatabaseConnectionManager.getConnections(
Reason analyze (If you can)
'maxPoolSize' of the data sources or decreasing the
'max-connections-size-per-query' config invalidate
max-connections-size-per-query default value 1
Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
-- MIGRATION SOURCE STORAGE
REGISTER MIGRATION SOURCE STORAGE UNIT yss_source_common_db (
URL="jdbc:mysql://ip:3306/xxx_common?serverTimezone=UTC&useSSL=false",
USER="user",
PASSWORD="user",
PROPERTIES("minPoolSize"="5","maxPoolSize"="50","idleTimeout"="60000")
);
-- add STORAGE UNIT
REGISTER STORAGE UNIT ds_0 (
URL="jdbc:mysql://ip:3306/proxy_demo_ds_0?useSSL=false",
USER="root",
PASSWORD="mysql",
PROPERTIES("maxPoolSize"=200,"idleTimeout"="30000","minPoolSize"=10,"maxLifetime"="1800000","connectionTimeout"="30000")
);
-- add sharding rule
CREATE SHARDING TABLE RULE app_notice_ind (
DATANODES(ds_0.app_notice_ind_$->{202401..202412}$->{0..2}),
TABLE_STRATEGY(TYPE="STANDARD",SHARDING_COLUMN=create_time,
SHARDING_ALGORITHM(TYPE(NAME="DATEYYYYMM_THREE_MOD",
PROPERTIES("algorithmClassName"="com.xxxxx.extend.proxy.algorithm.DateYYYYMMThreeTablePreciseAlgorithm",
"algorithm-expression"="app_notice_ind_$->{202401..202412}$->{0..2}")))),
AUDIT_STRATEGY (TYPE(NAME="DML_SHARDING_CONDITIONS"),ALLOW_HINT_DISABLE=true)
);
MIGRATE TABLE xxx.app_notice_ind INTO app_notice_ind;
Any help would be greatly appreciated.Thanks .
--
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.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
