debezium unable to start if the binlog is purged

[ronnie10]

Hi,

I am facing this issue as the mysql binlog is purged, i have seen there is another post, when there snapshot.mode is set to schema_only_recovery, I get the following error:

 Connector requires binlog file 'mysql-bin.000003', but MySQL only has mysql-bin.000041, mysql-bin.000042 (io.debezium.connector.mysql.MySqlConnectorTask:330)
[2018-10-03 12:48:43,752] INFO Stopping MySQL connector task (io.debezium.connector.mysql.MySqlConnectorTask:245)
[2018-10-03 12:48:43,752] INFO WorkerSourceTask{id=debezium-mysql-0} Committing offsets (org.apache.kafka.connect.runtime.WorkerSourceTask:397)
[2018-10-03 12:48:43,752] INFO WorkerSourceTask{id=debezium-mysql-0} flushing 0 outstanding messages for offset commit (org.apache.kafka.connect.runtime.WorkerSourceTask:414)
[2018-10-03 12:48:43,753] ERROR WorkerSourceTask{id=debezium-mysql-0} Task threw an uncaught and unrecoverable exception (org.apache.kafka.connect.runtime.WorkerTask:177)
org.apache.kafka.connect.errors.ConnectException: The connector is trying to read binlog starting at binlog file 'mysql-bin.000003', pos=715349422, skipping 982 events plus 40 rows, but this is no longer available on the server. Reconfigure the connector to use a snapshot when needed.

if I set it to snapshot.mode=never, i got the following error:

[2018-10-03 12:56:07,391] INFO WorkerSourceTask{id=debezium-mysql-0} flushing 0 outstanding messages for offset commit (org.apache.kafka.connect.runtime.WorkerSourceTask:414)
[2018-10-03 12:56:07,391] ERROR WorkerSourceTask{id=debezium-mysql-0} Task threw an uncaught and unrecoverable exception (org.apache.kafka.connect.runtime.WorkerTask:177)
org.apache.kafka.connect.errors.ConnectException: The db history topic is missing. You may attempt to recover it by reconfiguring the connector to SCHEMA_ONLY_RECOVERY
    at io.debezium.connector.mysql.MySqlConnectorTask.start(MySqlConnectorTask.java:90)

either way won't work for me, any other settings I can try?

[Jiri Pechanec]

Hi,

please try a different name for schema changes topic and use schema_only_recovery snapshot mode

J.

[ronnie10]

Hi Jiri,

I don't quite get it, is it database.history.kafka.topic?

Ronnie10

[Jiri Pechanec]

Hi,

yes exactly, that's the one.

J.

[ronnie10]

Hi jiri,

I have changed it, the error looks like this:

[2018-10-04 08:54:14,553] INFO Connector requires binlog file 'mysql-bin.000003', but MySQL only has mysql-bin.000041, mysql-bin.000042 (io.debezium.connector.mysql.MySqlConnectorTask:330)
[2018-10-04 08:54:14,553] INFO Stopping MySQL connector task (io.debezium.connector.mysql.MySqlConnectorTask:245)
[2018-10-04 08:54:14,553] INFO WorkerSourceTask{id=debezium-mysql-0} Committing offsets (org.apache.kafka.connect.runtime.WorkerSourceTask:397)
[2018-10-04 08:54:14,553] INFO WorkerSourceTask{id=debezium-mysql-0} flushing 0 outstanding messages for offset commit (org.apache.kafka.connect.runtime.WorkerSourceTask:414)
[2018-10-04 08:54:14,554] ERROR WorkerSourceTask{id=debezium-mysql-0} Task threw an uncaught and unrecoverable exception (org.apache.kafka.connect.runtime.WorkerTask:177)

org.apache.kafka.connect.errors.ConnectException: The connector is trying to read binlog starting at binlog file 'mysql-bin.000003', pos=715349422, skipping 982 events plus 40 rows, but this is no longer available on the server. Reconfigure the connector to use a snapshot when needed.

    at io.debezium.connector.mysql.MySqlConnectorTask.start(MySqlConnectorTask.java:84)
    at io.debezium.connector.common.BaseSourceTask.start(BaseSourceTask.java:45)
    at org.apache.kafka.connect.runtime.WorkerSourceTask.execute(WorkerSourceTask.java:198)
    at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:175)
    at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:219)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
[2018-10-04 08:54:14,555] ERROR WorkerSourceTask{id=debezium-mysql-0} Task is being killed and will not recover until manually restarted (org.apache.kafka.connect.runtime.WorkerTask:178)

Ronnie

[Jiri Pechanec]

Hi,

what happens if you use snapshot mode `when_needed`?

J.

[Ronnie10]

Hi Jiri,

After I changed connect-mysql.properties to following:

name=debezium-mysql
connector.class=io.debezium.connector.mysql.MySqlConnector
database.hostname=ipaddress
database.port=3306
database.user=user
database.password=password
database.server.id=1
database.server.name=localhost
database.history.kafka.bootstrap.servers=localhost:9092
database.history.kafka.topic=testintsqlxx
database.zeroDateTimeBehavior=convertToNull
decimal.handling.mode=string
snapshot.mode=when_needed

I am still getting the same error:

[2018-10-12 09:47:32,587] ERROR WorkerSourceTask{id=debezium-mysql-0} Task threw an uncaught and unrecoverable exception (org.apache.kafka.connect.runtime.WorkerTask:177)

org.apache.kafka.connect.errors.ConnectException: The db history topic is missing. You may attempt to recover it by reconfiguring the connector to SCHEMA_ONLY_RECOVERY
    at io.debezium.connector.mysql.MySqlConnectorTask.start(MySqlConnectorTask.java:90)

    at io.debezium.connector.common.BaseSourceTask.start(BaseSourceTask.java:45)
    at org.apache.kafka.connect.runtime.WorkerSourceTask.execute(WorkerSourceTask.java:198)
    at org.apache.kafka.connect.runtime.WorkerTask.doRun(WorkerTask.java:175)
    at org.apache.kafka.connect.runtime.WorkerTask.run(WorkerTask.java:219)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

[2018-10-12 09:47:32,588] ERROR WorkerSourceTask{id=debezium-mysql-0} Task is being killed and will not recover until manually restarted (org.apache.kafka.connect.runtime.WorkerTask:178)

Ronnie

[Jiri Pechanec]

Hi,

so this is the worst case. I am afraid you need to go through the full snapshot now. If you could remove the connector and register it under a new name it should re-execute the snapshot.

The other option is to manually update the offsets stored to the most recent binlog available like descibed in https://debezium.io/docs/faq/#how_to_change_the_offsets_of_the_source_database

J.

[Boris Shilov]

Greetings folks,

I am getting the same sequence of errors today with my MySQL tracking connector (version 1.2.0). While I will perform a full snapshot, would it be possible to have some context on why this occurs and how to avoid this in the future? For context this happened after the database I am tracking has been restarted.

Thanks & regards,

Boris

[jiri.p...@gmail.com]

Hi,

either https://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_expire_logs_days is set to a small value or an external process or database admin is calling https://dev.mysql.com/doc/refman/5.6/en/purge-binary-logs.html while Debezium is performing snapshot or the connector is down. In this case a part of binlog that was not seen by Debezium might be purged and that's the root cause of the problem.

J.