Database Change Data Capture Adapter tries to start CDC at server ins spite of Unchecking the box

[Sharad Honavar]

I UNCHECKED the "Perform Enable CDC Calls" Checkbox on the CDC Adapter and also the "Start SQL Server Agent Service". But at start up of fragment, after a jdbc connect to the DB, the CDC Adapter still tries to start the service and Enable the Table for CDCand fails with the error message below. Even though CDC is already started for the DB and Table is enabled. The DB Table exists so do the CDC functions and structures. If I give it dbo access it will still fail because it is already started.

 

([5912:OperatorThread(default.CDCAdapter.reader)] INFO CDCAdapter: Setup CDC

[5912:OperatorThread(default.CDCAdapter.reader)] WARN CDCAdapter: Adapter is shutting down: Error enabling change data capture for table 'myDBTable': Object does not exist or access is denied.

(fragment deploy): java.lang.Exception: Error enabling change data capture for table 'myDBTable': Object does not exist or access is denied.

We do NOT want the application to enable or setup the CDC on the Table, only our DB ADmin has authority to do that at the SQL Server level, and the it checkbox unchecked does not work. Please help.

[Steve Barber]

Ah, the reason I didn't see this post yesterday is that you tagged it as #StreamBase but didn't set the product to "TIBCO Streaming"

 

I get notified by email for all "TIBCO Streaming" Answer posts, but get no notifications just for tags.

 

So, just a heads-up on that one.

[Steve Barber]

OK, I looked at this issue in the adapter, and I also read your Support Case.

 

I don't understand why the adapter is behaving as you have observed, as from what I can tell, it should not, and yes, disabling those options should result in, well, disabling those options.

 

So, this is definitely a good thing to pursue through Support.

 

If you want to avoid a round of back and forth with the Support team, go ahead and proactively attach to the Support Case (not here on Community please!!):

- your engine log file showing the INFO messages indicated CDC enablement and Service Agent startup attempts, redacting sensitive information if you need to. It's better to highlight the lines you think are relevant, and also send the whole log file rather than excerpting the log since you never know which lines Support and Engineering are going to want to see.

- your application code and config files exported as fragment and application projects from your workspace, redacting sensitive information if you need to. If it's not appropriate to send the whole project, at least do a "View Source" on the CDC Adapter instance and copy the XML into the ticket so that Support can see your property settings for the adapter unambiguously.

- for extra info, take a node snapshot and attach to the Support ticket. epadmin --servicename=blah.blah create snapshot . . . . https://docs.streambase.com/latest/topic/com.streambase.sb.ide.help/data/html/rtcmd/epadmin-snapshot.html

[Steve Barber]

OK, so I have some information here and maybe it'll help you investigate the issue on your end.

 

That exception message you are seeing "Error enabling change data capture for table" is misleading. The error message should say "Error setting up change data capture for table"

 

The error message for enabling looks like it was copy/pasted into the setup code, and sadly you get the same error message for both parts of the process, which is confusing.

 

If you had seen an INFO log message that said "Enabling CDC" then the checkbox indeed would not be working as designed. But "Setting up CDC" has nothing to do with the "Perform Enable CDC Calls" checkbox. Setting up for CDC is something the adapter needs to do every time it initializes so it can get the schema of the capture table for the, um, captured table.

 

Is there a stack trace in the engine log associated with the "java.lang.Exception: Error enabling change data capture for table 'myDBTable': Object does not exist or access is denied." message or nearby That would help locate which specific call was failing in the adapter.

 

Also, to setup CDC, the adapter is trying to call this:"{call sys.sp_cdc_get_captured_columns(@capture_instance=)}"

 

Could your issue be a permissions issue with calling that stored proc or in something the stored proc is trying to access

 

Perhaps we should take seriously that the adapter is literally reporting that "Error enabling change data capture for table 'myDBTable': Object does not exist or access is denied."

 

It's not entirely clear from this error message which "Object" the adapter (or that stored proc) is having trouble accessing, but that might be something to look at on your end, if possible.

 

That is, "Object does not exist or access is denied" is coming right back from some JDBC method call, and not from the adapter code itself.

 

I guess another possibility is that the capture instance for myDBTable was never created or is inaccessible Just throwing out ideas to check.

 

Hope this information helps. 

[Sharad Honavar]

Made some prgress but not an auth/enable CDC issue, updated Case ticket as folllows

 

My further testing is clearly pointing to the Adapter not being compatible with SQL 2019, the tibco doc for the adapter mentions upto 2016 and they are right. Here's why:

When I enter the "Capture Instance Name" field in eventflow's Adapter Properties the adpater tries to "enable" the DB/Table for CDC and fails for authority/invalidobject reasons as in my original reason which started this case. 

The sample leaves this field blank, so I tried with this. Now the log clearly reveals the problem being a SQL 2019 mismatch when I leave the field blank. I get the following error.

CDCAdapter: The following output schema fields are not present in the capture table: Id,  fld1,  fld2,  (redacted)
CDCAdapter: Error reading capture activity between LSNs 000000AD000000260001 and 000000AD000000260001, sql: SELECT __$operation AS "_Operation", __$start_lsn AS "_CommitSeqNum", __$seqval AS "_SubSeqNum", __$update_mask AS "_UpdateMask"  FROM cdc.fn_cdc_get_all_changes_tablename_capture_instance(, , 'all'): Invalid object name 'cdc.fn_cdc_get_all_changes_tablename_capture_instance'.
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'cdc.fn_cdc_get_all_changes_tablename_capture_instance'.


quoting your doc on the Capture Instance Name field in the Adapter properties - "if left blank the value will be generated using {tablename}_capture_instance". But The MS doc for 2019 says 
"By default, the (Capture) name(at the server) is <schema-name_table-name> of the 
source table. Its associated change table is named by appending _CT to the capture instance name. The function that is used to query for all changes is named by prepending fn_cdc_get_all_changes_ to the capture instance name."
I verified the Capture Name, change table name and Function Name at our  server on the SQL server using the sys.sp_cdc_help_change_data_capture function matches the MS doc but not  what the Streambase adapter is making calls to - the function it thinks is invalid object. 

So the adapter is looking for 
a) <{tablename}_capture_instance> when it should be looking for <schema-name_table-name> in 2019
b) <cdc.fn_cdc_get_all_changes_tablename_capture_instance> and SQL 2019 it should be <cdc.fn_cdc_get_all_changes_tablename_CT>

 

 

 

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-data-capture-sql-serverview=sql-server-ver15

