Debezium SQL Server Connector on Kubernetes

[hansh1212]

I am hoping that someone might be able to help me troubleshoot and getting the SQL Server CDC working for my setup.

I am using the debezium connector (Alpha2) within Kubernetes on a kubernetes deployment of Kafka.   I am using the following SQL container image: microsoft/mssql-server-linux loaded with Microsoft's sample database named AdventureWorks2017.   CDC is enabled on the database as well as 2 tables, i.e. Person.Address and Person.AddressType.   The Address table has just under 20K records while the AddressType table has less than 10 records.

When the connector starts up, the snapshot works for the AddressType table without issue. As far as I can tell, it also is capturing changes and writing them to the AddressType topic correctly. 

On the otherhand, the CDC does not work for the Address table.  The topic is created, but there is no snapshot data being written to the topic.    When I make changes to the table, they are being tracked in the Person_Address_CT table, but once, again they are not being written to the topic.  There is nothing I can see in the logs that indicates any errors that are unique to the Address table.

Thanks.

   

[Jiri Pechanec]

Hi,

is this Kuberentes deployment specific problem or does it happen if you trey it without k8s?

J.

[hansh1212]

Thanks Jiri,

At this time, Kubernetes is the only deployment that I have to play with.   Is there a specific test that you might suggest that I try?

[Gunnar Morling]

Hi,

You could take our tutorial example (there's a version of this for SQL Server: https://github.com/debezium/debezium-examples/tree/master/tutorial#using-sql-server) and adjust it so it uses your tables. If this still expose the issue, you could create a branch with this reproducer and create a JIRA issue for it.

That said, have you excluded the table by means of whitelist/blacklist expressions by any chance? You should see an indication of the included/excluded tables in the log while the snapshot is running (might need to increase the log level for that).

--Gunnar

[hansh1212]

Hi Gunnar,

I am trying to work through the docker version in the Tutorial and substituting my database right now.  

I was using a whitelist to limit the CDC to only two tables in the entire mix of tables.   The logs did show the creation of snapshots...

2018-10-23 19:44:23,543 INFO   ||  Snapshot step 1 - Preparing   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]
2018-10-23 19:44:23,544 INFO   ||  Snapshot step 2 - Determining captured tables   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]
2018-10-23 19:44:23,563 INFO   ||  Snapshot step 3 - Locking captured tables   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]
2018-10-23 19:44:23,566 INFO   ||  Schema locking was disabled in connector configuration   [io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource]
2018-10-23 19:44:23,566 INFO   ||  Snapshot step 4 - Determining snapshot offset   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]
2018-10-23 19:44:23,573 INFO   ||  Snapshot step 5 - Reading structure of captured tables   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]
2018-10-23 19:44:23,574 INFO   ||  Reading stucture of schema 'AdventureWorks2017'   [io.debezium.connector.sqlserver.SqlServerSnapshotChangeEventSource]
2018-10-23 19:44:24,087 INFO   ||  Snapshot step 6 - Persisting schema history   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]
2018-10-23 19:44:24,200 WARN   ||  [Producer clientId=AdventureWorks2017-dbhistory] Error while fetching metadata with correlation id 1 : {dbhistory.adventures=LEADER_NOT_AVAILABLE}   [org.apache.kafka.clients.NetworkClient]
2018-10-23 19:44:24,200 INFO   ||  Cluster ID: kMBEbuIBSPauMJrrIL6cag   [org.apache.kafka.clients.Metadata]
2018-10-23 19:44:24,332 INFO   ||  Snapshot step 7 - Snapshotting data   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]
2018-10-23 19:44:24,333 INFO   ||       Exporting data from table 'AdventureWorks2017.Person.AddressType'   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]
2018-10-23 19:44:24,333 INFO   ||       For table 'AdventureWorks2017.Person.AddressType' using select statement: 'SELECT * FROM Person.AddressType'   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]
2018-10-23 19:44:24,354 INFO   ||       Finished exporting 6 records for table 'AdventureWorks2017.Person.AddressType'; total duration '00:00:00.019'   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]
2018-10-23 19:44:24,354 INFO   ||       Exporting data from table 'AdventureWorks2017.Person.Address'   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]
2018-10-23 19:44:24,354 INFO   ||       For table 'AdventureWorks2017.Person.Address' using select statement: 'SELECT * FROM Person.Address'   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]
2018-10-23 19:44:24,833 WARN   ||  [Producer clientId=producer-4] Error while fetching metadata with correlation id 1 : {AdventureWorks2017.Person.AddressType=LEADER_NOT_AVAILABLE}   [org.apache.kafka.clients.NetworkClient]
2018-10-23 19:44:24,833 INFO   ||  Cluster ID: kMBEbuIBSPauMJrrIL6cag   [org.apache.kafka.clients.Metadata]
2018-10-23 19:44:25,002 WARN   ||  [Producer clientId=producer-4] Error while fetching metadata with correlation id 7 : {AdventureWorks2017.Person.Address=LEADER_NOT_AVAILABLE}   [org.apache.kafka.clients.NetworkClient]
2018-10-23 19:44:26,009 INFO   ||       Finished exporting 19614 records for table 'AdventureWorks2017.Person.Address'; total duration '00:00:01.654'   [io.debezium.relational.HistorizedRelationalSnapshotChangeEventSource]

