SQL Server connector issue, Producer failure/SQLServerException
Anthony Limon
Hi,
I've never had an issue with my connector up until recently, I'll attach the full logs. The obvious error is (sorry for the weird formatting):
| 1730914142000 | [Worker-0d9964637a1c47fbf] [2024-11-06 17:29:02,764] ERROR [my-connector|task-1] Producer failure (io.debezium.pipeline.ErrorHandler:38)
| 1730914142000 | [Worker-0d9964637a1c47fbf] io.debezium.DebeziumException: com.microsoft.sqlserver.jdbc.SQLServerException: An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
| 1730914142000 | [Worker-0d9964637a1c47fbf] at io.debezium.pipeline.source.AbstractSnapshotChangeEventSource.execute(AbstractSnapshotChangeEventSource.java:85)
| 1730914142000 | [Worker-0d9964637a1c47fbf] at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:155)
| 1730914142000 | [Worker-0d9964637a1c47fbf] at io.debezium.connector.sqlserver.SqlServerChangeEventSourceCoordinator.executeChangeEventSources(SqlServerChangeEventSourceCoordinator.java:71)
| 1730914142000 | [Worker-0d9964637a1c47fbf] at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:109)
| 1730914142000 | [Worker-0d9964637a1c47fbf] at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
| 1730914142000 | [Worker-0d9964637a1c47fbf] at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
| 1730914142000 | [Worker-0d9964637a1c47fbf] at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
| 1730914142000 | [Worker-0d9964637a1c47fbf] at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
| 1730914142000 | [Worker-0d9964637a1c47fbf] at java.base/java.lang.Thread.run(Thread.java:829)
which is preventing the change data to be produced. I'm not sure what would've caused this. For more context, before the issue occurred there was a CA cert rotation performed on the SQL Server DB a day or two before, and the DB was restarted. During this activity the only thing that was done on the DB side was the grant of the EXECUTION permission on SPs to the same DB user our connector uses.
- msdb.dbo.rds_dms_tlog_download
- msdb.dbo.rds_dms_tlog_read
- msdb.dbo.rds_dms_tlog_list_current_lsn
- msdb.dbo.rds_task_status
I also have an AWS DMS ongoing replication task that writes this CDC data to S3, and was able to confirm the # of records in the source DB matched what we were seeing in S3, confirming all was well after the CA cert rotation as both the DMS Task and Connector are capturing changes from the same tables.
While digging into why the connector wasn't working I went back through the docs to make sure everything was set up correctly (https://debezium.io/documentation/reference/stable/connectors/sqlserver.html#setting-up-sqlserver), considering my DMS task is also replicating the change data and requires CDC to be enabled on the SQL Server DB and tables (https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.CDC.html#CHAP_Source.SQLServer.Configuration), I didn't expect there to be anything out of the ordinary. I decided to check the if the change table on the SQL Server had recent entries. Using cdc.fn_cdc_get_all_changes_<capture_instance> I did not see any recent data which is strange considering the data coming over via DMS is fresh, I also did not see any errors (sys.dm_cdc_errors). Please let me know if I missed anything, thanks in advance!
