问题现象:
(1)、CDM任务执行时报错:org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
2025-09-29 10:22:19.663|INFO |cdm-job-submit-pool24|[o.a.s.c.jdbc.GenericJdbcExecutor:596]|Destructive Action Warning: truncate all data: TRUNCATE TABLE "turbo_dev_01"."t_cm_customer_ship_detail" 2025-09-29 10:27:19.763|ERROR|cdm-job-submit-pool24|[org.apache.sqoop.utils.JdbcSandbox:62]|occur exception, actual is : org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:399)at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:290)at org.apache.sqoop.connector.jdbc.sql.impl.WrapStatement.lambda$executeUpdate$1(WrapStatement.java:33)at java.security.AccessController.doPrivileged(Native Method)at org.apache.sqoop.utils.JdbcSandbox.doPrivileged(JdbcSandbox.java:53)at org.apache.sqoop.connector.jdbc.sql.impl.WrapStatement.executeUpdate(WrapStatement.java:33)at org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.executeUpdate(GenericJdbcExecutor.java:741)at org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.truncateTable(GenericJdbcExecutor.java:597)at sun.reflect.GeneratedMethodAccessor231.invoke(Unknown Source)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.apache.sqoop.connector.jdbc.writer.JdbcExecutorProxy$ExecutorInvocationHandler.invoke(JdbcExecutorProxy.java:95)at com.sun.proxy.$Proxy36.truncateTable(Unknown Source)at org.apache.sqoop.connector.jdbc.GenericJdbcToInitializer.clearBeforeLoading(GenericJdbcToInitializer.java:842)at org.apache.sqoop.connector.jdbc.GenericJdbcToInitializer.prepareJob(GenericJdbcToInitializer.java:641)at org.apache.sqoop.connector.jdbc.GenericJdbcToInitializer.prepareJob(GenericJdbcToInitializer.java:86)at org.apache.sqoop.driver.job.JobInitiator.prepareJob(JobInitiator.java:852)at org.apache.sqoop.driver.job.JobSubmissionContext.submitInternal(JobSubmissionContext.java:238)at org.apache.sqoop.driver.job.JobSubmissionContext.submit(JobSubmissionContext.java:183)at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)at java.util.concurrent.FutureTask.run(FutureTask.java:266)at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)at java.lang.Thread.run(Thread.java:750) Caused by: java.net.SocketTimeoutException: Read timed outat java.net.SocketInputStream.socketRead0(Native Method)at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)at java.net.SocketInputStream.read(SocketInputStream.java:171)at java.net.SocketInputStream.read(SocketInputStream.java:141)at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:192)at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:159)at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:144)at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:76)at org.postgresql.core.PGStream.receiveChar(PGStream.java:476)at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2174)at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)... 28 common frames omitted
(2)重试后,发现执行也会报错
2025-09-29 10:15:14.175|INFO |cdm-job-submit-pool7|[o.a.s.c.jdbc.GenericJdbcExecutor:1446]|execute check sql: SELECT count(1) FROM "turbo_dev_01"."t_cm_customer_ship_detail" WHERE 1 = 2 . 2025-09-29 10:20:14.275|ERROR|cdm-job-submit-pool7|[org.apache.sqoop.utils.JdbcSandbox:62]|occur exception, actual is : org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:399)at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:517)at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:434)at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:356)at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:341)at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:317)at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:312)at org.apache.sqoop.connector.jdbc.sql.impl.WrapStatement.lambda$execute$3(WrapStatement.java:50)at java.security.AccessController.doPrivileged(Native Method)at org.apache.sqoop.utils.JdbcSandbox.doPrivileged(JdbcSandbox.java:53)at org.apache.sqoop.connector.jdbc.sql.impl.WrapStatement.execute(WrapStatement.java:50)at org.apache.sqoop.connector.jdbc.GenericJdbcExecutor.existTable(GenericJdbcExecutor.java:1448)at sun.reflect.GeneratedMethodAccessor193.invoke(Unknown Source)at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)at java.lang.reflect.Method.invoke(Method.java:498)at org.apache.sqoop.connector.jdbc.writer.JdbcExecutorProxy$ExecutorInvocationHandler.invoke(JdbcExecutorProxy.java:95)at com.sun.proxy.$Proxy36.existTable(Unknown Source)at org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.configureTableProperties(GenericJdbcFromInitializer.java:750)at org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.initialize(GenericJdbcFromInitializer.java:206)at org.apache.sqoop.connector.jdbc.GenericJdbcFromInitializer.initialize(GenericJdbcFromInitializer.java:111)at org.apache.sqoop.driver.job.JobInitiator.initializeConnector(JobInitiator.java:787)at org.apache.sqoop.driver.job.JobInitiator.createJobRequest(JobInitiator.java:371)at org.apache.sqoop.driver.job.JobSubmissionContext.submitInternal(JobSubmissionContext.java:235)at org.apache.sqoop.driver.job.JobSubmissionContext.submit(JobSubmissionContext.java:183)at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)at java.util.concurrent.FutureTask.run(FutureTask.java:266)at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)at java.lang.Thread.run(Thread.java:750) Caused by: java.net.SocketTimeoutException: Read timed outat java.net.SocketInputStream.socketRead0(Native Method)at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)at java.net.SocketInputStream.read(SocketInputStream.java:171)at java.net.SocketInputStream.read(SocketInputStream.java:141)at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:192)at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:159)at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:144)at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:76)at org.postgresql.core.PGStream.receiveChar(PGStream.java:476)at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2174)at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:372)... 28 common frames omitted
问题原因:
由于重试多次,发现还是报相同的错误。怀疑是表被锁导致。
解决办法:
(1)、查询表是否被锁,获取pid
SELECTl.locktype,l.MODE,l.GRANTED,A.pid, --解除锁表使用 A.usename,A.query,A.STATE,A.application_name FROMpg_locks lJOIN pg_stat_activity A ON l.pid = A.pidJOIN pg_class C ON l.relation = C.oid WHEREC.relname = 't_cm_customer_ship_detail' -- 将 your_table_name 替换为你的表名AND l.relation IS NOT NULL;
(2)解锁表,根据pid解锁表
SELECT pg_terminate_backend(139637009676032); --查询pid接触锁表