0

I have a webapplication and a corresponding mobile application.

User can add on the webapp and live DB is auto updated. App can be used offline and add insert and update into local sql lite. Any insert on the app will be given a uuid which can be used to identify it was added from the app.

Question is if I insert on the app then autosync to the live db we are aligned. If then I go offline on the app and then delete the same record from live table, then when the app goes online my back end script needs to decided if we should insert this out of sync record from local to master (As we just deleated from master) or if we should delete it locally from the local sqlite db.

The currenty soloution I have is that I have added a trigger to the live table were if a record is deleated it adds the uuid of that record to the the deleatedRecordUuid table so that when we come to sync, if the record is in the deleated table we know to delete from local and if not we insert to master.

Is it good to have a table of deleted records uuid's for this purpose or is there any other alternaitve??

Thanks

2
  • Start by listing all the combinations that can happen. It smells like there is a case where you do not have the information about whether to reload a row from one machine or delete it from the other.
    – Rick James
    Apr 6, 2022 at 23:01
  • @RickJames thats correct, I feel there will always be a situation where there could be conflict due to the user ability to use multiple devices and there could always be a situation where I don't know if to delete or insert.
    – flyingman
    Apr 6, 2022 at 23:39

1 Answer 1

0

The alternative is a type of tool called a CDC. This means Change Data Capture.

In MySQL, the binary log is the source of change data. This is a log of every data change in the MySQL database. This is how MySQL replication works, but there are other consumers of the binary log, such as Debezium.

For example, in MySQL, you can set up two MySQL servers as sources for each other. They both tail each other's binary log, and replay any changes they need to.

You might think this could result in an infinite loop. I.e. server1 inserts a row, the insert event is added to its binary log. Server2 sees that event, and inserts the same row in its database, and then logs that change to its own binary log. Server1 sees the same event in server2's binary log, and replays the same event, logging the change to its binary log. And so on.

The solution is that events are also annotated with the server id who originally created the event. Each of the server instances must have a distinct id. So if they tail the other's binary log, and see a change that is annotated with their own id, then they know that event has come full circle, and they should ignore that event.

Hopefully this gives you some ideas about how to solve this problem in your environment. I'm not saying you have to use MySQL's binary log, but perhaps the principle is something you could adapt for your own app.

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.