Undo Truncate Database

Yeah that would be hard to handle, and slow.

Not sure what sizes of tables you deal with on a regular basis, but this would not be possible on very large tables. Imagine trying to cache a few million rows, detect someone using one of the MANY different types of destructive commands available across MANY different database providers and versions of each of those.

Again, this is what a DEV,QA,PROD environment is for. DEV, do what you want. QA, do what you want a bit more carefully, PROD, write up what you are going to do, have other eyes look at what you want to do, get consensus on it, then cringe as you do it. :slight_smile:

3 Likes

Yeah, it’s pretty easy to delete stuff–caution is good. That said, if the data is important, you really should have DB backups.

I had just moved my production DB to it’s own server (was on the same one as Ignition). IT guy forgot to turn backups on which is when I fat fingered the truncation :sweat: I’ve removed DROP privileges from my connection since then forcing me to be more careful.

1 Like

Appreciate the input from everyone as always!

1 Like

I’m curious, how did you accidentally type:
TRUNCATE <TABLE>? :thinking:

I typed truncate <x>_history instead of truncate <y>_history
I wanted to truncate, just wrong table

1 Like

Using transactions in the query browser cannot be trusted, as each query is run from the pool of connections. I don’t think you can be sure any actual query will run in the connection that has the transaction started, nor that a rollback would run in the connection that has uncommitted operations.

Yeah that’s… a really good point. All the more reason to stay in Beekeeper/SSMS/etc

Why should someone need to make an irreversible mistake to learn the lesson

Tradition? For real though I simply don’t believe there’s any feasible solution to this other than a) RTFM, b) don’t work in production, and c) use a real SQL client

:rofl: You’re probably right. However, to me it is like saying

I’ve bought the world’s fastest car, but I can’t order it with airbags. Speed is dangerous, and always will be.

No, it’s like saying you bought a fast car with air bags, but you then steered the car into a tree and asked, why did the car let me do that. Don’t steer the car into a tree! :slight_smile:

2 Likes

Why not have a car that will still let you drive into the tree, but will set off warning bells and lights when it recognizes that it’s about to hit something, just in case the problem was the driver not paying attention.

Just have the SQL utility pop up a confirmation window any time it sees you about to execute a DROP or TRUNCATE or maybe even a UPDATE that would change more than some number of rows all at once.

1 Like

This won’t protect against poor UPDATE/DELETE queries, but for DROP/TRUNCATE I will always first rename the table to <table_name>_PENDING_DELETE_YYYYMMDD and in the case of needing to truncate, will create a new empty copy of the original table in its place. I usually let the renamed tables sit for a while (days/weeks) before actually dropping them. This way I can see if losing these tables will break anything in my system without losing the data, and if it does break something, it is much quicker to just rename the table back to the original name than trying to restore from a backup (table renames take less than a second whereas you’ll be waiting a while for a large backup to restore…). Also makes it harder to accidentally truncate/drop the wrong table with the semi-unique “_PENDING_DELETE_YYYYMMDD” suffix acting as a clearer indicator you are dropping a table that is ok to drop. When I finally do drop these tables, I always use a DB IDE (DataGrip) that is connected with a non-Ignition DB user that has the additional DROP grant (the IDE usually has an extra layer of confirmation for drop/truncate, assuming you use the GUI to drop/truncate).

Note: If you are trying to drop/truncate a table that is the source of a FK (probably shouldn’t be dropping this table anyways), this method gets a lot messier, but for good reason - assuming you haven’t set the ON DELETE option…

(PS - This is NOT in place of backups. Still definitely want to backup your DB.)

2 Likes

Why not have a car that will still let you drive into the tree, but will set off warning bells and lights when it recognizes that it’s about to hit something, just in case the problem was the driver not paying attention.

You know what makes driving even better? Jump scares! I absolutely hate this new trend. This crap causes me to second guess myself and take my focus away from whatever I’m doing to try to figure out what the car is alerting over

And the automatic lane-drift correction… who needs coffee when you can drive down the freeway and think that your steering is suddenly failing in the worst way (pulling hard in the opposite direction and pulling even harder when you try to correct then suddenly letting go. good times)

I get what auto makers are trying to accomplish but I can’t drive safely with that stuff on. The only way I see this working is a) fully self-driving or b) a camera that watches your eye movements and does early collision warnings only if you’re not looking ahead

Because the Driver will undoubtedly think the car is being too cautious, ignore the warnings and hit the tree anyways. Been there, done that.

If warnings are useless, then why does the designer ask if you really want to move tags, or delete components, etc.?

Nanny state

6 Likes

:rofl: :joy: :rofl:

2 Likes

As amusing as the car analogies are, the only real answer is that it’s impossible to come up with something that perfectly suits everyone. There’s already lots of areas in the gateway and designer where you can cause yourself significant harm (see: footgun) - it’d be nice to take some of those edges off, but it’s also impossible to do that in a way that doesn’t ‘stifle’ power users. Plus, dialog/warning fatigue is very real.
We could, and certainly might, implement some (bypassable?) confirmation dialog in the DB query browser if you’re executing an UPDATE/DELETE/INSERT, but we’re absolutely not going to get into the realm of attempting to back up DB changes for you.

2 Likes

That makes perfect sense :ok_hand:

I bet @chasondeshotel has never used ctrl+z before :wink:

1 Like

Clippy appears and asks: “are you sure you want to undo?”

4 Likes