Rolling back accidental Express change (major/minor/whatever) - planning

Using Concrete / Help
1

So I’m working up to rolling out about 5 years of work from my Dev Concrete CMS space into my Prod space, and I’m aiming to build out some DevOps automations, as well as protections against $filthyHumans (such as myself) doing mistakes at any point in time.

One of those things in particular is what this thread is about… and that is I’m trying to figure out a tool/method in order to restore Express data if a $filthyHuman deletes something by mistake in Prod. Whether it’s an Express Entry, or an entire Express Object (and all the things that includes such as Associations, Entries, etc).

I’m currently stuck to Concrete CMS v8.5, for long-winded reasons, but probably months down the road this year that could improve to some form of v9.x, but at this point in time I am working with v8.5.

As I’m sure many of us are aware, Concrete CMS uses MySQL for the DB tech. So I am limited to options for that DB tech.

From what I am seeing, MySQL “cannot” roll back DB changes once they are committed. I suspect if I were to accidentally Delete an Express Entry or Express Object, or whatever, that it would PROBABLY be committed to the MySQL DB, making rollback uhh… “hard”.

So I was hoping to hear whatever advice I can get on the matter. What methods/tools/whatever can I implement to make it so that the relevant MySQL database content is constantly recorded, and I can roll-back individual data changes even after they are committed, as well as some way of nicely tracking which changes happened over time.

I am NOT experienced at all with writing SQL Queries, hence trying to figure out a tool to help with this. It scares the absolute snot out of me that me, or anyone in the future working on this Concrete CMS system with me, could accidentally delete an Express Entry or Express Object and that could break a LOT of stuff. I foresee that without some sort of tooling to help roll that back, that it would take a lot of work to correct such an accidental deletion, or that we would have to roll back and maybe lose an entire day’s worth of changes (which really is NOT a good option as we could be talking about storefront reversions too).

So…

What do you folks do? What can you say on the matter? Thanks to anyone that has things to share! :slight_smile:

2

Amazon RDS offers point-in-time recovery. You would restore from a particular point in time to a new DB (not overwrite prod) then extract the entries you want. MySQL offers this as well, but I’ve never tried it.

If you think this scenario is going to happen frequently, you might want some other solution such as logging all queries or logging any existing values before changing or deleting them.

3

So a couple things…

  1. I should have mentioned, this is all on-premise, self-hosted, so that specific Amazon RDS feature is not on the table. However equivalent functionality for rollback of the WHOLE database is something that can already be implemented… but…
  2. I’m talking about rolling back PART of the database, because I don’t want to lose any data that was created since the previous backup/snapshot, I just want to restore very specific and granular data. Rolling back to a specific point in time is not what I’m looking to do here and I plan to treat that as last resort not my first option.

I don’t expect this to happen frequently, and logging all queries/existing values as they change is not off the table, but I’m not seeing what tool I can use to help me manage that so I can review what changed and select what to “undo” (so to say). So if you know of any particular tools (preferably FOSS) that can do that, I’m all ears :slight_smile:

4

You can’t really automatically revert part of database if there are multiple relationships (and be still sure data is 100% valid/correrct).
You would have to combine versioning (so additional tables with versions), soft-deleting and proper cascading/not-cascading deletion.
And maybe then, you could cook something from that.
Doing stuff like that in Express would be pain in the ass.
For complex situations, it’s easier to just write some doctrine enitites/make simple UI for CRUD operations.

If you do not want to “accidentaly” delete Express objects, just stop using super-admin account and restrict viewing related dashboard pages (to any role below in hierarchy).

2 Likes
5

Yeah I know I DON’T want to delete Express Objects, but I need a solution if that DOES happen. Access control limitations will be implemented, but as anyone who actually does Disaster Recovery, I’m trying to build for the worst case scenario here.

What do I do if it DOES happen? I need a good solution. Not just roll back backups.

6

What you’re describing is something that would typically be handled with ‘soft deletes’. In those cases, deleted records still exist, just with a deleted flag on them, so they can be easily re-instated.

But Express doesn’t support soft deletes, and it’s not something you can really add by just adding another field as you can still fully delete records.

As Parasek has suggested, this sounds like you need to shift this data into database tables that you define, with Doctrine or similar, where you can then more easily implement soft deletes, and you simply remove the ability to accidentally trigger a real delete on the data. Express maybe isn’t the right tech for what you’ve described.

The other approach is that you may need an audit or transaction log for your data. So when records are deleted, they’re actually copied elsewhere first, in enough detail that you could resurrect the data later if needed. Maybe some like a chunk of JSON representing the record you could save somewhere when a delete is triggered, one that you could then potentially work back the other way from when a restore is needed. Fair bit of fiddly work for that.

Disaster recover is about planning for disasters, not really loss of records due to intentional user action - that’s more an application level consideration, where you need to be able to fully remove the ability to destroy the data.

2 Likes
7
  1. I’m not switching away from Express as I have a huge amount of work built around that, as using Express saves a huge amount of time in areas where skills are lacking (namely SQL).
  2. I wasn’t talking about intentional deletions primarily. There’s going to be a functional need to have certain people have access to Express stuff for very rational reasons. And the thing about working with humans is that mistakes happen no matter how careful one is. Planning for things like that is a very warranted (and wise) way to develop DR.
  3. Audit/Transaction log flows and management of that is on the table, so if you can point me to any tools for that for MySQL I’d love to hear thanks!
8

Liquibase? Never used it myself but it sounds like something worth looking at.

1 Like
9

That sounds oddly familiar! I’ll check it out but I have some fragment of an not-currently-trusted memory that recognises that name for this function!

Thank you! Even if it doesn’t work out (we’ll see) it’s still appreciate that you shared this thought! Yay! :smiley: