The db history topic or its content is fully or partially missing. Please check database history topic configuration and re-execute the snapshot

[Logu Venkatachalam]

I see this thread in the Confluence developer forum.

https://forum.confluent.io/t/debezium-the-db-history-topic-or-its-content-is-fully-or-partially-missing-please-check-database-history-topic-configuration-and-re-execute-the-snapshot/4614

We see this exception often in our logs. After this point the Connector stopped and no events flow after that.

As the above URL suggests recreating the connector with different name seem to work, but that is not a solution for us.

Any suggestions how we can fix this?

Logu

[Logu Venkatachalam]

Today we upgraded to debezium 1.9.2 and the exception

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.

started appearing immediately.

Any suggestion how we can fix this?

[CCHsu]

Hi, Logu,

As far as I know, the only way I can find is to use a different task name when trying DBZ for SQL Server, 

and I also find it not good at the first glance.

The current steps I find acceptable is outlined as below.

1) `curl -X DELETE /connectors/{name}` the current task 

2) Rename the task (`name` property)

3) Additionally set `"snapshot.mode": "schema_only"` property

4) Load the "new" task with `curl -X POST /connectors`

5) Check the kafka connect log to confirm the issue solved

6) Remove `"snapshot.mode": "schema_only"` property and reload the "new" task with `curl -X PUT /connectors/{new_name}`

Disclaimer: I am also on the way trying/testing DBZ.  😅

log...@gmail.com 在 2022年6月21日 星期二清晨7:42:03 [UTC+8] 的信中寫道：

[Chris Cranford]

Hi Logu -

All Debezium relational connectors except PostgreSQL require a database history topic in order to function.  This topic must be configured with the correct retention, partition, and clean-up policies based on the documentation [1].  I would first start by checking the configuration of the history topic with what we outline in the documentation and adjust those settings if they're not correct.  This is more often than not the reason for that error message on restart.  The less common scenario is someone inadvertently removed the history topic from Kafka and if that happened, you may be able to repair it by using "snapshot.mode=schema_only_recovery" if the connector involved supports that mode and no schema changes have occurred since the last execution of the connector.  If you can't guarantee that point about schema changes, then unfortunately you'll need to redeploy the connector.

A redeploy of the connector does not necessarily require an initial snapshot.  If you are on the latest versions of the connector, you should be able to use a schema-only snapshot mode (if the connector supports it) to begin streaming changes from the current time and then trigger an incremental snapshot using a signal (requires configuring signals) and that would capture the changes missed from the last successful execution and the time you redeployed.

Hope that helps,
CC

[1]: https://debezium.io/documentation/reference/stable/install.html#_configuring_debezium_topics

--
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 view this discussion on the web visit https://groups.google.com/d/msgid/debezium/3f74bfa0-784d-4b0f-b547-fe122ad8696dn%40googlegroups.com.

[Logu Venkatachalam]

Thanks Chris. I am going to check this. Checked the document you gave. It is not clear how to set this up from the doc.

Do you have a sample configuration for SQLServer? That will help a lot.

[Chris Cranford]

Hi Logu -

The history topic configuration is universal and isn't specific to a connector (outside of the fact that PG doesn't use it naturally).  What you want to confirm at the topic side is the following:

    - Infinite retention policy
    - No compaction
    - Single partition

If that isn't the case, this will impact the validity of the history topic and lead to problems.

Hope that helps
CC

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/30af4bf1-0f72-49c3-a712-bd3ad596b908n%40googlegroups.com.

[Logu Venkatachalam]

Thanks Chris for the reply. I am new to debezium and I really do not understand what you are saying. I need to read about debezium. Let me read more and see if can follow these steps.

[Logu Venkatachalam]

Chris, we are using Azure EventHub. Just came to know that EventHub does not support Infinite retention policy. It only supports max 60 days. Is there a workaround for this combination?

[Chris Cranford]

Logu, you could configure the database history topic configuration to either a Kafka-based topic or you could point to a file on the filesystem if you have access to a persisted volume.  See the debezium.source.database.history configuration options found here: https://debezium.io/documentation/reference/stable/operations/debezium-server.html#debezium-source-database-history-class

Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/53d44b92-9d2e-4b8d-83be-48adcce9cbc8n%40googlegroups.com.

