[Bug] [connetor-jdbc] the number of insert sql fields in sink is less than tableSchema.columns occurs Exception

[rtyuy]

Search before asking

• I had searched in the issues and found no similar issues.

What happened

Sink directly use tableSchema in source. When the number of insert sql fields in Slink is less than Source tableSchema.columns, an exception occurs

SeaTunnel Version

2.3.5-SNAPSHOT

SeaTunnel Config

env {
  parallelism = 1
  job.mode = "BATCH"
}

source {
  # This is a example source plugin **only for test and demonstrate the feature source plugin**
  Jdbc {
    result_table_name = "INVER_WORK_ORDER"
    driver = oracle.jdbc.driver.OracleDriver
    url = "jdbc:oracle:thin:@192.168.199.187:1521/XE"
    user = ***
    password = ***
    query = "select work_order_id, work_order_name, expect_time, work_order_content, need_type, work_order_type, work_order_state, create_staff_id, create_staff_name, create_date, accept_date, end_date, scores, solve, return_num, se_state, se_date, se_content, se_speed, se_attitude, se_quality, type_code, promoter_staff_id, promoter_staff_name, source_sys, up_flag, promoter_staff_phone, region_id from  INVER_WORK_ORDER"
    properties {
       database.oracle.jdbc.timezoneAsRegion = "false"
    }
  }

  # If you would like to get more information about how to configure seatunnel and see full list of source plugins,
  # please go to https://seatunnel.apache.org/docs/connector-v2/source/FakeSource
}

sink {
  Jdbc {
    source_table_name = "INVER_WORK_ORDER"
    driver = "org.postgresql.Driver"
    url = "jdbc:postgresql://192.168.199.187:5432/postgres"
    user = ***
    password =***
    query = "INSERT INTO INVER_WORK_ORDER (work_order_id,work_order_name,       expect_time,   work_order_content,  need_type,work_order_type,work_order_state,create_staff_id,create_staff_name) VALUES(?,?,?,?,?,?,?,?,?)"
  }
  Jdbc {
    source_table_name = "INVER_WORK_ORDER"
    driver = "org.postgresql.Driver"
    url = "jdbc:postgresql://192.168.199.187:5432/postgres"
    user = postgres
    password = whale
    query = "INSERT INTO INVER_WORK_ORDER2 (work_order_id,work_order_name,  create_staff_id,create_staff_name) VALUES(?,?,?,?)"
  }
  # If you would like to get more information about how to configure seatunnel and see full list of sink plugins,
  # please go to https://seatunnel.apache.org/docs/connector-v2/sink/Jdbc
}

Running Command

$SEATUNNEL_HOME/bin/seatunnel.sh --config $SEATUNNEL_HOME/config/ora_to_pg.config.bak

Error Exception

2024-03-15 11:01:11,313 ERROR [o.a.s.c.s.SeaTunnel           ] [main] - 

===============================================================================


2024-03-15 11:01:11,313 ERROR [o.a.s.c.s.SeaTunnel           ] [main] - Fatal Error, 

2024-03-15 11:01:11,313 ERROR [o.a.s.c.s.SeaTunnel           ] [main] - Please submit bug report in https://github.com/apache/seatunnel/issues

2024-03-15 11:01:11,313 ERROR [o.a.s.c.s.SeaTunnel           ] [main] - Reason:SeaTunnel job executed failed 

2024-03-15 11:01:11,315 ERROR [o.a.s.c.s.SeaTunnel           ] [main] - Exception StackTrace:org.apache.seatunnel.core.starter.exception.CommandExecuteException: SeaTunnel job executed failed
        at org.apache.seatunnel.core.starter.seatunnel.command.ClientExecuteCommand.execute(ClientExecuteCommand.java:199)
        at org.apache.seatunnel.core.starter.SeaTunnel.run(SeaTunnel.java:40)
        at org.apache.seatunnel.core.starter.seatunnel.SeaTunnelClient.main(SeaTunnelClient.java:34)
Caused by: org.apache.seatunnel.engine.common.exception.SeaTunnelEngineException: java.lang.RuntimeException: java.lang.RuntimeException: org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException: ErrorCode:[COMMON-08], ErrorDescription:[Sql operation failed, such as (execute,addBatch,close) etc...] - Writing records to JDBC failed.
        at org.apache.seatunnel.engine.server.task.flow.SinkFlowLifeCycle.received(SinkFlowLifeCycle.java:257)
        at org.apache.seatunnel.engine.server.task.flow.SinkFlowLifeCycle.received(SinkFlowLifeCycle.java:66)
        at org.apache.seatunnel.engine.server.task.SeaTunnelTransformCollector.collect(SeaTunnelTransformCollector.java:39)
        at org.apache.seatunnel.engine.server.task.SeaTunnelTransformCollector.collect(SeaTunnelTransformCollector.java:27)
        at org.apache.seatunnel.engine.server.task.group.queue.IntermediateBlockingQueue.handleRecord(IntermediateBlockingQueue.java:75)
        at org.apache.seatunnel.engine.server.task.group.queue.IntermediateBlockingQueue.collect(IntermediateBlockingQueue.java:50)
        at org.apache.seatunnel.engine.server.task.flow.IntermediateQueueFlowLifeCycle.collect(IntermediateQueueFlowLifeCycle.java:51)
        at org.apache.seatunnel.engine.server.task.TransformSeaTunnelTask.collect(TransformSeaTunnelTask.java:73)
        at org.apache.seatunnel.engine.server.task.SeaTunnelTask.stateProcess(SeaTunnelTask.java:168)
        at org.apache.seatunnel.engine.server.task.TransformSeaTunnelTask.call(TransformSeaTunnelTask.java:78)
        at org.apache.seatunnel.engine.server.TaskExecutionService$BlockingWorker.run(TaskExecutionService.java:649)
        at org.apache.seatunnel.engine.server.TaskExecutionService$NamedTaskWrapper.run(TaskExecutionService.java:952)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.lang.RuntimeException: org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException: ErrorCode:[COMMON-08], ErrorDescription:[Sql operation failed, such as (execute,addBatch,close) etc...] - Writing records to JDBC failed.
        at org.apache.seatunnel.connectors.seatunnel.common.multitablesink.MultiTableSinkWriter.subSinkErrorCheck(MultiTableSinkWriter.java:117)
        at org.apache.seatunnel.connectors.seatunnel.common.multitablesink.MultiTableSinkWriter.write(MultiTableSinkWriter.java:136)
        at org.apache.seatunnel.connectors.seatunnel.common.multitablesink.MultiTableSinkWriter.write(MultiTableSinkWriter.java:41)
        at org.apache.seatunnel.engine.server.task.flow.SinkFlowLifeCycle.received(SinkFlowLifeCycle.java:247)
        ... 16 more
Caused by: org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException: ErrorCode:[COMMON-08], ErrorDescription:[Sql operation failed, such as (execute,addBatch,close) etc...] - Writing records to JDBC failed.
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.writeRecord(JdbcOutputFormat.java:109)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.sink.JdbcSinkWriter.write(JdbcSinkWriter.java:129)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.sink.JdbcSinkWriter.write(JdbcSinkWriter.java:47)
        at org.apache.seatunnel.connectors.seatunnel.common.multitablesink.MultiTableWriterRunnable.run(MultiTableWriterRunnable.java:62)
        ... 5 more
Caused by: org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException: ErrorCode:[COMMON-10], ErrorDescription:[Flush data operation that in sink connector failed]
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.flush(JdbcOutputFormat.java:137)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.writeRecord(JdbcOutputFormat.java:106)
        ... 8 more
