Error: The db history topic or its content is fully or partially missing.

[Nicolò Arrighini]

Hi all,

we've implemented Debezium on Azure AKS cluster, connected it to Azure Event Hub, capturing data changes on Azure SQL Server PaaS. Everything works fine but we randomly get some errors and need to recreate a new connector. Following more details:

we've deployed a CDC Debezium deployment on cluster AKS following this guide:

https://github.com/Azure-Samples/azure-sql-db-change-stream-debezium

The debezium deployment is capturing CDC events on SQLServer PaaS on Azure and transferring them as events in the event hub.

Debezium connector is working as expected, publishing CDC events to the event hub, which are consumed by other tools; however, after some time Debezium returns the following error:

ERROR || WorkerSourceTask{id=wwi4-0} Task threw an uncaught and unrecoverable exception. Task is being killed
and will not recover until manually restarted [org.apache.kafka.connect.runtime.WorkerTask]
io.debezium.DebeziumException: The db history topic or its content is fully or partially missing. Please check database history topic
configuration and re-execute the snapshot.

When we have this error, the CDC is not working. The only workaround we found out is to create a new Debezium connector, but ends with the same error after some time. It can be a few hours or a few days between the creation of the connector and this error.

This is the Connector:

{
    "snapshot.mode": "schema_only",
    "connector.class": "io.debezium.connector.sqlserver.SqlServerConnector",
    "database.hostname": "XXXXXXXXX",
    "database.port": "1433",
    "database.user": "XXXXX",
    "database.password": "XXXXXX",
    "database.dbname": "XXXXX",
    "database.server.name": "SQLAzure",
    "tasks.max": "1",
    "decimal.handling.mode": "string",
    "table.include.list": "XXXXXX,YYYYY,ZZZZZ",
    "transforms": "Reroute",
    "transforms.Reroute.type": "io.debezium.transforms.ByLogicalTableRouter",
    "transforms.Reroute.topic.regex": "(.*)",
    "transforms.Reroute.topic.replacement": "wwi",
    "tombstones.on.delete": false,
    "database.history": "io.debezium.relational.history.MemoryDatabaseHistory"
}

I think the problem is related to the parameter and value:  "database.history": "io.debezium.relational.history.MemoryDatabaseHistory"

From the Debezium documentation (https://debezium.io/documentation/reference/stable/operations/debezium-server.html#debezium-source-database-history-class)
we read that: io.debezium.relational.history.MemoryDatabaseHistory is a "volatile store for test environments"
while the other value that could be set to the parameter "database.history", io.debezium.relational.history.FileDatabaseHistory, is for non-Kafka deployments, like ours.

If we want to set io.debezium.relational.history.FileDatabaseHistory, we need to set also in the Debezium configuration, the parameter debezium.source.database.history.file.filename
(https://debezium.io/documentation/reference/stable/operations/debezium-server.html#debezium-source-database-history-file-filename), with which we indicate
"The name and location of the file to which FileDatabaseHistory persists its data."

However, as I set those parameters as:

"database.history": "io.debezium.relational.history.FileDatabaseHistory",
"debezium.source.database.history.file.filename": "/history/dbhistory.dat"

I get the error:

The 'database.history.file.filename' value is invalid: A value is required

So my questions are:

1) Can one avoid the error of History topic fully or partially missing by maintaining the value io.debezium.relational.history.MemoryDatabaseHistory and how?

2) Can one go in production environment with by maintaining the value io.debezium.relational.history.MemoryDatabaseHistory? I believe not, so:

3) If using io.debezium.relational.history.FileDatabaseHistory, how to avoid the above error ( The 'database.history.file.filename' value is invalid:) and also:

4) Where actually is the file history.dat created? In the DB?

Can you please help us?

Thank you,

kind regards from Italy,

Nicolò