Unable perform CDC on the Mysql DB which is month old , Getting Bin Log Exception.

[nikhil channa]

Hi Team,

I am trying to perform cdc replication using debezium Kafka connect.
We are using secondary replica for cdc. My database  month old where log retention is 5 days.  I am trying to run a connector ,getting below error. On Newly Created Datasbe Connector  works fine.


Exception In Connector logs:

org.apache.kafka.connect.errors.RetriableException: An exception occurred in the change event producer. This connector will be restarted.
2024-10-08 21:36:56     at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:63)
2024-10-08 21:36:56     at io.debezium.connector.binlog.BinlogStreamingChangeEventSource$ReaderThreadLifecycleListener.onCommunicationFailure(BinlogStreamingChangeEventSource.java:1235)
2024-10-08 21:36:56     at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:1101)
2024-10-08 21:36:56     at com.github.shyiko.mysql.binlog.BinaryLogClient.connect(BinaryLogClient.java:653)
2024-10-08 21:36:56     at com.github.shyiko.mysql.binlog.BinaryLogClient$7.run(BinaryLogClient.java:954)
2024-10-08 21:36:56     at java.base/java.lang.Thread.run(Unknown Source)
2024-10-08 21:36:56 Caused by: io.debezium.DebeziumException: Could not find first log file name in binary log index file Error code: 1236; SQLSTATE: HY000.
2024-10-08 21:36:56     at io.debezium.connector.binlog.BinlogStreamingChangeEventSource.wrap(BinlogStreamingChangeEventSource.java:1190)
2024-10-08 21:36:56     ... 5 more
2024-10-08 21:36:56 Caused by: com.github.shyiko.mysql.binlog.network.ServerException: Could not find first log file name in binary log index file
2024-10-08 21:36:56     at com.github.shyiko.mysql.binlog.BinaryLogClient.listenForEventPackets(BinaryLogClient.java:1065)
2024-10-08 21:36:56     ... 3 more


Connector Config:
Note: Tried with Initial and when_needed.

{

"name": "cdc_connector_test",

"config": {

"connector.class": "io.debezium.connector.mysql.MySqlConnector",

"transforms.db_pipeline_done.type": "org.apache.kafka.connect.transforms.InsertHeader",

"transforms.Reroute.regex": ".*",

"snapshot.new.table": "parallel",

"transforms": "Reroute,unwrap",

"transforms.db_fleet_id.header": "db_fleet_id",

"include.schema.changes": "true",

"transforms.unwrap.drop.tombstones": "true",

"poll.interval.ms": "700",

"transforms.unwrap.type": "io.debezium.transforms.ExtractNewRecordState",

"database.user": "<DB_USER>",

"database.dbname": "<ADD YOU DB>",

"schema.history.internal.kafka.bootstrap.servers": "<Kafka_BOOTSTRAP>",

"transforms.unwrap.add.fields.prefix": "",

"producer.override.max.request.size": "15728640",

"database.password": "<DB_PASSWORD>",

"name": "cdc_connector_test",

"max.batch.size": "10000",

"snapshot.mode": "initial",

"max.queue.size": "15000",

"transforms.Reroute.replacement": "raw.topic",

"transforms.unwrap.delete.handling.mode": "rewrite",

"schema.history.internal.store.only.captured.databases.ddl": "false",

"topic.prefix": "cdc_connector_test_prefix",

"schema.history.internal.kafka.topic": "schema-changes.mydatabase",

"transforms.Reroute.type": "org.apache.kafka.connect.transforms.RegexRouter",

"database.server.id": "69398157",

"time.precision.mode": "connect",

"database.server.name": "cdc_connector_test.0",

"offset.flush.timeout.ms": "6000",

"database.port": "3306",

"offset.flush.interval.ms": "60000",

"transforms.db_log_type.value.literal": "json",

"database.hostname": "<ADD YOU DB>",

"transforms.unwrap.add.fields": "op,ts_ms,source.db:db,source.table:table",

"table.include.list": "<ADD YOU DB>.<ADD YOU TABLE>,<ADD YOU DB>.<ADD YOU TABLE>,..."

}

}

[Chris Cranford]

Hi -

Please try removing the offsets for the connector and use "snapshot.mode" set to "initial". The issue is appears that the binlog filename/position in the offsets the connector wishes to resume from no longer exists.

Thanks,
-cc

[nikhil channa]

Thank you for your response Chris. I'm new to Debezium and would appreciate any guidance on how to remove the offsets for a connector. 

I’m encountering issues where the same configuration works for new databases, 

but fails for older databases (several months or years old) when setting up a new connector.

Thanks 
NC.

--
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/5a637969-9afe-4efa-b52c-cbd6a9f1e7df%40gmail.com.

[nikhil channa]

Hi Chris,
As per suggestion, I have stopped the connector and later deleted using APIs.  I am still facing similar issues.
I have also tried running a new connector it is also failing due to a binlog exception.

[Chris Cranford]

Hi -

If you are running the connector on Kafka Connect, there are a couple ways to handle clearing the offsets. The first is to follow the instructions in the FAQ [1] to remove the offsets manually. Please be sure to do this when the connector has been deleted. The second is to simply delete and re-create the connector using a new "name" in the connector JSON details. Since the "name" is part of the key in the offset store, simply changing the connector name will force the connector to think its new. In both cases, be sure to remove the schema history topic before restarting the connector.

If you are using the file-based offset and schema history stores or any other storage implementations, you'll need to follow a different set of steps, such as deleting the offset and schema history storage files when using the file-based storage implementations.

Hope that helps,
-cc

[1]: https://debezium.io/documentation/faq/#how_to_remove_committed_offsets_for_a_connector

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CA%2BPWsUeUHn0Csfjuj7f53fKt9qsA78yw08PQWWfpQq2o%2BFQ0jA%40mail.gmail.com.

[nikhil channa]

Thank you Chris this helped me a lot.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/93970991-636a-40d1-b67a-c2d813f2f02f%40gmail.com.