Caused by: org.postgresql.util.PSQLException: 栏位索引超过许可范围：10，栏位数：9。
        at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:70)
        at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:133)
        at org.postgresql.jdbc.PgPreparedStatement.bindString(PgPreparedStatement.java:1099)
        at org.postgresql.jdbc.PgPreparedStatement.setTimestamp(PgPreparedStatement.java:1470)
        at org.postgresql.jdbc.PgPreparedStatement.setTimestamp(PgPreparedStatement.java:420)
        at org.apache.seatunnel.shade.com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setTimestamp(HikariProxyPreparedStatement.java)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.FieldNamedPreparedStatement.setTimestamp(FieldNamedPreparedStatement.java:153)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.converter.AbstractJdbcRowConverter.toExternal(AbstractJdbcRowConverter.java:174)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.SimpleBatchStatementExecutor.addToBatch(SimpleBatchStatementExecutor.java:45)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.SimpleBatchStatementExecutor.addToBatch(SimpleBatchStatementExecutor.java:31)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.BufferedBatchStatementExecutor.executeBatch(BufferedBatchStatementExecutor.java:51)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.attemptFlush(JdbcOutputFormat.java:167)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.flush(JdbcOutputFormat.java:131)
        ... 9 more

        at org.apache.seatunnel.core.starter.seatunnel.command.ClientExecuteCommand.execute(ClientExecuteCommand.java:191)
        ... 2 more
 
2024-03-15 11:01:11,315 ERROR [o.a.s.c.s.SeaTunnel           ] [main] - 
===============================================================================



Exception in thread "main" org.apache.seatunnel.core.starter.exception.CommandExecuteException: SeaTunnel job executed failed
        at org.apache.seatunnel.core.starter.seatunnel.command.ClientExecuteCommand.execute(ClientExecuteCommand.java:199)
        at org.apache.seatunnel.core.starter.SeaTunnel.run(SeaTunnel.java:40)
        at org.apache.seatunnel.core.starter.seatunnel.SeaTunnelClient.main(SeaTunnelClient.java:34)
Caused by: org.apache.seatunnel.engine.common.exception.SeaTunnelEngineException: java.lang.RuntimeException: java.lang.RuntimeException: org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException: ErrorCode:[COMMON-08], ErrorDescription:[Sql operation failed, such as (execute,addBatch,close) etc...] - Writing records to JDBC failed.
        at org.apache.seatunnel.engine.server.task.flow.SinkFlowLifeCycle.received(SinkFlowLifeCycle.java:257)
        at org.apache.seatunnel.engine.server.task.flow.SinkFlowLifeCycle.received(SinkFlowLifeCycle.java:66)
        at org.apache.seatunnel.engine.server.task.SeaTunnelTransformCollector.collect(SeaTunnelTransformCollector.java:39)
        at org.apache.seatunnel.engine.server.task.SeaTunnelTransformCollector.collect(SeaTunnelTransformCollector.java:27)
        at org.apache.seatunnel.engine.server.task.group.queue.IntermediateBlockingQueue.handleRecord(IntermediateBlockingQueue.java:75)
        at org.apache.seatunnel.engine.server.task.group.queue.IntermediateBlockingQueue.collect(IntermediateBlockingQueue.java:50)
        at org.apache.seatunnel.engine.server.task.flow.IntermediateQueueFlowLifeCycle.collect(IntermediateQueueFlowLifeCycle.java:51)
        at org.apache.seatunnel.engine.server.task.TransformSeaTunnelTask.collect(TransformSeaTunnelTask.java:73)
        at org.apache.seatunnel.engine.server.task.SeaTunnelTask.stateProcess(SeaTunnelTask.java:168)
        at org.apache.seatunnel.engine.server.task.TransformSeaTunnelTask.call(TransformSeaTunnelTask.java:78)
        at org.apache.seatunnel.engine.server.TaskExecutionService$BlockingWorker.run(TaskExecutionService.java:649)
        at org.apache.seatunnel.engine.server.TaskExecutionService$NamedTaskWrapper.run(TaskExecutionService.java:952)
        at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
        at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
        at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.lang.RuntimeException: org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException: ErrorCode:[COMMON-08], ErrorDescription:[Sql operation failed, such as (execute,addBatch,close) etc...] - Writing records to JDBC failed.
        at org.apache.seatunnel.connectors.seatunnel.common.multitablesink.MultiTableSinkWriter.subSinkErrorCheck(MultiTableSinkWriter.java:117)
        at org.apache.seatunnel.connectors.seatunnel.common.multitablesink.MultiTableSinkWriter.write(MultiTableSinkWriter.java:136)
        at org.apache.seatunnel.connectors.seatunnel.common.multitablesink.MultiTableSinkWriter.write(MultiTableSinkWriter.java:41)
        at org.apache.seatunnel.engine.server.task.flow.SinkFlowLifeCycle.received(SinkFlowLifeCycle.java:247)
        ... 16 more
