Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.2k views
in Technique[技术] by (71.8m points)

apache kafka - Debezium: No maximum LSN recorded in the database; please ensure that the SQL Server Agent is running

This question is related to: Debezium How do I correctly register the SqlServer connector with Kafka Connect - connection refused

In Windows 10, I have Debezium running on an instance of Microsoft SQL Server that is outside of a Docker container. I am getting the following warning every 390 milliseconds:

No maximum LSN recorded in the database; please ensure that the SQL Server Agent is running
[io.debezium.connector.sqlserver.SqlServerStreamingChangeEventSource]

I checked Debezium's code on Github and the only place that I can find this warning states in the code comments that this warning should only be thrown if the Agent is not running. I have confirmed that the SQL Server Agent is running.

Why is this warning showing up and how do I fix it?

Note:

My current solution appears to only work in a non-production environment - per Docker's documentation.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

LSN is the "pieces" of information related about your SQL Server changes. If you don't have LSN, is possible that your CDC is not running or not configured properly. Debezium consumes LSNs to replicate so, your SQL Server need to generate this.

Some approaches:

  1. Did you checked if your table are with CDC enabled? This will list your tables with CDC enabled:
SELECT s.name AS Schema_Name, tb.name AS Table_Name
, tb.object_id, tb.type, tb.type_desc, tb.is_tracked_by_cdc
FROM sys.tables tb
INNER JOIN sys.schemas s on s.schema_id = tb.schema_id
WHERE tb.is_tracked_by_cdc = 1
  1. Your CDC database are enabled and runnig? (see here)

Check if enabled:

SELECT * 
FROM sys.change_tracking_databases 
WHERE database_id=DB_ID('MyDatabase')

And check if is running:

EXECUTE sys.sp_cdc_enable_db;  
GO  
  1. Your CDC service are running on SQL Server? See in docs
EXEC sys.sp_cdc_start_job;  
GO  
  1. On enabling table in CDC, I had some issues with rolename. For my case, configuring at null solved my problem (more details here)
    EXEC sys.sp_cdc_enable_table
        @source_schema=N'dbo',
        @source_name=N'AD6010',
        @capture_instance=N'ZZZZ_AD6010',
        @role_name = NULL,
        @filegroup_name=N'CDC_DATA',
        @supports_net_changes=1
     GO

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...