[Sharad Honavar]

I got the Change Data Capture Adapter to work but a non ideal workaround for most practical situtuations for two reasons, unless I am missing something.

 

If the Adapter is tried on a SQL server Table for which CDC is already enabled externally through the Microsoft proc calls, sys.sp_cdc_enable_db and sys.sp_cdc_enable_table, and the Adapter "Perform Enable CDC" box is unchecked, then calls to *get_all_changes* fails because the names of the *get_all_changes* function created by the MS sys enable calls do not match the function names the Adapter uses, the suffixes are different than what MS creates and specified in their doc. As gleaned from the function call failure error messages. 

 

(more specifically SB ADapter creates "fn_cdc_get_all_changes_testCDC_capture_instance" and MS creates "fn_cdc_get_all_changes_myschema_testCDC" actually, as per the spec  fn_cdc_get_all_changes_<capture_instance> where capture_instance =myschema_testCDC)

 

If we let the ADapter do Perform Enable CDC, on a Table on which CDC is not enabled, it needs sysadmin rights, which is dubious for a client app for practical security reasons. So one work around is to give the tibco client user profile sysadmin rights, run the ADapter once to enable CDC at the server, and then relinquish the sysadmin rights, and run it with unchecked "Perform Enable CDC" for subsequent runs. Obviously this will not work if the table is already enabled through MS calls and used by other apps outside of Streambase who already use the MS spec function names.

 

The 2nd more problematic issue is Streambase CDC Adpater, prepends the default out of box "dbo" schema to the Table Name field, so if my Table is using another schema, which is 90% of the time, it tries to access dbo.mySchema.myTable. So unless the table is in the dbo schema and you enter just the table name for the Table Name Adapter input, it will not work. We already have 1000s of tables assigned to 4-5 schemas accessed by 100s of apps, and cannot change this and put everything in the dbo schema..

 

 

[Steve Barber]

Hi shonavar,

 

This is all great feedback for the Streaming product team, for some combination of defect reports and/or enhancement requests. I can see that you've already included this information in your Support Case on the same topic, and that indeed is the best channel to further pursue these issues to resolution.

 

I guess this is all good information for any readers of this Answer thread wanting to use the CDC Adapter as of 10.6.1. Hopefully, the product team will be addressing your issues and things will move on and any adapter limitations described in this post will soon be out of date!