Caused by: org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException: ErrorCode:[COMMON-08], ErrorDescription:[Sql operation failed, such as (execute,addBatch,close) etc...] - Writing records to JDBC failed.
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.writeRecord(JdbcOutputFormat.java:109)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.sink.JdbcSinkWriter.write(JdbcSinkWriter.java:129)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.sink.JdbcSinkWriter.write(JdbcSinkWriter.java:47)
        at org.apache.seatunnel.connectors.seatunnel.common.multitablesink.MultiTableWriterRunnable.run(MultiTableWriterRunnable.java:62)
        ... 5 more
Caused by: org.apache.seatunnel.connectors.seatunnel.jdbc.exception.JdbcConnectorException: ErrorCode:[COMMON-10], ErrorDescription:[Flush data operation that in sink connector failed]
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.flush(JdbcOutputFormat.java:137)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.writeRecord(JdbcOutputFormat.java:106)
        ... 8 more
Caused by: org.postgresql.util.PSQLException: 栏位索引超过许可范围：10，栏位数：9。
        at org.postgresql.core.v3.SimpleParameterList.bind(SimpleParameterList.java:70)
        at org.postgresql.core.v3.SimpleParameterList.setStringParameter(SimpleParameterList.java:133)
        at org.postgresql.jdbc.PgPreparedStatement.bindString(PgPreparedStatement.java:1099)
        at org.postgresql.jdbc.PgPreparedStatement.setTimestamp(PgPreparedStatement.java:1470)
        at org.postgresql.jdbc.PgPreparedStatement.setTimestamp(PgPreparedStatement.java:420)
        at org.apache.seatunnel.shade.com.zaxxer.hikari.pool.HikariProxyPreparedStatement.setTimestamp(HikariProxyPreparedStatement.java)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.FieldNamedPreparedStatement.setTimestamp(FieldNamedPreparedStatement.java:153)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.converter.AbstractJdbcRowConverter.toExternal(AbstractJdbcRowConverter.java:174)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.SimpleBatchStatementExecutor.addToBatch(SimpleBatchStatementExecutor.java:45)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.SimpleBatchStatementExecutor.addToBatch(SimpleBatchStatementExecutor.java:31)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.executor.BufferedBatchStatementExecutor.executeBatch(BufferedBatchStatementExecutor.java:51)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.attemptFlush(JdbcOutputFormat.java:167)
        at org.apache.seatunnel.connectors.seatunnel.jdbc.internal.JdbcOutputFormat.flush(JdbcOutputFormat.java:131)
        ... 9 more

        at org.apache.seatunnel.core.starter.seatunnel.command.ClientExecuteCommand.execute(ClientExecuteCommand.java:191)
        ... 2 more
2024-03-15 11:01:11,317 INFO  [s.c.s.s.c.ClientExecuteCommand] [ForkJoinPool.commonPool-worker-19] - run shutdown hook because get close signal

Zeta or Flink or Spark Version

No response

Java or Scala Version

No response

Screenshots

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[github-actions]

This issue has been automatically marked as stale because it has not had recent activity for 30 days. It will be closed in next 7 days if no further activity occurs.

[github-actions]

This issue has been closed because it has not received response for too long time. You could reopen it if you encountered similar problems in the future.

[hailin0]

Why not use transform to filter fields？