3

My scenario is fairly typical, but I can't figure out a good solution. I have a DB table (in reality several unrelated tables, but let's focus on one) where there is a fair deal of activity going on. In the table:

  • Records are rarely inserted
  • Records are often updated (I expect multiple records to be updated every minute)
  • Records are never deleted (they are merely marked as "deleted" by one final update)

There are also multiple external systems that are interested in having an up-to-date copy of this table. The number of systems changes fairly rarely, but it does happen every now and then (this involves contracts being signed).

I now want to implement a "push" mechanism that will send a notification to these external systems when something changes. I also want to send just a "delta" - only the changed records.

The primary process which does all the frequent updates has its hands full of other things already, so I don't want to burden it with notifying the external systems. I'd rather create a separate process which regularly polls for changes and then sends the notifications as needed.

In addition, some of the systems might be offline now and then (it's not normal, but this is life - things happen). In that case, when it comes back online, I want to know what was the last update that I sent to it and send all the changes since then. And, of course, if there is a new system added, then it needs all the records to be sent.

My DB is MySQL 8.latest and I struggle to figure out a good way to do this. In every mechanism that I come up with (last_updated timestamps, counters, "generations", whatever) I can also come up with a (more or less convoluted) scenario where an update might get lost.

I would like to think that this is a solved problem in the industry. How can I reliably do this?

11
  • Have you investigated MySql replication? This sort of copying is easiest done at the database level where it is a well solved problem. I would not recommend rolling your own solution at the application level.
    – kiwiron
    Mar 8, 2023 at 6:16
  • @kiwiron Unfortunately that will not be possible. I have to adhere to a standard API which mandates that updates have to be pushed from our system to other connected systems and also specifies the exact HTTP requests to do so. Figuring out what to push is my responsibility, and I also want to separate the process that modifies the data and the process that pushes the modified data to other systems.
    – Vilx-
    Mar 8, 2023 at 8:54
  • @kiwiron In case you're wondering, the protocol is called "OCPI" and the spec is freely available.
    – Vilx-
    Mar 8, 2023 at 8:54
  • I have no hands on experience with event streaming platforms like Apache Kafka but your problems reminded me of its description. Especially that an external system might be added/removed or is temporarily offline. But as far I grasp the streaming approach it is also a lot of work, because your primary process will send the modifications to Kafka and your DB will be just another "external" system. But I might be completely wrong and I am happy to be corrected. Nevertheless perhaps you can also read a bit about the publish subscribe pattern.
    – gillesB
    Mar 8, 2023 at 21:58
  • 1
    Kafka can be used for CDC, and depending on the requirements an existing Kafka Connect connector may be enough (or at least something to start with). However you need to be prepared for the increased complexity when adding Kafka to your tech stack (although it can offer plenty of opportunities as well).
    – Kayaman
    Mar 9, 2023 at 12:57

3 Answers 3

1

The reason @kiwiron suggested you look at replication is because it contains the technology you need to do all of these things: the binary log. Once configured, every modification to the database gets written to the binary log, in order.

You can then replicate the events either "offline" by reading the binary log, or by writing a program which appears as a MySQL slave. It would not actually be a MySQL slave, instead it would take the stream of events from the master MySQL server and convert them into the API transactions you need. The pseudo-slave will need its own persistent way of keeping track of exactly which transaction it last handled successfully.

It's still a fairly large project, but I think it holds the best chance of working.

1
  • I see. This sounds... quite involved and feels like an overkill. Not to mention I'd be dependent of MySQL version (if they change something in the file format...) Oh, but at least for me the final nail in the coffin is the fact that we're using a managed DB in "the cloud", so we don't even have access to these files or have the ability to manually add a slave... Anyways, have an upvote for the idea, but I'll wait for something different.
    – Vilx-
    Mar 8, 2023 at 17:31
1

Another way to do this would be to use a queue system like rabbitmq and sending events after making changes to the DB. The drawback is that any change to the DB external to your application would go unnoticed.

0

I'm going to post an answer here myself, but not accept it yet, because I might not end up using it after all, and perhaps someone will have something useful to comment at this.

The way I currently think of approaching this is by leveraging 3 MySQL-specific features:

  1. You can have UTC timestamp columns that automatically update whenever a record is updated. In CREATE TABLE I can go: last_updated timestamp(6) not null default current_timestamp(6) on update current_timestamp(6)
  2. These timestamps can have up to 6 decimal places after the seconds (so basically a microsecond resolution). This is noted above by the (6) parts.
  3. You can do lock tables tbl1 read, tbl2 read, ... Which locks the tables in read-only mode and makes sure that no transaction can update (or hold locks) them while I check for changes.

So my idea is to save somewhere the timestamp of the last update, then do lock tables, select all records that have changed since the last update, and then immediately unlock tables.

Now, the lock tables part feels really dangerous to me since it essentially freezes the rest of the system while I check for changes. So I need to be fast. I might even lock/select/unlock each table individually, although then I need to take care of the order of the selects (for tables that have a parent-child type of relationship).

Since the timestamps have a microsecond resolution, I'm fairly confident that it's impossible to get a situation where a past timestamp appears after I've already selected the data.

EXCEPT.

This is a real danger for stored procedures. The current_timestamp() function returns the time value as it was at the start of the procedure. So an update to a row from a stored procedure COULD get a timestamp that is who know how long in the past.

Luckily we don't use stored procedures in this project, so I think I'm good.

Added: I realized a problem with the above. The timestamp is in UTC, which means it is actual date and time. The problem is - in computer world, time can also flow backwards. And no, it doesn't involve theory of relativity. Rather computer clocks are fairly imprecise, so in order to keep them correct they need to be regularly synced to some authoritative time source. This is an absolute must-have for any production server, but it also means that the current date time can jump backwards a little when a synchronization happens. Thus there is a window of opportunity where a row can appear with a timestamp in the "past" after I've already selected all the rows to sync.

In order to combat this I've come up with another idea: for each row add another field sync_trigger which is exactly the same as the last_updated field, except that it's nullable. When you want to select rows that need syncing you look at the rows that have sync_trigger is not null and then set them to null when they're synced.

2
  • I can't see how your proposed architecture fills the requirement of syncing "multiple external systems" when some may be unavailable due to maintenance or network problems.
    – kiwiron
    Mar 10, 2023 at 19:29
  • @kiwiron Oops, you're right. This is just the first part of the scheme which allows me to reliably detect changed rows. I'll add the rest a bit later.
    – Vilx-
    Mar 11, 2023 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.