[Logu Venkatachalam]

Thanks Chris. We applied these configs and they were rejected as "Unrecognized config variables". Later found in the document you sent these are for Debezium Server.

This is our setting

SQL Server -> Debezium SQL Server connector (we are just using the jar files) -> Kafka Connect -> Event Hub.

Looking for instructions to setup database history topic for this environment. I read through the document online and I am unable to figure this out. I would appreciate any and every help on this.

[Chris Cranford]

Hi Logu -

I apologize for the confusion, you mentioned EventHubs not knowing about retention policies so I just assumed you were using Debezium Server + EventHub sink. 

But I think based on what you have provided, the same philosophy holds true.  As I understand it, EventHubs has a pretty strict retention policy and I believe the default is something like 24 hours, which won't work for database history topics at all.  So what you will need to do is explicitly configure the database history to be stored in a local file or in a separate Kafka broker from EventHubs.

The first option you need to adjust is this:

    database.history

This uses the "io.debezium.relational.history.KafkaDatabaseHistory" implementation by default.  You have two options here.  The first is to change this to use the file-based implementation, io.debezium.relational.history.FileDatabaseHistory class name.  This will require you have a shared volume available across the Kafka Connect cluster so that the connector task can access the file.  If you'd rather keep the history topic in a remote topic, you could also consider standing up a separate Kafka broker where you store just the history topic with infinite retention since EventHubs does not offer that.  For this you would need to adjust the broker host/ip and credentials using the database.history.* settings pass those through to the underlying Kafka Connect APIs like you would when configuring credentials and SSL communications with the broker.  See the pass-thru section at https://debezium.io/documentation/reference/stable/connectors/sqlserver.html#debezium-sqlserver-connector-database-history-configuration-properties

Hope that helps and let us know if you have any other questions.
CC

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/593902c9-f87f-4171-ae34-2d4ea497f213n%40googlegroups.com.

[Logu Venkatachalam]

Wow! Thank you so much, Chris! I am going to try this today.

[Silver 7331]

Hi Chris, 

out of interest,

why must the history topics be created with infinite retention and a single partition?

[Ruben Wolff]

Did the debezium.relational.history.FileDatabaseHistory approach end up working for you ?  I have not found much documentation on how to implement it. 

[Chris Cranford]

Hi,

A single partition allows the topic's messages to have their order retained regardless of the key used.  If multiple partitions were allowed, order would only be guaranteed based on each unique key and we don't want that.  By using a single partition, all DDL history events written to the topic will always be written in chronological order and consumed the same way.

The infinite retention policy is a bit more complicated and in-depth.  In short, its used to guarantee that the connector has a lifetime record of all the schema changes applicable to the source database, and more importantly for the tables that you're capturing changes for.  A database's schema can change at any time and the connector needs to be able to identify what the table's schema was when we get a change event.  We can't rely on the current table's structure because we may be processing events that are relatively old, which could have occurred before the current table's schema was altered.  When the connector restarts, the connector uses this topic to rebuild table structures in-memory so that when we begin processing changes from where we left off, we know exactly what the table's schema was at that moment in time, even if that time point was minutes, hours, or even days prior.

Hope that helps clarify.
Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/df3afb53-a018-4560-b74d-0c45f85c0fb4n%40googlegroups.com.

[Silver 7331]

Thank you for clarifying Chris.

[Patrick Herrera]

Hi Chris, we have started getting this "fully or partially missing" error also, and I quickly realised that it is probably because we didn't have infinite retention (although now that I've got to the point of looking at the source code for a solution, I don't understand why this was never logged as a warning and instead we got "Database history topic 'X' has correct settings" when clearly we didn't).

I've fixed the retention but the Connector still fails to start and I can't see how to resolve without starting from scratch with a new Connector and topics.  We are using Microsoft SQL Server, so the SCHEMA_ONLY_RECOVERY option is not available.

Is there anything else we can try before going to the nuclear option?

Thanks,

Patrick

[Chris Cranford]

Hi Patrick,

So that seems odd regarding the retention of the history topic.  What exactly was the retention previously in milliseconds?  Is it possible it wasn't infinite but exceeded 5 years? 