[Mario Vera]

I would also add that you could confirm that the problematic table has being properly configured for CDC !

--
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/5799f272-4f53-4568-8a0f-1806d842d4bd%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[hansh1212]

I would assume that if the changes are appearing in the CDC.Person_Address_CT table that CDC is properly configured. 

[Jiri Pechanec]

Hi,

I'll take a look at it tomorrow. The database in question is part of the image or is available to download somewhere?

Thanks

J.

[hansh1212]

The AdventureWorks2017.bak file is available for download at https://github.com/Microsoft/sql-server-samples/releases.  

#docker cp ./AdventureWorks2017.bak $MSSQLContainerID:/var/opt/mssql/data/AdventureWorks2017.bak
#docker-compose -f docker-compose-sqlserver.yaml exec sqlserver bash -c '/opt/mssql-tools/bin/sqlcmd -U sa -P $SA_PASSWORD'
>USE [master]
>RESTORE DATABASE [AdventureWorks2017] FROM  DISK = N'/var/opt/mssql/data/AdventureWorks2017.bak' WITH  FILE = 1, 
>MOVE N'AdventureWorks2017' TO N'/var/opt/mssql/data/AdventureWorks2017.mdf', 
>MOVE N'AdventureWorks2017_log' TO N'/var/opt/mssql/data/AdventureWorks2017_log.ldf', 
>NOUNLOAD,  STATS = 5
>GO

[Jiri Pechanec]

Hi,

I've followed your steps deploying the database in question into our tutorial. I've got messages in both topics - AddressType and Address and the changes in the tables later on were duly streamed into the topics.

J.

[Henning von Schmeling]

Jiri, 

Thanks for running through the exercise.   Its encouraging to know that you were able to get it working properly.   A couple of questions for you...

1.  Were the topics fully populated with snapshot data?

2.  Would you be able to share with me what configuration you used for the connector?

3.  What is the easiest way to crank up the logging level, as I think I will need to do a deeper dive to try and figure out what is going on in my deployment.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/f5aec0ff-6687-4250-bdaf-07e8d9134374%40googlegroups.com.

[Jiri Pechanec]

Hi,

1. Technically I cannot confirm at 100 % that as I stopped the consumer after 40000 messages were cosnumed from Address topic but I suppose it should. I then switched to consuming from latest instead of earliest

2.

{

    "name": "inventory-connector4",

    "config": {

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

        "tasks.max" : "1",

        "database.server.name" : "server1",

        "database.hostname" : "sqlserver",

        "database.port" : "1433",

        "database.user" : "sa",

        "database.password" : "Password!",

        "database.dbname" : "AdventureWorks2017",

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

        "database.history.kafka.topic": "schema-changes.inventory",

        "table.whitelist": "Person.Address,Person.AddressType"

    }

}

3. The easiest is to set LOG_LEVEL env var to TRACE at deployment. Unfortunately it enables trace level for all loggers which becomes quite unmanageable. The other option is to login into container and modify config/log4.properties file. Then you need to restart the container. Beware! /docker-entrypoint.sh manipulates this file according to LOG_LEVEL env var so you might need to modify the script as well.

J.