debezium | Change data capture for a variety of databases | Change Data Capture library

The reason of empty values for all columns except the PK is not related to New Record State Extraction SMT at all. For postgres, there is a REPLICA IDENTITY table-level parameter that can be used to control the information written to WAL to identify tuple data that is being deleted or updated.

This parameter has 4 modes:

• DEFAULT
• USING INDEX index
• FULL
• NOTHING

In the case of DEFAULT, old tuple data is only identified with the primary key of the table. Columns that are not part of the primary key do not have their old value written.

In the case of FULL, all the column values of old tuple are properly written to WAL all the time. Hence, executing the following command for the target table will make the old record values to be properly populated in debezium message:

So generally speaking, Debezium does not recommend that you use multiple connectors per database source and prefer that you adjust your connector configuration instead. We understand that isn't always the case when you have different business use cases at play.

That said, it's important that if you do deploy multiple connectors you properly configure each connector so that it doesn't share state such as the same database history topic, etc.

For certain database platforms, having multiple source connectors really doesn't apply any real burden to the database, such as MySQL. But other databases like Oracle, running multiple connectors can have a pretty substantial impact.

When an Oracle connector streams changes, it starts an Oracle LogMIner mining session. This session is responsible for loading, reading, parsing, and preparing the contents of the data read in a special in-memory table that the connector uses to generate change events. When you run multiple connectors, you will have concurrent Oracle LogMiner sessions happening and each session will be consuming its own share of PGA memory to support the steps taken by Oracle LogMiner. Depending on your database's volatility, this can be stressful on the database server since Oracle specifically assigns one LogMiner session to a CPU.

For an Oracle environment, I highly recommend you avoid using multiple connectors unless you are needing to stream changes from different PDBs within the same instance since there is really no technical reason why you should want to read, load, parse, and generate change data for the same redo entries multiple times, once per connector deployment.

First we have to caveat this that "transactions" are a function of a particular engine. InnoDB is the primary engine used by people so I'll focus on that.

Yes, certainly there can be multiple transactions, because if there wasn't you would never have deadlocks.

But the binlog doesn't include anything that wasn't committed:

Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.

So by necessity, the transaction log is inherently serialized.

MariaDB has some InnoDB documentation that includes this:

You can modify data on a maximum of 96 * 1023 concurrent transactions that generate undo records. Of the 128 rollback segments, InnoDB assigns 32 to non-redo logs for transactions that modify temporary tables and related objects, reducing the maximum number of concurrent data-modifying transactions to 96,000, from 128.000. The limit is 32,000 concurrent transactions when all data-modifying transactions also modify temporary tables.

The purpose of the log is to be able to recover from a catastrophic loss, by being able to replay completed statements and transactions. If recovery goes through the transaction log and a transaction is never committed, that transaction isn't in the transaction log.

With debezium you are stuck with one table to one topic mapping. However, there are creative ways to get around it.

Based on the description, it looks like you have some sort of product that has SQL Server backend, and that has 500 tables. This product is being used by 500 or more clients and everyone has their own instance of the database.

You can create a connector for one client and read all 500 tables and publish it to Kafka. At this point you will have 500 Kafka topics. You can route the data from all other database instances to the same 500 topics by creating separate connectors for each client / database instance. I am assuming that since this is a backend database for a product, the table names, schema names etc. are all same, and the debezium connector will generate same topic names for the tables. If that is not the case, you can use topic routing SMT.

You can differentiate the data in Kafka by adding a few metadata columns in the topic. This can easily be done in the connector by adding SMTs. The metadata columns could be client_id, client_name or something else.

As for your other question,

Is it the best way to send SQL data to Kafka or should we send an event to Kafka queue through code whenever there is an insert/update/delete on a table?

The answer is "it depends!". If it is a simple transactional application, I would simply write the data to the database and not worry about anything else.

The answer is also dependent on why you want to deliver data to Kafka. If you are looking to deliver data / business events to Kafka to perform some downstream business processing requiring transactional integrity, and strict SLAs, writing the data from application may make sense. However, if you are publishing data to Kafka to make it available for others to use for analytical or any other reasons, using the K-Connect approach makes sense.

There is a licensed alternative, Qlik Replicate, which is capable of something very similar.

Oh, just found the solution in the wiki page at https://github.com/hadolint/hadolint/wiki/DL4006

Here is my fixed version:

The message says

logical decoding requires wal_level >= logical

In postgresql.conf you should set the folllowing:

Using OJDBC6.jar with all dependencies helped me to resolve the issue. And most importantly i placed the jars in connectors lib folder.

You need to set correctly rest.advertised.host.name (or CONNECT_REST_ADVERTISED_HOST_NAME, if you’re using Docker). This is how a Connect worker communicates with other workers in the cluster.

For more details see Common mistakes made when configuring multiple Kafka Connect workers by Robin Moffatt.

In your case try to remove CONNECT_REST_ADVERTISED_HOST_NAME=localhost from compose file.

Using debezium mysql connect for Kafka

You can set that to use AvroConverter, then the subject will be created automatically

Otherwise, you can have KSQL use VALUE_FORMAT=JSON and you need to manually specify all the field names. Unclear what difference you're asking about (they are different serialization formats), but from a KSQL perspective, JSON alone is seen as plain-text (similar to DELIMITED) and needs to be parsed, as compared to the other formats like Avro where the schema+fields are already known.