You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@flink.apache.org by "Tan Nguyen (Jira)" <ji...@apache.org> on 2024/10/17 03:43:00 UTC
[jira] [Updated] (FLINK-36372) Can not insert data to sink with reserved keywords column name

     [ https://issues.apache.org/jira/browse/FLINK-36372?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Tan Nguyen updated FLINK-36372:
-------------------------------
    Affects Version/s: 1.18.1

> Can not insert data to sink with reserved keywords column name
> --------------------------------------------------------------
>
>                 Key: FLINK-36372
>                 URL: https://issues.apache.org/jira/browse/FLINK-36372
>             Project: Flink
>          Issue Type: Bug
>    Affects Versions: 1.18.1
>            Reporter: Tan Nguyen
>            Priority: Major
>         Attachments: image-2024-09-26-10-33-37-134.png, image-2024-09-26-10-40-07-605.png, image-2024-09-26-10-41-10-327.png, image-2024-09-26-10-48-11-315.png
>
>
> Hi teams,
> I created a flink application running as batch mode, we read data from kafka then we run the query transform data from sql file. Finally execute insert query from sql file to sink data. Here the insert query:
> !image-2024-09-26-10-33-37-134.png!
> Even I use the backticks to escape the reserved keywords, still failed, here the error:
> {code:java}
> // code placeholder
> java.lang.RuntimeException: Writing records to JDBC failed.at org.apache.flink.connector.jdbc.internal.JdbcOutputFormat.close(JdbcOutputFormat.java:265)at org.apache.flink.connector.jdbc.internal.GenericJdbcSinkFunction.close(GenericJdbcSinkFunction.java:70)at org.apache.flink.api.common.functions.util.FunctionUtils.closeFunction(FunctionUtils.java:41)at org.apache.flink.streaming.api.operators.AbstractUdfStreamOperator.close(AbstractUdfStreamOperator.java:115)at org.apache.flink.streaming.runtime.tasks.StreamOperatorWrapper.close(StreamOperatorWrapper.java:163)at org.apache.flink.streaming.runtime.tasks.RegularOperatorChain.closeAllOperators(RegularOperatorChain.java:125)at org.apache.flink.streaming.runtime.tasks.StreamTask.closeAllOperators(StreamTask.java:1062)at org.apache.flink.streaming.runtime.tasks.StreamTask.afterInvoke(StreamTask.java:930)at org.apache.flink.streaming.runtime.tasks.StreamTask.invoke(StreamTask.java:813)at org.apache.flink.runtime.taskmanager.Task.runWithSystemExitMonitoring(Task.java:955)at org.apache.flink.runtime.taskmanager.Task.restoreAndInvoke(Task.java:934)at org.apache.flink.runtime.taskmanager.Task.doRun(Task.java:748)at org.apache.flink.runtime.taskmanager.Task.run(Task.java:564)at java.base/java.lang.Thread.run(Thread.java:829)Caused by: java.io.IOException: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO native_program_domain.member_program_aux_data(id, member_program_id, program_provider_id, enrollment_event_type, cancellation_reason, member_start_date, house_hold_id, enrollment_source, plan_contract_id, protocol, enrollment_priority, member_disenroll_date, pbp, hras_needed, hra_due_date, completion_status, hra_completion_method, hra_completion_description, enrollment_lob, clock_start_date, csp_group_id, csp_plan_id, group, segment_id, division, created_by, created_date, last_modified_by, last_modified_date) VALUES (('3319eb7d-e247-4eec-9c3e-e0ba9ee5b7aa'), ('5e4fd136-2b14-3b63-9b9c-e3c1af424c55'), (NULL), ('R'), (NULL), ('20140608'), (NULL), (NULL), ('CONT-00008'), ('PR01'), (NULL), (NULL), ('PBP-0008'), ('FALSE'::boolean), (NULL), (NULL), (NULL), ('NextGen'), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL)) ON CONFLICT (id) DO UPDATE SET id=EXCLUDED.id, member_program_id=EXCLUDED.member_program_id, program_provider_id=EXCLUDED.program_provider_id, enrollment_event_type=EXCLUDED.enrollment_event_type, cancellation_reason=EXCLUDED.cancellation_reason, member_start_date=EXCLUDED.member_start_date, house_hold_id=EXCLUDED.house_hold_id, enrollment_source=EXCLUDED.enrollment_source, plan_contract_id=EXCLUDED.plan_contract_id, protocol=EXCLUDED.protocol, enrollment_priority=EXCLUDED.enrollment_priority, member_disenroll_date=EXCLUDED.member_disenroll_date, pbp=EXCLUDED.pbp, hras_needed=EXCLUDED.hras_needed, hra_due_date=EXCLUDED.hra_due_date, completion_status=EXCLUDED.completion_status, hra_completion_method=EXCLUDED.hra_completion_method, hra_completion_description=EXCLUDED.hra_completion_description, enrollment_lob=EXCLUDED.enrollment_lob, clock_start_date=EXCLUDED.clock_start_date, csp_group_id=EXCLUDED.csp_group_id, csp_plan_id=EXCLUDED.csp_plan_id, group=EXCLUDED.group, segment_id=EXCLUDED.segment_id, division=EXCLUDED.division, created_by=EXCLUDED.created_by, created_date=EXCLUDED.created_date, last_modified_by=EXCLUDED.last_modified_by, last_modified_date=EXCLUDED.last_modified_date was aborted: ERROR: syntax error at or near "group"Position: 435 Call getNextException to see other errors in the batch.at org.apache.flink.connector.jdbc.internal.JdbcOutputFormat.flush(JdbcOutputFormat.java:222)at org.apache.flink.connector.jdbc.internal.JdbcOutputFormat.close(JdbcOutputFormat.java:262)... 13 moreCaused by: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO native_program_domain.member_program_aux_data(id, member_program_id, program_provider_id, enrollment_event_type, cancellation_reason, member_start_date, house_hold_id, enrollment_source, plan_contract_id, protocol, enrollment_priority, member_disenroll_date, pbp, hras_needed, hra_due_date, completion_status, hra_completion_method, hra_completion_description, enrollment_lob, clock_start_date, csp_group_id, csp_plan_id, group, segment_id, division, created_by, created_date, last_modified_by, last_modified_date) VALUES (('3319eb7d-e247-4eec-9c3e-e0ba9ee5b7aa'), ('5e4fd136-2b14-3b63-9b9c-e3c1af424c55'), (NULL), ('R'), (NULL), ('20140608'), (NULL), (NULL), ('CONT-00008'), ('PR01'), (NULL), (NULL), ('PBP-0008'), ('FALSE'::boolean), (NULL), (NULL), (NULL), ('NextGen'), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL)) ON CONFLICT (id) DO UPDATE SET id=EXCLUDED.id, member_program_id=EXCLUDED.member_program_id, program_provider_id=EXCLUDED.program_provider_id, enrollment_event_type=EXCLUDED.enrollment_event_type, cancellation_reason=EXCLUDED.cancellation_reason, member_start_date=EXCLUDED.member_start_date, house_hold_id=EXCLUDED.house_hold_id, enrollment_source=EXCLUDED.enrollment_source, plan_contract_id=EXCLUDED.plan_contract_id, protocol=EXCLUDED.protocol, enrollment_priority=EXCLUDED.enrollment_priority, member_disenroll_date=EXCLUDED.member_disenroll_date, pbp=EXCLUDED.pbp, hras_needed=EXCLUDED.hras_needed, hra_due_date=EXCLUDED.hra_due_date, completion_status=EXCLUDED.completion_status, hra_completion_method=EXCLUDED.hra_completion_method, hra_completion_description=EXCLUDED.hra_completion_description, enrollment_lob=EXCLUDED.enrollment_lob, clock_start_date=EXCLUDED.clock_start_date, csp_group_id=EXCLUDED.csp_group_id, csp_plan_id=EXCLUDED.csp_plan_id, group=EXCLUDED.group, segment_id=EXCLUDED.segment_id, division=EXCLUDED.division, created_by=EXCLUDED.created_by, created_date=EXCLUDED.created_date, last_modified_by=EXCLUDED.last_modified_by, last_modified_date=EXCLUDED.last_modified_date was aborted: ERROR: syntax error at or near "group"Position: 435 Call getNextException to see other errors in the batch.at org.postgresql.jdbc.BatchResultHandler.handleCompletion(BatchResultHandler.java:186)at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:585)at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:896)at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:919)at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1685)at org.apache.flink.connector.jdbc.statement.FieldNamedPreparedStatementImpl.executeBatch(FieldNamedPreparedStatementImpl.java:65)at org.apache.flink.connector.jdbc.internal.executor.TableSimpleStatementExecutor.executeBatch(TableSimpleStatementExecutor.java:64)at org.apache.flink.connector.jdbc.internal.executor.TableBufferReducedStatementExecutor.executeBatch(TableBufferReducedStatementExecutor.java:101)at org.apache.flink.connector.jdbc.internal.JdbcOutputFormat.attemptFlush(JdbcOutputFormat.java:246)at org.apache.flink.connector.jdbc.internal.JdbcOutputFormat.flush(JdbcOutputFormat.java:216)... 14 moreCaused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "group"Position: 435at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:574)... 22 more {code}
> I try to remove backticks and other syntax like: `"group"`,  ``group``, `'group'`, etc, still failed. I  also copy the insert query from the log and execute in SQL Server, the same error happen. But when I change group to "group" the query ran succeed
> !image-2024-09-26-10-41-10-327.png!
> +Note:+ The select query/ create temporary view are working fine with the backticks, but the insert failed. Here is my insert code
> !image-2024-09-26-10-48-11-315.png!
> I hope the teams could take a look this issue, thanks!



--
This message was sent by Atlassian Jira
(v8.20.10#820010)