Debezium SQLServerConnector for Kakfa Connect not creating topics in Kafka

[TD Knight]

I'm trying to get the SQLServerConnector from Debezium working in Confluent Kafka Connect and everything seems to be running fine, except there are no topics created. Am I missing something?

It starts the task with the right configs...

Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO Starting SqlServerConnectorTask with configuration: (io.debezium.connector.common.BaseSourceTask:42)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO    connector.class = io.debezium.connector.sqlserver.SqlServerConnector (io.debezium.connector.common.BaseSourceTask:44)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO    database.user = cdc_test (io.debezium.connector.common.BaseSourceTask:44)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO    database.dbname = kafka_sql_test (io.debezium.connector.common.BaseSourceTask:44)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO    task.class = io.debezium.connector.sqlserver.SqlServerConnectorTask (io.debezium.connector.common.BaseSourceTask:44)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO    database.hostname = mbvmsqlsvr001-t (io.debezium.connector.common.BaseSourceTask:44)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO    database.history.kafka.bootstrap.servers = horton1:9092 (io.debezium.connector.common.BaseSourceTask:44)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO    database.history.kafka.topic = dbhistory.sql2016test (io.debezium.connector.common.BaseSourceTask:44)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO    database.password = ******** (io.debezium.connector.common.BaseSourceTask:44)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO    name = sql-server-test-connector (io.debezium.connector.common.BaseSourceTask:44)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO    database.server.name = sql2016test (io.debezium.connector.common.BaseSourceTask:44)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO    database.port = 1433 (io.debezium.connector.common.BaseSourceTask:44)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,284] INFO    table.whitelist = message_table (io.debezium.connector.common.BaseSourceTask:44)

Gets the snapshot setup......

Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,828] INFO Snapshot step 1 - Preparing (io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource:85)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,830] INFO Snapshot step 2 - Determining captured tables (io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource:94)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,835] INFO Snapshot step 3 - Locking captured tables (io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource:100)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,836] INFO Schema locking was disabled in connector configuration (io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource:84)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,836] INFO Snapshot step 4 - Determining snapshot offset (io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource:106)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,837] INFO Snapshot step 5 - Reading structure of captured tables (io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource:109)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,837] INFO Snapshot step 6 - Persisting schema history (io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource:113)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,838] INFO Snapshot step 7 - Snapshotting data (io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource:125)
Apr 16 08:44:20 horton5 connect-distributed[3091]: [2019-04-16 08:44:20,839] INFO Snapshot step 8 - Finalizing (io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource:149)

And the connector tasks are running...

# curl  http://172.24.32.10:8083/connectors/sql-server-test-connector/status | jq
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   175  100   175    0     0  73160      0 --:--:-- --:--:-- --:--:-- 87500
{
  "name": "sql-server-test-connector",
  "connector": {
    "state": "RUNNING",
    "worker_id": "horton5:8083"
  },
  "tasks": [
    {
      "id": 0,
      "state": "RUNNING",
      "worker_id": "horton5:8083"
    }
  ],
  "type": "source"
}

The username and password have a SPID on the SQL Server and running the CDC queries to get the changes, but nothing shows up in a Kafka topic.

What am I not understanding? Am I supposed to create the topic first and then how would I config the connector to publish messages to it? I have inspected the Kafka Connect logs and there are no errors, but where are the Worker logs for the connectors?

[Gunnar Morling]

You've got a whitelist config there: table.whitelist = message_table.
Are you sure that there actually are changes to this specific table?
If so, you might increase the log level
(http://debezium.io/docs/logging/) and you should get some more info
about the connector's working (whether it actually receives events
etc.)

> --
> You received this message because you are subscribed to the Google Groups "debezium" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.
> To post to this group, send email to debe...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/8bafff97-2d07-4658-97cf-497929595bfc%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.

[TD Knight]

Yes, the table has changes and is setup for CDC.

I'll check more and more logs. Are they stdout logs or the logs for Kafka Connect? (The link you provided is dead.)

On Tuesday, April 16, 2019 at 12:23:37 PM UTC-7, Gunnar Morling wrote:

You've got a whitelist config there: table.whitelist = message_table.
Are you sure that there actually are changes to this specific table?
If so, you might increase the log level
(http://debezium.io/docs/logging/) and you should get some more info
about the connector's working (whether it actually receives events
etc.)

> To unsubscribe from this group and stop receiving emails from it, send an email to debe...@googlegroups.com.

[Gunnar Morling]

Kafka Connect logs. Sorry about the link, I meant this one:
https://debezium.io/docs/configuration/logging/

> To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.

