[FLINK-29558] Use select count(*) from xxx; and get SQL syntax

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.15.3
• Fix Version/s: 1.17.0
• Component/s: Table SQL / Planner
• Labels: pull-request-available

Environment

flink 1.15.2
CentOS Linux release 7.9.2009 (Core)
5.7.32-log MySQL Community Server (GPL)

• Assignee: lincoln lee
• Reporter: StarBoy1005
• Created: 10/Oct/22 07:37
• Updated: 30/Jan/23 08:12
• Resolved: 30/Jan/23 08:12

Description

Hi, I use flink sql to make kafka records to mysql.
so I create these 2 tables in flink sql,here is the mysql ,and I created the table in mysql before I did the insert action in flink sql.

CREATE TABLE mysql_MyUserTable (
id STRING,
name STRING,
age STRING,
status STRING,
PRIMARY KEY (id) NOT ENFORCED
) WITH (
'connector' = 'jdbc',
'url' = 'jdbc:mysql://10.19.29.170:3306/fromflink152',
'table-name' = 'users',
'username' = 'root',
'password' = '******'
);

In mysql, I created database "fromflink152" then created the table like this way

CREATE TABLE `users` (
`id` varchar(64) NOT NULL DEFAULT '',
`name` varchar(255) DEFAULT NULL,
`age` varchar(255) DEFAULT NULL,
`status` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
)

After executed insert sql,I found 'select * from mysql_MyUserTable' can get correct result,but ’select count(*) from mysql_MyUserTable‘ or ’select count(id) from mysql_MyUserTable‘ ,the collect job in flink app keep restarting again and again.The exception is:

So I wonder which config that I missed about the table in flink or mysql side

Issue Links

• links to: GitHub Pull Request #21704