0

I have a Spring Boot webapp with a MySQL database. I'm trying to replicate this data into a MSSQL for running reports against.

All rows are audited with created_at and modified_at datetimes so i can easily fetch new and changed rows. Problem is when a row is deleted this will not be reflected in the target database.

From the top of my head there's a couple of ways to solving this

  • Triggers and a "history" table. Whenever there's an update to the table, it inserts a copy of the row and a column describing the action taken.
  • A deleted bit. 1 is a deleted row (this will have me redo all queries though, and now i see the advantage of having a class in between the controller and the repository)
  • And the very verbose method of querying all rows and check what's missing (will not do)

Is there a right way of handling this?

Improve this question
4
  • What versions are your MySQL and SQL Server instances? Which replication feature are you using (a MySQL feature or SQL Server)?
    – J.D.
    Commented Jun 3, 2023 at 14:25
  • Azure SQL and MariaDB 10. As per now theres no replication going on as i need to resolve this issue. I started out creating a Flow in Power Automate but soon hit this roadblock.
    – jared
    Commented Jun 3, 2023 at 14:50
  • Ah well those are important details. If you were on an older version of SQL Server, you'd be able to setup replication on that side which would handle deletions for you. Also, when you say Azure SQL, I assume you mean the product Azure SQL Database (not any of the other Azure products)?
    – J.D.
    Commented Jun 3, 2023 at 17:06
  • MariaDB have built-in versioning. So it can easily compare 2 data states - current and at specified timepoint, and provide all data needed for DBs synchronizing. See mariadb.com/kb/en/temporal-tables
    – Akina
    Commented Jun 3, 2023 at 18:17

1 Answer 1

Reset to default
0

the second method is better. you can just mark them as deleted, after all the actions are reflected to the target database, deleted them in another async thread.

Improve this answer

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.