Unable to run a JDBC Source connector with Confluent REST API

Question

I want to run a JDBC source connector using Kafka Connect REST API. Although stand-alone mode works perfect using the following properties file:

name=source-mysql-test
connector.class=io.confluent.connect.jdbc.JdbcSourceConnector
tasks.max=1

connection.url=jdbc:mysql://localhost:3306/kafka
connection.user=myuser
connection.password=mypass


table.whitelist=MY_TABLE

# Pull all rows based on timestamp
mode=timestamp
timestamp.column.name=ROWVERSION
validate.non.null=false

# The Kafka topic will be made up of this prefix, plus the table name.
topic.prefix=MYSQL-

table.types=TABLE,VIEW
poll.interval.ms=1000

I am not able to run the connector using the REST API. Here's the call:

curl -X POST -H "Content-Type: application/json" --data '{"name": "source-mysql-test", "config": {"connector.class":"io.confluent.connect.jdbc.JdbcSourceConnector", "tasks.max":"1", "connection.url":"jdbc:mysql://localhost:3306/kafka","connection.user":"myuser","connection.password":"mypass", "table.whitelist":"MY_TABLE", "mode":"timestamp", "timestamp.column.name":"ROWVERSION", "validate.non.null":"false", "topic.prefix":"MYSQL-", "table.types":"TABLE,VIEW", "poll.interval.ms":"1000" }}' http://localhost:8083/connectors

And here's the response:

{
  "error_code": 400,
  "message": "Connector configuration is invalid and contains the following 2 error(s):\nInvalid value com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. for configuration Couldn't open connection to jdbc:mysql://localhost:3306/kafka\nInvalid value com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. for configuration Couldn't open connection to jdbc:mysql://localhost:3306/kafka\nYou can also find the above list of errors at the endpoint `/{connectorType}/config/validate`"
}

In the past I have used the REST API in order to run JDBC sink connectors without any problems!

Here are the available connector plugins:

> ls /usr/share/java/kafka-connect-jdbc/
common-utils-4.1.0.jar        mysql-connector-java-5.1.46.jar  uber-restavro-1.0-SNAPSHOT.jar
jline-0.9.94.jar              netty-3.10.5.Final.jar          
kafka-connect-jdbc-4.1.0.jar  postgresql-9.4-1206-jdbc41.jar   zkclient-0.10.jar
log4j-1.2.17.jar              slf4j-api-1.7.25.jar             zookeeper-3.4.10.jar
mssql-jdbc-6.2.2.jre8.jar     sqlite-jdbc-3.8.11.2.jar

Reinareinald

Answer 1

The problem seems to be the compatibility of the JDBC Connector with MySQL 8. I've upgraded MySQL Connector/J from 5.1.46 to 8.0.11 and the issue has been fixed.

Reinareinald

Answer 2

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: 
Could not create connection to database server

Kafka Connect cannot connect to your MySQL machine.

Traipse

How can you explain that with standalone mode, Kafka Connect can connect to MySQL? – Reinareinald

Is it running on a different machine? Are you using the same JDBC driver? The error is pretty explicit: Couldn't open connection to jdbc:mysql://localhost:3306/kafka – Traipse

Yes, MySQL and Kafka are running on the same machine and both the standalone mode and REST API call have been performed on the same machine. – Reinareinald

Also, other calls to REST API run without any problems and are able to connect to the database. – Reinareinald

OK, so what's the difference between the REST calls that do connect to the database, and this one that doesn't? – Traipse

REST calls that do connect to the database, are using SQL Server but the one that does not, is trying to open a connection in a MySQL Database. The most interesting part is that in standalone-mode, kafka is able to open the connection on MySQL. With the exact same configuration (as you can see from the configurations provided in the question), kafka connect is unable to connect to the database through a REST call. – Reinareinald

Another difference between the REST calls that do work and the one that does not, is the connector type. Those running are sink connectors while the one that is not able to connect to the database is a source connector. – Reinareinald

if you run a JDBC client (e.g. jisql) from the machine running Kafka Connect, can it connect to the MySQL database that you are using as a source using the same JDBC URL? – Traipse