[
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)
