db_replication | Change Data Capture library

In Azure SQL Database the capture and cleanup SQL Server Agent jobs are replaced by a change data capture scheduler that periodically invokes stored procedures to capture and cleanup of the change tables. This scheduler runs stored procedures automatically.

You can check this document to understand how the Capture job initiates the running of stored procedures.

I built a little test for this, and yes, I saw the same thing. It took me a couple of minutes to figure out what was going on.

The "gotcha" is this little entry in the docs:

If other entries in cdc.lsn_time_mapping share the same commit time as the entry identified by the new low watermark, the smallest LSN associated with that group of entries is chosen as the low watermark.

In other words, if the result of sys.fn_cdc_get_max_lsn() maps to a cdc.lsn_time_mapping.tran_begin_time that also has other start_lsn values associated with it, then the cleanup proc won't actually use the value of sys.fn_cdc_get_max_lsn() as the new low water mark.

In other other words, if the max lsn currently in the change table you want to clean up has the same tran_begin_time as other LSN's, and it is not the lowest of those LSNs, you cannot get a "complete" cleanup of the change table.

The easiest way to get a complete cleanup in those cases is probably to make a minor change to the target table to advance the max lsn and force a new entry, and "hope" that the new entry isn't also associated with any other LSNs with the same tran begin time.

To make that more explicit, here's my little test. Running it over and over has a result that in some cases cleanup is predicted to fail (and fails) and in other cases it is predicted to succeed (and succeeds).

You can use Vue transitions (see https://v3.vuejs.org/guide/transitions-enterleave.html#transitioning-single-elements-components).

Using a tag with name, and the element with key (each change to that key will trigger a transition update)

Found some options.

Postgres JDBC driver is this https://github.com/pgjdbc/pgjdbc/blob/master/pgjdbc/src/main/java/org/postgresql/core/Utils.java and other impl. is even more simple https://github.com/p6spy/p6spy/blob/master/src/main/java/com/p6spy/engine/common/Value.java#L172 literally doing

The BSON timestamp value is a 64 bits number where the first 32 bits denote a count of seconds since the Unix epoch of 1970-01-01 at 00:00 UTC.

Given below is an excerpt from the mongoDB documentation:

Timestamps

BSON has a special timestamp type for internal MongoDB use and is not associated with the regular Date type. This internal timestamp type is a 64 bit value where:

• the most significant 32 bits are a time_t value (seconds since the Unix epoch)
• the least significant 32 bits are an incrementing ordinal for operations within a given second.

So for your example:

R2DBC is primarily a specification to enable reactive/non-blocking communication with your database. What an R2DBC driver is capable of pretty much depends on your database.

R2DBC specifies a set of interfaces including methods where every database conversation is activated through a Publisher. R2DBC has no opinion on the underlying wire protocol. Instead, a database driver implementing R2DBC has to stick to its database communication protocol. What you get through JDBC or ODBC is pretty much the same as what you can expect from an R2DBC driver.

There are smaller differences: some JDBC drivers require polling for data (such as Postgres Pub/Sub notification) whereas, in R2DBC, a notification stream can be consumed without a polling thread as all I/O is based on listening on the receive buffers and emitting data once the driver receives data. In contrast, JDBC (and pretty much all imperative API) require someone to call a method to consume/obtain data.

I'm not sure how CDC works with MySQL; I think you need to scan (poll) the BINLOG using MySQL commands or the MySQL protocol. Right now, the R2DBC MySQL driver doesn't support BINLOG polling.

Postgres has similar functionality (Logical Decode). It is supported by R2DBC Postgres (see the documentation of Logical Decode using R2DBC Postgres). In Postgres, the server pushes the replication log to the client, which gives you a hot stream as logical decode subscribes to the replication log.

The gist is pretty much that it depends on the actual database technology.

I think it doesn't give you the same level of safety. What if something fails between Kafka commit and DB commit.

https://medium.com/dev-genius/transactional-integration-kafka-with-database-7eb5fc270bdc

It's not so easy with PostgreSQL. You can use WAL’s aka Write Ahead Logs or triggers. May be the best approach will be using a external library like https://debezium.io

Reliability at the protocol (CometD) level implies a number of properties. Chief among them is a transactional ACK(nowledgement) of the message having been received by the subscriber. CometD supports ACKs as an extension. Salesforce's implementation of CometD doesn't support ACKs. Even if it did, you'd still have issues...but the frequency/loss of risk might be lower.

In your case you have to engineer a solution that amounts to finding and replaying events that were not committed to your target database. You do this using custom code or wiring adapters in Mule. Replay ID values are not guaranteed to be contiguous for consecutive events but they will be ordered. Event A with replay ID of 100 will be followed by event B with replay ID of 200.

You will need to store a replay ID value in your DB. You can then use it on resubscription (after subscriber failure) to retrieve events from SF that are missing from your DB. This will only work if the failure window is small enough. Salesforce event retention window is currently at 24 hours for standard platform event license. Higher-level licenses allow for longer retention.

Depending on the volume of data, frequency of events and other process parameters, you could get all of this out of the box with Heroku Connect. It does imply a Postgres DB on Heroku + licensing cost of HC and operational costs but most of our customers in similar circumstances find it worthwhile.