kafka sink connector with mysql DB table not found

Question

I was trying to configure kafka sink connector to mysql DB. Kafka topic has value in AVRO format, and i want to dump data to mysql. I was getting error saying table not found (Table 'airflow.mytopic' doesn't exist). I was expecting table to be created in 'myschema.mytopic', but it was looking for table in airflow. I had enabled "auto.create": "true" expecting the table to be created wherever it wants.

I am using Confluent Kafka 5.4.1 and started it manually

Configuration:

"topics": "mytopic",
"connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",
"connection.url": "jdbc:mysql://<mysqlDB>:3306/myschema",
"connection.user": "db_user",
"connection.password": "db_pwd",
"tasks.max": "1",
"auto.evolve": "true",
"auto.create": "true",
"transforms": "routeRecords",
"transforms.routeRecords.type": "org.apache.kafka.connect.transforms.RegexRouter",
"transforms.routeRecords.replacement": "$1",
"transforms.routeRecords.regex": "(.*)",
"key.converter": "org.apache.kafka.connect.storage.StringConverter",
"value.converter": "io.confluent.connect.avro.AvroConverter",
"value.converter.schema.registry.url": "http://localhost:8084",
"connection.attempts": "1",
"dialect.name": "MySqlDatabaseDialect",
"table.name.format": "myschema.mytopic"

Error stack:

org.apache.kafka.connect.errors.ConnectException: Exiting WorkerSinkTask due to unrecoverable exception.
    at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:561)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.poll(WorkerSinkTask.java:322)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.iteration(WorkerSinkTask.java:224)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.execute(WorkerSinkTask.java:192)
    at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:177)
    at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:227)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: org.apache.kafka.connect.errors.ConnectException: java.sql.SQLException: Exception chain:
java.sql.SQLSyntaxErrorException: Table 'airflow.mytopic' doesn't exist

    at io.confluent.connect.jdbc.sink.JdbcSinkTask.put(JdbcSinkTask.java:122)
    at org.apache.kafka.connect.runtime.WorkerSinkTask.deliverMessages(WorkerSinkTask.java:539)
    ... 10 more
Caused by: java.sql.SQLException: Exception chain:
java.sql.SQLSyntaxErrorException: Table 'airflow.mytopic' doesn't exist
    
    at io.confluent.connect.jdbc.sink.JdbcSinkTask.getAllMessagesException(JdbcSinkTask.java:150)
    at io.confluent.connect.jdbc.sink.JdbcSinkTask.put(JdbcSinkTask.java:102)
    ... 11 more

Any clue what could be the reason for error?

Answer 1

Issue got resolved by downgrading the mysql driver (mysql-connector-java-5.1.17.jar), below are the configurations

"topics": "mytopic",
"connector.class": "io.confluent.connect.jdbc.JdbcSinkConnector",
"connection.url": "jdbc:mysql://<mysqlDB>:3306/myschema",
"connection.user": "db_user",
"connection.password": "db_pwd",
"tasks.max": "1",
"insert.mode": "insert",
"auto.evolve": "true",
"auto.create": "true",
"transforms": "unwrap",
"transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",
"transforms.unwrap.drop.tombstones": "false",
"key.converter": "org.apache.kafka.connect.storage.StringConverter",
"value.converter": "io.confluent.connect.avro.AvroConverter",
"value.converter.schema.registry.url": "http://localhost:8084",
"connection.attempts": "1",
"dialect.name": "MySqlDatabaseDialect",
"table.name.format": "myschema.mytopic"