Is there any way to undo a truncate command issued by the Ignition database browser? Is there any cached data that could be retrieved?
It’s up to your database software, but generally the answer is no. You might have periodic backups you could rollback to, but only if previously enabled on your DB.
yep, that is what I was afraid of. Live and learn 
I reconfigured all my database connections with a new user profile that does not have DROP privileges so I can’t mistype a truncate command again. 
2 Likes
I submitted to the ideas forum: Add ‘Are you sure?’ prompt and/or some form of data caching for truncate/drop queries to protect against accidental data loss.
Database Query Browser Protection | Voters | Inductive Automation
If you use transactions you can rollback up to the point when you commit (at least on PostgreSQL and MS SQL)
I assume you are talking about system.db.beginTransaction… this is from scripting, I’m talking about when using the Database Query Browser.
That sounds like a convenience wrapper so you don’t have to system.db.runQuery("BEGIN TRANSACTION")
MS SQL BEGIN TRANSACTION docs: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-ver15
In the database query browser
BEGIN TRANSACTION;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 13;
COMMIT;
or like
BEGIN TRANSACTION;
DELETE FROM HumanResources.JobCandidate
WHERE JobCandidateID = 31; --oops!
ROLLBACK;
It’s helpful to do a SELECT before committing to check your work.
Obviously you’ll have to run COMMIT or ROLLBACK as a separate query to get the benefit
I would still like to see something like a rollback button
Then you also need a begin transaction and a commit button and Ignition now has to keep up with your transactions. What if you start a transaction, close the database browser, come back later and do some more stuff, then hit rollback thinking you’re going to roll back the last query and instead you lose a day’s work?
At that point why not just do transactions in SQL? This is SQL 101. SSMS doesn’t even have a begin/rollback button. I see this as a difficult-to-implement feature with limited upside
Sure an undo button would be nice but that’s just not how SQL works
1 Like
Sorry about your data btw. Been there, done that. It’s how I learned the value of transactions 
1 Like
As well as the value of running a SELECT in place of DELETE to insure you're actually deleting what you want to, prior to actually deleting it.
1 Like
Does that makes sense when using truncate? With truncate you want to delete everything and reset the table, so most people like me, unwisely do not review the table before the command. I typed the wrong table name, and poof data gone.
Why should someone need to make an irreversible mistake to learn the lesson?
Overall, I think you guys are right. But I think there is value in having a better way to log/start transactions in the DQB to protect noob mistakes.
Absolutely!
Had you run, SELECT TOP 1000 * FROM wrongTable prior to the truncate you probably would have seen it was the wrong table.
How does Ignition in general know the user's experience level? How does the DQB know you don't mean to run against that table? If it asked you to confirm, in the moment would you have really reviewed what you were doing, after having been annoyed at Ignition constantly asking you if you're sure?
I used to use this software package called Unigraphics (I'm sorry for anyone who knows what that is), when ever you would close, weather your file was dirty or not, it would ask you if you were sure you wanted to exit. Of course I'm sure, that's why I clicked the X button. Problem was, you were conditioned to just click yes, the one time you hadn't clicked save in 2 hours, poof there went your work. Super frustrating.
I feel like this would be much the same in that respect. I still make what I would consider to be beginner level mistakes, and I've been at this for nearly 20 years (boy that makes me sound old 
)
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.
Sorry. Truncate is dangerous. Always will be, I suspect.
2 Likes
There are two kinds of developers in the world: those who’ve accidentally deleted something with a SQL command, and those who will accidentally delete something with a SQL command.
11 Likes
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.
SA,Root,Administrator,DBOwner, etc… There are reasons that everyone in charge of security are VERY mindful of handing out rights above someones head. Raw database access on a production server should be treated on the same level as Root or Administrator.
We have all made those kinds of mistakes, best to learn to fear the major altering commands in a production environment.
1 Like
Exactly, why is it so wrong to ask for the query browser to cache table data before truncation is invoked. It would only persist as long as the browser window is open, then memory could be freed on browser close? You could even have an option to turn on/off or alter caching behaviors.
And when the table has 1TB of data? When truncating, the client isn’t even seeing the data that is discarded. So too with DELETE FROM–all the action is in the DB. You need to be asking your DB supplier to offer this type of undo, not IA.
2 Likes