[FLINK-27268] build sql query error in JdbcDynamicTableSource

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.16.0
• Fix Version/s: 2.0.0
• Component/s: Connectors / JDBC
• Labels: pull-request-available

Language

Java

• Assignee: Unassigned
• Reporter: chouc
• Created: 16/Apr/22 07:52
• Updated: 10/Jul/24 06:57

Description

# Condidtion

build sql query error in JdbcDynamicTableSource

 

//代码占位符
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment();
        StreamTableEnvironment tenv = StreamTableEnvironment.create(env);
        String createMysqlTableMapping = "CREATE TABLE table(\n" +
            "id int \n" +
            " )  WITH (\n" +
            "    'connector' = 'jdbc',\n" +
            "    'url' = 'jdbc:mysql://s1:3306/db',\n" +
            "    'username' = '',\n" +
            "    'password' = '',\n" +
            "    'table-name' = 'table_name'" +
            ")\n";        String countSql = "select count(1) from t_ds_task_instance";
        tenv.executeSql(createMysqlTableMapping).print();
        tenv.executeSql(countSql).print(); 

ERROR

//代码占位符
Caused by: java.lang.IllegalArgumentException: open() failed.You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `table`' at line 1
  at org.apache.flink.connector.jdbc.table.JdbcRowDataInputFormat.open(JdbcRowDataInputFormat.java:207)
  at org.apache.flink.streaming.api.functions.source.InputFormatSourceFunction.run(InputFormatSourceFunction.java:84)
  at org.apache.flink.streaming.api.operators.StreamSource.run(StreamSource.java:110)
  at org.apache.flink.streaming.api.operators.StreamSource.run(StreamSource.java:67)
  at org.apache.flink.streaming.runtime.tasks.SourceStreamTask$LegacySourceFunctionThread.run(SourceStreamTask.java:332)
Caused by: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM `table`' at line 1
  at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
  at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
  at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953)
  at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1009)
  at org.apache.flink.connector.jdbc.table.JdbcRowDataInputFormat.open(JdbcRowDataInputFormat.java:204)
  ... 4 more 

 

Reason

because constants cann't be push to jdbc sql as columns, when user query single constants in a table,and result to build sql state error

 

 

Issue Links

• links to: GitHub Pull Request #19495