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.


  • Impossible Mission Players - A

    @blakeyrat said:

    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 below Select *) so I wouldn't just truncate the table.
    So, in this case, selecting the (would be commented out) Delete statement to the end and 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.


  • Discourse touched me in a no-no place

    @Tsaukpaetra said:

    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:

    @Tsaukpaetra said:

    ```
    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


  • Impossible Mission Players - A

    @antiquarian said:

    do it this way:

    Lessoned learned, for sure. :smiley:

    @blakeyrat said:

    That makes no sense.
    You're right, it doesn't, and it doesn't.

    @blakeyrat said:

    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?




  • :belt_onion:

    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.



  • @blakeyrat said:

    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)



  • @Onyx said:

    "make shell freaking RED on production systems"

    XD

    who didn't reboot the wrong server while working via ssh?


  • Impossible Mission Players - A

    @Jarry said:

    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.

    @blakeyrat said:

    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....

    @Jarry said:

    what will be the fallout of that?
    Technically? Me pointing at them an quacking.

    @Jarry said:

    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



  • @Tsaukpaetra said:

    my local machine is good enough to have the end users feed production data into.

    :facepalm:



  • @Tsaukpaetra said:

    Technically? Me pointing at them an quacking.

    oh, in that case, maybe you could leverage this to convince them to do the right thing.


  • Impossible Mission Players - A

    @Jarry said:

    convince them
    It will be difficult.



  • @Tsaukpaetra said:

    And the last backup was from two days ago.

    That's the frist :wtf:. 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.


  • Impossible Mission Players - A

    @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. :stuck_out_tongue:

    @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...


  • Impossible Mission Players - A

    Also, can we get this off-topic discussion set Jeff'd? I think we've exceeded the threshold of reasonable tolerance...


  • Grade A Premium Asshole

    @Tsaukpaetra said:

    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. ;)


  • I survived the hour long Uno hand

    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.



  • @Tsaukpaetra said:

    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.


  • Impossible Mission Players - A

    @Yamikuronue said:

    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).

    @blakeyrat said:

    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".


    Filed under: Also for your enjoyment, this thing was conceptualized, designed, tested, and deployed (no, not approved, never approved) by one unit (me) in the space of a full 6 days. I'm surprised it works at all...



  • @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. :)


  • Impossible Mission Players - A



  • 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]
    

    :arrow_up: 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.



  • @blakeyrat said:

    USE XXXXX

    Yes. Certainly with one off update/deletes.

    @blakeyrat said:

    opened a transaction

    I have MSSQL Management Studio set to automatically start a transaction for that reason.



  • @Tsaukpaetra said:

    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.


  • Impossible Mission Players - A

    Eh, i think they've accepted the loss. They have a partial export from before anyways.



  • @Yamikuronue said:

    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.



  • @antiquarian said:

    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.



  • @blakeyrat said:

    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.



  • @boomzilla said:

    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.



  • @blakeyrat said:

    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.

    @blakeyrat said:

    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?



  • @boomzilla said:

    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.

    @boomzilla said:

    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.



  • @blakeyrat said:

    ... 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.

    @blakeyrat said:

    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.


  • Impossible Mission Players - A

    @blakeyrat said:

    .

    @boomzilla said:
    .
    When did this thread become so philosophical? WTH?

    @powerlord said:

    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


  • I survived the hour long Uno hand

    We segregate PII data into a separate DB that isn't refreshed.We don't have much of it, though.


  • Impossible Mission Players - A

    @anonymous234 said:

    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
    Response Msg 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'.

    @Yamikuronue said:

    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.



  • @Tsaukpaetra said:

    Query: DELETE FROM TheTable Select * from TheTable

    Response: ORA-00933: SQL command not properly ended


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.