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 2022/05/19 09:55:01 UTC
[GitHub] [shardingsphere] TeslaCN opened a new issue, #17806: PostgreSQL Scaling incremental task failed
TeslaCN opened a new issue, #17806:
URL: https://github.com/apache/shardingsphere/issues/17806
## Bug Report
### Which version of ShardingSphere did you use?
master - https://github.com/apache/shardingsphere/tree/a393962f5b3eec2d154ad1c2c5d15a758efc499b
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
Scaling works well.
### Actual behavior
```
[ERROR] 2022-05-19 17:35:46.888 [ShardingSphere-Scaling-Incremental-0130317c30317c3054317c626d73716c5f7368617264696e67-2] o.a.s.d.p.c.i.AbstractImporter - flush failed 3/3 times.
java.sql.BatchUpdateException: Batch entry 0 INSERT INTO "public"."bmsql_item_3"("i_id","i_name") VALUES(999999, 'ull i_price[numeric]:null i_data[character varying]:null i_im_id[integer]:null') ON CONFLICT (i_id) DO UPDATE SET "i_name"=EXCLUDED."i_name" was aborted: ERROR: value too long for type character varying(24) Call getNextException to see other errors in the batch.
at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
at org.postgresql.core.ResultHandlerDelegate.handleError(ResultHandlerDelegate.java:52)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:559)
at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:887)
at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910)
at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1638)
at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:128)
at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
at org.apache.shardingsphere.driver.executor.batch.BatchPreparedStatementExecutor$1.executeSQL(BatchPreparedStatementExecutor.java:141)
at org.apache.shardingsphere.driver.executor.batch.BatchPreparedStatementExecutor$1.executeSQL(BatchPreparedStatementExecutor.java:137)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:84)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:64)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:135)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:121)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:115)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:65)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:49)
at org.apache.shardingsphere.driver.executor.batch.BatchPreparedStatementExecutor.executeBatch(BatchPreparedStatementExecutor.java:150)
at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.executeBatch(ShardingSpherePreparedStatement.java:560)
at org.apache.shardingsphere.data.pipeline.core.importer.AbstractImporter.executeBatchInsert(AbstractImporter.java:166)
at org.apache.shardingsphere.data.pipeline.core.importer.AbstractImporter.doFlush(AbstractImporter.java:140)
at org.apache.shardingsphere.data.pipeline.core.importer.AbstractImporter.tryFlush(AbstractImporter.java:125)
at org.apache.shardingsphere.data.pipeline.core.importer.AbstractImporter.flushInternal(AbstractImporter.java:116)
at org.apache.shardingsphere.data.pipeline.core.importer.AbstractImporter.lambda$flush$2(AbstractImporter.java:107)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
at org.apache.shardingsphere.data.pipeline.core.importer.AbstractImporter.flush(AbstractImporter.java:105)
at org.apache.shardingsphere.data.pipeline.core.importer.AbstractImporter.write(AbstractImporter.java:88)
at org.apache.shardingsphere.data.pipeline.core.importer.AbstractImporter.doStart(AbstractImporter.java:74)
at org.apache.shardingsphere.data.pipeline.api.executor.AbstractLifecycleExecutor.start(AbstractLifecycleExecutor.java:41)
at org.apache.shardingsphere.data.pipeline.api.executor.AbstractLifecycleExecutor.run(AbstractLifecycleExecutor.java:61)
at java.base/java.util.concurrent.CompletableFuture$AsyncRun.run(CompletableFuture.java:1804)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: org.postgresql.util.PSQLException: ERROR: value too long for type character varying(24)
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
... 32 common frames omitted
```
Data consistency check failed.
```
[INFO ] 2022-05-19 17:36:50.048 [_finished_check_Worker-1] o.a.s.d.p.core.job.FinishedCheckJob - scaling job 0130317c30317c3054317c626d73716c5f7368617264696e67 almost finished.
[INFO ] 2022-05-19 17:36:50.048 [_finished_check_Worker-1] o.a.s.d.p.s.r.s.DefaultSourceWritingStopLock - lock, databaseName=bmsql_sharding, jobId=0130317c30317c3054317c626d73716c5f7368617264696e67
[INFO ] 2022-05-19 17:36:50.061 [_finished_check_Worker-1] o.a.s.d.p.c.a.i.RuleAlteredJobAPIImpl - stopClusterWriteDB, tryLockSuccess=true
[INFO ] 2022-05-19 17:36:50.063 [_finished_check_Worker-1] o.a.s.d.p.core.job.FinishedCheckJob - dataConsistencyCheck for job 0130317c30317c3054317c626d73716c5f7368617264696e67
[INFO ] 2022-05-19 17:36:50.064 [_finished_check_Worker-1] o.a.s.d.p.a.c.TableNameSchemaNameMapping - mapping={pg_trigger=pg_catalog, bmsql_district=public, bmsql_oorder=public, bmsql_stock=public, columns=information_schema, pg_class=pg_catalog, pg_tablespace=pg_catalog, pg_database=pg_catalog, bmsql_customer=public, bmsql_item=public, tables=information_schema, bmsql_new_order=public, bmsql_history=public, bmsql_warehouse=public, pg_inherits=pg_catalog, bmsql_order_line=public, views=information_schema, bmsql_config=public}
[INFO ] 2022-05-19 17:36:50.329 [_finished_check_Worker-1] o.a.s.d.p.c.a.i.RuleAlteredJobAPIImpl - Scaling job 0130317c30317c3054317c626d73716c5f7368617264696e67 with check algorithm 'DATA_MATCH' data consistency checker result {bmsql_item=DataConsistencyCheckResult(countCheckResult=DataConsistencyCountCheckResult(sourceRecordsCount=100001, targetRecordsCount=100000, matched=false), contentCheckResult=DataConsistencyContentCheckResult(matched=false))}
[ERROR] 2022-05-19 17:36:50.329 [_finished_check_Worker-1] o.a.s.d.p.c.a.i.RuleAlteredJobAPIImpl - Scaling job: 0130317c30317c3054317c626d73716c5f7368617264696e67, table: bmsql_item data consistency check failed, count matched: false, content matched: false
[INFO ] 2022-05-19 17:36:50.329 [_finished_check_Worker-1] o.a.s.d.p.c.a.i.GovernanceRepositoryAPIImpl - persist job check result 'false' for job 0130317c30317c3054317c626d73716c5f7368617264696e67
[ERROR] 2022-05-19 17:36:50.338 [_finished_check_Worker-1] o.a.s.d.p.c.a.i.RuleAlteredJobAPIImpl - Scaling job: 0130317c30317c3054317c626d73716c5f7368617264696e67, table: bmsql_item data consistency check failed, count matched: false, content matched: false
[ERROR] 2022-05-19 17:36:50.338 [_finished_check_Worker-1] o.a.s.d.p.core.job.FinishedCheckJob - data consistency check failed, job 0130317c30317c3054317c626d73716c5f7368617264696e67
[INFO ] 2022-05-19 17:36:50.338 [_finished_check_Worker-1] o.a.s.d.p.s.r.s.DefaultSourceWritingStopLock - releaseLock, databaseName=bmsql_sharding, jobId=0130317c30317c3054317c626d73716c5f7368617264696e67
[INFO ] 2022-05-19 17:36:50.339 [_finished_check_Worker-1] o.a.s.d.p.c.a.i.RuleAlteredJobAPIImpl - restoreClusterWriteDB, before releaseLock, databaseName=bmsql_sharding, jobId=0130317c30317c3054317c626d73716c5f7368617264696e67
```
Full logs could be found here: https://paste.ubuntu.com/p/wFpnZcgYym/
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
#### 1. Prepare ShardingSphere-Proxy
config-sharding.yaml
```yaml
schemaName: bmsql_sharding
dataSources:
ds_0:
url: jdbc:postgresql://127.0.0.1:25432/bmsql_0
username: postgres
password: postgres
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 32
minPoolSize: 0
ds_1:
url: jdbc:postgresql://127.0.0.1:25432/bmsql_1
username: postgres
password: postgres
connectionTimeoutMilliseconds: 3000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 32
minPoolSize: 0
rules:
- !SHARDING
defaultDatabaseStrategy:
none:
defaultTableStrategy:
none:
keyGenerators:
autoTables:
bmsql_item:
actualDataSources: ds_${0..1}
shardingStrategy:
standard:
shardingColumn: i_id
shardingAlgorithmName: mod_4
shardingAlgorithms:
mod_4:
type: MOD
props:
sharding-count: 4
scalingName: bmsql_scaling
scaling:
bmsql_scaling:
completionDetector:
props:
incremental-task-idle-seconds-threshold: '60'
type: IDLE
dataConsistencyChecker:
props:
chunk-size: '1000'
type: DATA_MATCH
```
#### 2. Prepare inventory data
```sql
create table bmsql_item (
i_id integer not null,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id integer
);
alter table bmsql_item add constraint bmsql_item_pkey
primary key (i_id);
-- Insert 100000 rows into table.
```
#### 3. Add data sources and alter rule to trigger Scaling
```sql
ADD RESOURCE target_0 (
URL="jdbc:postgresql://127.0.0.1:35432/bmsql_0",
USER=postgres,
PASSWORD= postgres,
PROPERTIES("maximumPoolSize"=32, "minimumIdle"=0,"idleTimeout"="60000")
), target_1 (
URL="jdbc:postgresql://127.0.0.1:35432/bmsql_1",
USER= postgres,
PASSWORD= postgres,
PROPERTIES("maximumPoolSize"=32, "minimumIdle"=0,"idleTimeout"="60000")
), target_2 (
URL="jdbc:postgresql://127.0.0.1:35432/bmsql_2",
USER= postgres,
PASSWORD= postgres,
PROPERTIES("maximumPoolSize"=32, "minimumIdle"=0,"idleTimeout"="60000")
);
ALTER SHARDING TABLE RULE bmsql_item(
RESOURCES(target_0, target_1, target_2),
SHARDING_COLUMN=i_id,
TYPE(NAME=MOD,PROPERTIES("sharding-count"=12))
);
```
#### 4. Do insert before Scaling switching to new rules
```sql
insert into bmsql_item (i_id) values (999999);
```
Then error occurred.
--
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] sandynz closed issue #17806: PostgreSQL Scaling incremental task failed
Posted by GitBox <gi...@apache.org>.
sandynz closed issue #17806: PostgreSQL Scaling incremental task failed
URL: https://github.com/apache/shardingsphere/issues/17806
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #17806: PostgreSQL Scaling incremental task failed
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #17806:
URL: https://github.com/apache/shardingsphere/issues/17806#issuecomment-1140769862
I found the `null` is not handled properly.
<img width="1044" alt="image" src="https://user-images.githubusercontent.com/20503072/170934057-92fb4e19-68a6-4d25-b723-835c9871263a.png">
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org