DELETE in prod
-
You always keep the "delete" part commented-out until you're done. Then select the delete statement without removing the comment marker to actually run it. Then deselect the text the INSTANT you hit F5.
Come on man, what is this babytown frolics?
But yes, SQL is really designed badly when it comes to DELETEs.
-
Then select the delete statement without removing the comment marker to actually run it.
The problem is that I needed the logic below it (all the stuff belowSelect *
) so I wouldn't just truncate the table.
So, in this case, selecting the (would be commented out) Delete statement to the endand fs.State = 'S'
would still delete everything in the table, because the Select (which wouldn't be commented) starts the next statement in the selection.
Filed under: Blech.
-
What SQL variant are you using?
I assumed you were using the select * part just to test your WHERE clause is returning the correct rows before you delete it.
What weird-ass mutant SQL combines a DELETE FROM with a SELECT *? That makes no sense.
-
The problem is that I needed the logic below it (all the stuff below Select *) so I wouldn't just truncate the table.
Next time do it this way:
```
Select * -- delete fs
from FormSubmissions fs left join QuestionDatas qd on fs.SubmissionID = qd.FormSubmissionID
Where qd.FormSubmissionID is null
and fs.State = 'S'
-
Right; that's the normal way to do it.
That way you can hit F5 with no text selected and it's harmless, but you can still select from the comment to the end of the query and do your delete when you've verified the SELECT is ok.
But Tsaufdhfwuygeru was making it sound as if the SELECT * was required to make the DELETE query work, which is strange.
-
i think his problem is that the weird ass mutant SQL doesn't requires a statement delimiter. so that this executed the two statements instead of blowing up like any sane sql flavour
-
do it this way:
Lessoned learned, for sure.
That makes no sense.
You're right, it doesn't, and it doesn't.just to test your WHERE clause is returning the correct rows before you delete it.
Got it in one. For whatever reason it didn't occur to me to omit that select once it was ready to be transmogrified into a Delete.
-
You can also add a USE XXXXX at the top of the file so it'll fail if you flub an F5 with nothing selected at all. (Assuming you don't have a database named XXXXX.) That trick's also helped me in the past.
-
also, whatever mutant SQL you're using, you just don't play with live data without a backup.
repeat with me: I SHOULDN'T WRITE A DELETE COMMAND ON A PRODUCTION SQL SERVER IF I HAVEN'T TESTED IT BEFORE ON A REPLICA
-
Also, you could have opened a transaction you then could have rolled back after you realized the error, BASICALLY THERE'S A MILLION THINGS THAT WOULD HAVE PREVENTED THIS.
-
what will be the fallout of that?
how much data went to oblivion?
-
-
Ran such a test on a prod system once before I added the "make shell freaking RED on production systems" to my install script.
Fortunately, it was a new install. All I lost is an hour of work because I just had to re-run all the scripts that populate the DB.
-
USE XXXXX
neat. i'll have it in mind the next time i need to do a risky operation.(I hope the next time is never, but i doubt it'll be the case)
-
"make shell freaking RED on production systems"
XDwho didn't reboot the wrong server while working via ssh?
-
ON A PRODUCTION SQL SERVER
Oh, trust me, this isn't in any way shape or form a production server.
However, that business decided that the crap I'm actively developing on my local machine is good enough to have the end users feed production data into. This despite me telling them explicitly in writing and verbal NOT to do that.BASICALLY THERE'S A MILLION THINGS THAT WOULD HAVE PREVENTED THIS.
Yeah, but there's no reason to really prevent it, it's a Dev server after all....what will be the fallout of that?
Technically? Me pointing at them an quacking.how much data went to oblivion?
Well, all of the questionnaire data, except for the header meta (which means I now have ~200 "submitted" questionnaires with no answers).PreEdit:Body is too similar to a recent post, due to 500 issues..
Filed under: I'm still waiting for them to provision my DB on the actual official Development server, not holding my breath for them to do so for Test and Production within the next month
Also filed under: This was all started with a fire drill prompted by a bank
-
my local machine is good enough to have the end users feed production data into.
-
Technically? Me pointing at them an quacking.
oh, in that case, maybe you could leverage this to convince them to do the right thing.
-
-
And the last backup was from two days ago.
That's the frist . You don't have daily backups? Or more, on a high traffic system?
Second, before you type ANYTHING into a production SQL connection, the very first thing you enter is:
BEGIN TRANSACTION
ROLLBACK TRANSACTION
Then and ONLY then do you enter commands. And THEN you do everything else everyone has said in this thread. Use XXXX. -- DELETE FROM. Everything.
The good news is, your users are probably fucking idiots, so who cares what their opinions are. Nothing of value lost.
-
@Lorne_Kates said:
You don't have daily backups?
It's a local database, no not really. Backups happen when I'm logged in and I feel like it.
@Lorne_Kates said:
a production SQL connection
Never said it was a production server (OMG that would be terrible if they let an average user like me use the DELETE command!).
Actually, see tag line in that post.@Lorne_Kates said:
Nothing of value lost.
Nope. Really, all that was lost was a bunch of "Y" or "N" entries, tied to an account number.
Nobody is supposed to even be in this thing, yet management is quite happy to send people to a site that has a big perma-banner on it that reads "Development Server Do Not Use For Production".
Filed under: Maybe I'll have a pre-roll-ad type-thing that everyone has to click through on every page...
-
Also, can we get this off-topic discussion set Jeff'd? I think we've exceeded the threshold of reasonable tolerance...
-
Also, can we get this off-topic discussion set Jeff'd? I think we've exceeded the threshold of reasonable tolerance...
I was thinking it should be Jeffed, but because it has a lot of good info for SQL newbs if they search for it. We don't do tolerance around here. ;)
-
Huh, something my company does well: we pull down production data into a test environment, write the script there using various tricks about commenting and whatnot, run the script in the test db, verify the problem is fixed, then hand the completed script to the operational DBAs to run in prod. We do this for anything that needs to modify prod data outside of one of our usual stored procedures.
-
can we get this off-topic discussion set Jeff'd?
Discourse told me something had been Jeffed, but it was even more determined than usual to keep me from figuring out what had been Jeffed and where it had been moved to. I only found it because a new topic with >20 posts suddenly appeared where it hadn't been moments before.
-
... also consider implementing soft deletes.
Because 99.9% of the time that's what you really want anyway once the actual business requirements come down the pike.
-
Huh, something my company does well: we pull down production data into a test environment, write the script there using various tricks about commenting and whatnot, run the script in the test db, verify the problem is fixed, then hand the completed script to the operational DBAs to run in prod. We do this for anything that needs to modify prod data outside of one of our usual stored procedures.
In theory, this is how it's done. But, due to the aforementioned Fire Drill, pretty much the entirety of the SDLC process was sidelined.Not even going to think about the actual f'ed state of the Test environment (which was supposed to be a day-behind Mirror of the Production db).
implementing soft deletes.
Yup. That's a bonafide entry on the imaginary Requirements Document that never got wrote. It's there, I swears it!This is literally a Proof-of-concept tech-demo that the heads want to use "RIGHT NOW".
-
@Lorne_Kates said:
Second, before you type ANYTHING into a production SQL connection, the very first thing you enter is:
Here's a tip for those using SQL Server Management Studio and are blessed with local administrative rights: change the default query template.
Put this in it:
BEGIN TRANSACTION SET XACT_ABORT ON /* Your query goes here. */ ROLLBACK
Tadaa, each query is now covered by a transaction. And even if you hit F5 before you've finished building your query, you won't lose data. :)
-
-
This looks like a pretty clear example of a bad interface.
You're about to run a DELETE statement. The following [n] records will be deleted: [scrolling list] [OK] [Cancel]
There, fixed it. I'll take my million dollars now etc.
-
@Lorne_Kates said:
Second, before you type ANYTHING into a production SQL connection, the very first thing you enter is:
BEGIN TRANSACTION
ROLLBACK TRANSACTION
Then and ONLY then do you enter commands. And THEN you do everything else everyone has said in this thread. Use XXXX. -- DELETE FROM. Everything.
This.
The only thing I would add is not to dawdle once you have the transaction open, as your work might escalate to table locks and block users on this live system.
-
In where we work, the procedure is a bit different. Our practice requires us to do database backup whenever we need to patch data on production server, no matter how little the change is.
But the basic idea is the same... you need to make sure you have way to go back when something goes wrong.
-
USE XXXXX
Yes. Certainly with one off update/deletes.opened a transaction
I have MSSQL Management Studio set to automatically start a transaction for that reason.
-
all of the questionnaire data, except for the header meta (which means I now have ~200 "submitted" questionnaires with no answers).
Quick script, 200 sets of answers indicating what you know the users need, rather than what they think they want, job jobbed.
-
Eh, i think they've accepted the loss. They have a partial export from before anyways.
-
Huh, something my company does well: we pull down production data into a test environment, write the script there using various tricks about commenting and whatnot, run the script in the test db, verify the problem is fixed, then hand the completed script to the operational DBAs to run in prod. We do this for anything that needs to modify prod data outside of one of our usual stored procedures.
Yeeeah, we can't do that on the application I currently work on for security reasons. There is data in production that the developers are not allowed to see.
Which means you have to write scripts against test data and hope you covered all the bases before handing it to the DBAs.
Edit: I should note that Dev and Testing both use datasets from several years ago before the sensitive data was part of this database.
This database is slowly growing larger as it is essentially the central Oracle Spatial instance for our entire organization.
-
Next time do it this way:
I usually structure mine like:
delete from the_table where id in( select id from the_table ... );
And I just start with the select to begin with and then only put in the delete and the parentheses at the end.
Oh, yeah, and I always have auto-commit turned off, so I can always roll back if needed.
-
Yeah that's how I do it.
Auto-commit is turned off, although in event of human error, we have flashback enabled which usually gets stuff back faster than restoring from backup.
-
We have multiple development schemas in the same database, so I can either just use one of those or possibly resurrect the data from one of them. I don't remember the last time I had a problem that required something like that, though.
-
Rejected, too error-prone.
The delete statement should remain commented-out so flubs of the F5 key will do nothing.
-
The delete statement should remain commented-out so flubs of the F5 key will do nothing.
I don't understand how "commented out" is less error prone than "never typed." Granted, if I run it then change my mind and roll back, I comment it out while I faff around with the
select
.
-
I don't understand how "commented out" is less error prone than "never typed."
Because once you save the query it's typed, and some bozo might open up the .sql file and hit F5 thinking it's all hunky-dory because he isn't involved in your brilliant Boomzilla Method.
Unless there's some step you're leaving out.
-
Because once you save the query it's typed, and some bozo might open up the .sql file and hit F5 thinking it's all hunky-dory because he isn't involved in your brilliant Boomzilla Method.
If I'm putting the query out for someone else to use, it's been submitted through our review process and the DBA will add some extra stuff that they do to it. It's meant to be run at that point. Or I've put it into our code base somewhere else and whatever flow control that's in the code deals with running it or not.
Unless there's some step you're leaving out.
I don't understand this thing about "I save the file" and then it gets run at some point. Just not a thing that happens in my workflow. Are you sharing a network drive or something?
-
I don't understand this thing about "I save the file" and then it gets run at some point. Just not a thing that happens in my workflow.
... you don't EVER save queries? WTF.
Are you sharing a network drive or something?
Usually, yeah. Or putting them in Confluence. You never know when the query will come in handy again, and I'm not the only employee at the company.
Even if I weren't, I'd be saving them for my own use.
I think you're the freak if you NEVER save queries.
-
... you don't EVER save queries? WTF.
Yes. But rarely 'delete' queries. It's usually something that I'm just doing to clean up whatever I'm working with. Or, as I explained, as part of a formal process that we have, where commenting it out would be stupid.
Usually, yeah. Or putting them in Confluence. You never know when the query will come in handy again, and I'm not the only employee at the company.
If it's something I think I'll need again it probably gets saved. But for most stuff where I'm looking at things...no. I'd end up with something that would only be useful for playing random and slo-mo.
-
.
@boomzilla said:.
When did this thread become so philosophical? WTH?before the sensitive data was part of this database.
Yeah, apparently some sort of new regulation "mandates" that certain "personal information" be "obfuscated" in our Dev and Test servers. In theory it should work, after the "daily" refresh of production data into the test and dev environments, just use an obfuscation tables to update all the records to their expected obfuscated state.
Too bad it doesn't work.
-
phpmyadmin, is bad, but it does that.
if you try to run a delete query without a where it barfs and asks you to confirm
-
We segregate PII data into a separate DB that isn't refreshed.We don't have much of it, though.
-
bad interface.
Maybe. How would you implement this without a GUI? After all, this is literally some text being sent through a pipe and waiting for the results to come back.Query:
DELETE FROM TheTable Select * from TheTable
Response:Are you sure?
Query:Y
Response:Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'Y'.
Query:YES
ResponseMsg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'YES'.
Query:DELETE FROM TheTable Select * from TheTable YES
Response:Are you sure? (0 row(s) affected)
Query:FFFFFFFFFFFFUUUUUUUUUUUUUUUUUUU
Response:Msg 2812, Level 16, State 62, Line 1 Could not find stored procedure 'FFFFFFFFFFFFUUUUUUUUUUUUUUUUUUU'.
We don't have much of it, though
Unfortunately, we do, and it's not nearly so nice as to be in one pretty little side database.
In fact, the sporadic location nature is probably why the obfuscation process isn't working right, whoever did it probably doesn't know all the "self healing" procedures that the databases use to try to "protect" themselves with.
-
Query: DELETE FROM TheTable Select * from TheTable
Response:
ORA-00933: SQL command not properly ended