Tan Nguyen created FLINK-36372:
----------------------------------
Summary: 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
Reporter: Tan Nguyen
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 success
!image-2024-09-26-10-41-10-327.png!
+Note:+ The select query 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)