> To post to this group, send email to debe...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/1b93de1f-50f2-453c-bc2a-03132104838d%40googlegroups.com.

[Max Li]

Would you please share your json file for mssql connector? I am struggling on this same testing... please

for the worker, here is my json, and I launched the connector

curl -s -X POST -H "Content-Type: application/json" --data '{

  "name": "newtest2",

  "config": {

    "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",

    "database.hostname": "10.154.54.83",

    "database.port": "1433",

    "database.user": "sql_testdb",

    "database.password": "silver7",

    "database.dbname": "mydb",

    "database.server.name": "myservername",

    "table.whitelist": "newtest2",

    "database.history.kafka.bootstrap.servers": "kafka:9092",

    "database.history.kafka.topic": "mssql-newtest2"

    }

}' http://houbiap5.oii.oceaneering.com:18083/connectors | jq

[TD Knight]

One thing I did try was put the schema.table in the white list config (instead of just the table name), per the Debezium documentation, but it made no difference.

curl -X POST -H "Content-Type: application/json" --data '{

  "name": "sql-server-test-connector",

  "config": {

    "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",

"database.hostname": "mbvmsqlsvr001-t",

"database.port": "1433",

"database.user": "cdc_test",

"database.password": "Password!123",

"database.dbname": "kafka_sql_test",

"database.server.name": "sql2016test",

"table.whitelist": "dbo.message_table",

"database.history.kafka.bootstrap.servers": "horton1:9092,horton2:9092,horton3:9092,horton4:9092",

"database.history.kafka.topic": "dbhistory.sql2016test"

}

  }' http://172.24.32.10:8083/connectors

[Max Li]

thank you very much!, I checked this link, https://debezium.io/docs/connectors/sqlserver/#connector-properties

I am confused about your database.hostname and database.server.name, usually they are same but I am really confused here,

database.server.name---Logical name that identifies and provides a namespace for the particular SQL Server database server being monitored. The logical name should be unique across all other connectors, since it is used as a prefix for all Kafka topic names emanating from this connector.

database.hostname--IP address or hostname of the SQL Server database server

on SQL server, select @@SERVERNAME;--return SQL server hostname

SELECT HOST_NAME() ---return client host name

[TD Knight]

So I interpreted those parameters differently.

database.server.name = Since this provides a namespace (I assume in Kafka Connect) and needs to be unique across all connectors, this can be whatever identifiable information you want. Considering the uniqueness requirement, if it is the SQL server name that would mean that if I had 100 databases with 10,000 tables each all residing on one SQL Server, then they would all need to use the same connector. It doesn't seem right.

database.hostname = This is required to be the IP address or the host name of the SQL Server.

Again, it is connected to SQL Server and able to query for changed data so these parameters seem correct (as far as I can tell), the problem is that no changes are being published to Kafka.

For the sake of proper troubleshooting, I changed the connector to the below and it did not make a difference.

curl -X POST -H "Content-Type: application/json" --data '{

  "name": "sql-server-test-connector",

  "config": {

    "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",

"database.hostname": "mbvmsqlsvr001-t",

"database.port": "1433",

"database.user": "cdc_test",

"database.password": "Password!123",

"database.dbname": "kafka_sql_test",

"database.server.name": "mbvmsqlsvr001-t",

"table.whitelist": "dbo.message_table",

"database.history.kafka.bootstrap.servers": "horton1:9092,horton2:9092,horton3:9092,horton4:9092",

"database.history.kafka.topic": "dbhistory.sql2016test"

}

  }' http://172.24.32.10:8083/connectors

[Jiri Pechanec]

Hi,

please use

"table.whitelist": "mbvmsqlsvr001-t.dbo.message_table",

for the configuration described above - please note the three part table name

J.

[Gunnar Morling]

> Considering the uniqueness requirement, if it is the SQL server name that would mean 

> that if I had 100 databases with 10,000 tables each all residing on one SQL Server,

> then they would all need to use the same connector. It doesn't seem right.

You can capture distinct subsets of those 10,000 tables using different connectors, they must just have separate server names then. It's really like a namespace, as you said.

--Gunnar

[TD Knight]

Sorry, changing the whitelist name did not fix the issue.

[TD Knight]

FIXED.

My own stupid mistake. I was using the debezium-connector-sqlserver-0.9.0.Alpha1-plugin.tar.gz distribution. I put the debezium-connector-sqlserver-0.9.4.Final-plugin.tar.gz in place and everything works perfect (I can see changes in the consumer nearly real time).

I don't even know how I got a hold of the Alpha version.......

[Jiri Pechanec]

Cool, thanks for letting us know!

J.