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. 
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 
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>? 
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
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!
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
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
Clippy appears and asks: "are you sure you want to undo?"
4 Likes