Regarding the latter part of your question, one option could be to leverage a temporary connector where you run "snapshot.mode=schema_only" and push the latest schema into a new history topic.  Once you've stopped this temporary connector after the schema has been published, you could attempt to reconfigure your real connector to use this history topic instead.  Now keep in mind, this is only advisable if there have been on schema changes since the table the real connector stopped and the present. 

Hope that helps.
Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/9d8b6f9e-12c3-4372-88a4-3de6c3e3539bn%40googlegroups.com.

[Patrick Herrera]

Hi Chris, thanks for that suggestion - it actually worked perfectly and because the history topic on the original connector was empty anyway since the retention period had passed, it was a simple matter to configure the new "schema_only" connector to update the history topic on the original connector directly.

So for anyone else, this is what we did:

1. Create a duplicate Connector for each failing Connector, with unique names to ensure it didn't conflict (we have a Terraform module to configure each Connector (using the Mongey Kafka Connect Provider) and associated resources so it was simple to supply a different prefix that is used in the naming)
   1. Ensure the new Connector is configured with: snapshot.mode=schema_only
   2. We had to hack our module a little to allow us to override the database.history.kafka.topic value to retain the same name as the original Connector
   3. Ensure the Kafka identity used by the new Connector has required permissions on the old DB History Topic
2. Start the new Connector and it filled up the DB History Topic with the current state of the DB schema
3. The old Connectors picked up the fact the topic was no longer empty and started running and recent changes started to flow through again
4. Delete the old Connector and associated resources

The problem at that point is that CDC in SQL Server appears to default to retaining 72 hours of history, so we didn't magically get all of the changes since the Connectors stopped worked and had to signal a refresh. Which I believe means that even if the schema had changed at some point, the refresh would work since it would match the schema that we just recreated.

Still puzzled on why we weren't warned earlier that our topic was not configured correctly (we had 30 day retention configured, which I triple checked).  One thing I did think I noticed is that while working this out, I hadn't done the step of granting the Kafka identity of the new Connector the right ACL to see the old DB history topic, yet the Connector still said the topic was OK but then failed at some other point (sorry I should have made more detailed notes).  I note that the methods in the Kafka Client library throw checked exceptions for authorisation errors and I saw nothing in the Debezium code to suggest that those exceptions were swallowed or turned into something else.

We are on the latest Debezium 1.9 version.

Cheers,

Patrick

[Chris Cranford]

Hi Patrick -

There has been a check in the connector code base since 1.2, and the WARN message in question is "Database schema history topic '{}' option '{}' should be '{}' or greater than '{}' (5 years) but is '{}".  Is it possible this has been logged but overlooked in the logs?  There could be other INFO log entries that might prevent this from being written to the logs, which include these checks:

    * INFO: Expected one topic '{}' to match the query but got {}
    * INFO: Could not get config for topic '{}'
    * WARN: Database schema history topic '{}' option '{} should be '{}' but is '{}'

Perhaps the connector has complained about multiple topics, multiple configs, or "retention.bytes" as those checks come before the check for "retention.ms".  If you haven't seen any of those, could I ask what version of Kafka Connect and Kafka broker is in your environment?

Thanks,
Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/ce8a2ac1-8616-4306-b2b2-a298aa813cfan%40googlegroups.com.

[Shawn He]

I am just wondering if we can make a change to the connector to separate the history topic part out, and add an option to use file storage instead of kafka for the history changes, the file storage option is supported inside the engine, it could be just a matter of expose it inside the individual connectors. 

[Chris Cranford]

Hi Shawn -

This feature already exists by "schema.history.internal" to the fully qualified class name to the implementation you want to use.  So using Debezium 2.1 here, the following configuration would use the file-based schema history approach:

    schema.history.internal=io.debezium.storage.file.history.FileSchemaHistory
    schema.history.internal.file.filename=/tmp/schema-history.dat

If you're using Debezium 1.9 or earlier, replace "schema.history.internal." with "database.history.", everything else remains the same.

Hope that helps.
Chris

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/6ae5770e-b573-41dc-96e9-43b294267f22n%40googlegroups.com.