apache / apache/shardingsphere

Error exception when migration with PG 15

Open
#26,203 1 comment 0 reactions 0 assignees View on GitHub
db: PostgreSQL feature: migration status: volunteer wanted
Dominant language
Java
Stars
20.8k
Forks
6.9k
Avg merge
9h 55m
Merged PRs (30d)
311

Description

## Bug Report

### Which version of ShardingSphere did you use?
master
35856435d1b10f9c1d388b3040c4c8d8005616e2
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
proxy
### Expected behavior

migration successfully

### Actual behavior
migration failed

```
[ERROR] 2023-06-02 16:02:04.136 [j0102p00002c60d901819e54f4b5361c5781d943ca_Worker-1] o.a.s.d.p.c.job.AbstractPipelineJob - job prepare failed, j0102p00002c60d901819e54f4b5361c5781d943ca-0
org.apache.shardingsphere.infra.util.exception.external.sql.type.wrapper.SQLWrapperException: ERROR: column "datlastsysoid" does not exist
Position: 21
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.AbstractPostgreSQLDDLAdapter.executeByTemplateForSingleRow(AbstractPostgreSQLDDLAdapter.java:83)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLTablePropertiesLoader.fetchDataBaseId(PostgreSQLTablePropertiesLoader.java:60)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLTablePropertiesLoader.load(PostgreSQLTablePropertiesLoader.java:50)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLCreateTableSQLGenerator.loadMaterials(PostgreSQLCreateTableSQLGenerator.java:52)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLCreateTableSQLGenerator.generate(PostgreSQLCreateTableSQLGenerator.java:43)
at org.apache.shardingsphere.data.pipeline.core.metadata.generator.PipelineDDLGenerator.generateLogicDDL(PipelineDDLGenerator.java:79)
at org.apache.shardingsphere.data.pipeline.core.prepare.datasource.AbstractDataSourcePreparer.getCreateTargetTableSQL(AbstractDataSourcePreparer.java:123)
at org.apache.shardingsphere.data.pipeline.postgresql.prepare.datasource.PostgreSQLDataSourcePreparer.prepareTargetTables(PostgreSQLDataSourcePreparer.java:40)
at org.apache.shardingsphere.data.pipeline.core.prepare.PipelineJobPreparerUtils.prepareTargetTables(PipelineJobPreparerUtils.java:123)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareTarget(MigrationJobPreparer.java:153)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareAndCheckTarget(MigrationJobPreparer.java:132)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareAndCheckTargetWithLock(MigrationJobPreparer.java:118)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepare(MigrationJobPreparer.java:84)
at org.apache.shardingsphere.data.pipeline.scenario.migration.MigrationJob.doPrepare(MigrationJob.java:76)
at org.apache.shardingsphere.data.pipeline.core.job.AbstractPipelineJob.prepare(AbstractPipelineJob.java:90)
at org.apache.shardingsphere.data.pipeline.core.job.AbstractSimplePipelineJob.execute(AbstractSimplePipelineJob.java:61)
at org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:33)
at org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:29)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.process(ElasticJobExecutor.java:173)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.process(ElasticJobExecutor.java:142)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.execute(ElasticJobExecutor.java:124)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.execute(ElasticJobExecutor.java:100)
at org.apache.shardingsphere.elasticjob.lite.internal.schedule.LiteJob.execute(LiteJob.java:35)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.postgresql.util.PSQLException: ERROR: column "datlastsysoid" does not exist
Position: 21
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:243)
at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:110)
at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.AbstractPostgreSQLDDLAdapter.executeByTemplateForSingleRow(AbstractPostgreSQLDDLAdapter.java:80)
... 24 common frames omitted
[INFO ] 2023-06-02 16:02:04.170 [Pipeline-EventListener-0] o.a.s.d.p.c.m.n.e.h.i.ConfigMetaDataChangedEventHandler - UPDATED job configuration: /pipeline/jobs/j0102p00002c60d901819e54f4b5361c5781d943ca/config, disabled=true
[INFO ] 2023-06-02 16:02:04.170 [Pipeline-EventListener-0] o.a.s.d.p.c.job.AbstractPipelineJob - stop tasks runner, jobId=j0102p00002c60d901819e54f4b5361c5781d943ca
[INFO ] 2023-06-02 16:02:06.321 [Pipeline-EventListener-0] org.quartz.core.QuartzScheduler - Scheduler j0102p00002c60d901819e54f4b5361c5781d943ca_$_NON_CLUSTERED shutting down.
[INFO ] 2023-06-02 16:02:06.321 [Pipeline-EventListener-0] org.quartz.core.QuartzScheduler - Scheduler j0102p00002c60d901819e54f4b5361c5781d943ca_$_NON_CLUSTERED paused.
[INFO ] 2023-06-02 16:02:06.327 [Pipeline-EventListener-0] org.quartz.core.QuartzScheduler - Scheduler j0102p00002c60d901819e54f4b5361c5781d943ca_$_NON_CLUSTERED shutdown complete.
[ERROR] 2023-06-02 16:02:06.351 [j0102p00002c60d901819e54f4b5361c5781d943ca_Worker-1] o.a.s.e.e.h.g.LogJobErrorHandler - Job 'j0102p00002c60d901819e54f4b5361c5781d943ca' exception occur in job processing
org.apache.shardingsphere.infra.util.exception.external.sql.type.wrapper.SQLWrapperException: ERROR: column "datlastsysoid" does not exist
Position: 21
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.AbstractPostgreSQLDDLAdapter.executeByTemplateForSingleRow(AbstractPostgreSQLDDLAdapter.java:83)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLTablePropertiesLoader.fetchDataBaseId(PostgreSQLTablePropertiesLoader.java:60)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLTablePropertiesLoader.load(PostgreSQLTablePropertiesLoader.java:50)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLCreateTableSQLGenerator.loadMaterials(PostgreSQLCreateTableSQLGenerator.java:52)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.PostgreSQLCreateTableSQLGenerator.generate(PostgreSQLCreateTableSQLGenerator.java:43)
at org.apache.shardingsphere.data.pipeline.core.metadata.generator.PipelineDDLGenerator.generateLogicDDL(PipelineDDLGenerator.java:79)
at org.apache.shardingsphere.data.pipeline.core.prepare.datasource.AbstractDataSourcePreparer.getCreateTargetTableSQL(AbstractDataSourcePreparer.java:123)
at org.apache.shardingsphere.data.pipeline.postgresql.prepare.datasource.PostgreSQLDataSourcePreparer.prepareTargetTables(PostgreSQLDataSourcePreparer.java:40)
at org.apache.shardingsphere.data.pipeline.core.prepare.PipelineJobPreparerUtils.prepareTargetTables(PipelineJobPreparerUtils.java:123)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareTarget(MigrationJobPreparer.java:153)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareAndCheckTarget(MigrationJobPreparer.java:132)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepareAndCheckTargetWithLock(MigrationJobPreparer.java:118)
at org.apache.shardingsphere.data.pipeline.scenario.migration.prepare.MigrationJobPreparer.prepare(MigrationJobPreparer.java:84)
at org.apache.shardingsphere.data.pipeline.scenario.migration.MigrationJob.doPrepare(MigrationJob.java:76)
at org.apache.shardingsphere.data.pipeline.core.job.AbstractPipelineJob.prepare(AbstractPipelineJob.java:90)
at org.apache.shardingsphere.data.pipeline.core.job.AbstractSimplePipelineJob.execute(AbstractSimplePipelineJob.java:61)
at org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:33)
at org.apache.shardingsphere.elasticjob.simple.executor.SimpleJobExecutor.process(SimpleJobExecutor.java:29)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.process(ElasticJobExecutor.java:173)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.process(ElasticJobExecutor.java:142)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.execute(ElasticJobExecutor.java:124)
at org.apache.shardingsphere.elasticjob.executor.ElasticJobExecutor.execute(ElasticJobExecutor.java:100)
at org.apache.shardingsphere.elasticjob.lite.internal.schedule.LiteJob.execute(LiteJob.java:35)
at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:573)
Caused by: org.postgresql.util.PSQLException: ERROR: column "datlastsysoid" does not exist
Position: 21
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:243)
at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:110)
at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java)
at org.apache.shardingsphere.data.pipeline.postgresql.ddlgenerator.AbstractPostgreSQLDDLAdapter.executeByTemplateForSingleRow(AbstractPostgreSQLDDLAdapter.java:80)
... 24 common frames omitted
```

### Reason analyze (If you can)
Postgres 15 removed datlastsysoid field from pg_database table, so the error `column "datlastsysoid" does not exist` exception

### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
migration with PG 15

Contributor guide

Open the contributing guide

Research direction

Start with AbstractPostgreSQLDDLAdapter.java and PostgreSQLTablePropertiesLoader.java, especially the fetchDataBaseId path shown in the stack trace. Reproduce migration against PostgreSQL 15 and inspect the failing metadata lookup. Done means PostgreSQL 15 migration preparation succeeds without the missing datlastsysoid error.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, postgresql
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.