New Support Guy probably needs clean pants now...



  •  Just spoken to the support desk of a software supplier we use. The software in question we've used for years, and we have an agreement with them that we can write our own software around their database that does anything their product doesn't do (and we've become damned good at it). Yesterday we applied an update to it, and the part of the program that claims customer payments didn't work. I knew why it didn't work (one database field wasn't filled in), but contacted their support as I didn't want to fix it myself every day, especially since I'm off on holidays soon...

     By the time support bod called back, a new fella I'd not spoken to before, I'd already fixed it as our admin girls wanted to go home to drink multiple bottles of cheap wine, but I promised him I'd show him the next day (today)

     So, I hooked up into their remote desktop system so he could see what I was looking at on screen, and I could hear him in the background chatting to a developer. Then came the question "How did you fix it yesterday?". He then watched me remote desktop from that PC to my PC, loaded up SQL Server Enterprise Manager, and typed (without checking the database schema first, 'cos I've practially committed it to memory) "UPDATE PaymentClaims SET Reference = AccountNumber WHERE ...etc... " and clicked "Execute".

    I could practically hear him go as white as a sheet just before he stuttered "What are you doing!?!?!?!", clearly thinking we're the equivalent of a granny taking her car to get serviced and showing the mechanic how she removes and reinserts the spark plugs in order to get it started

    Bless him, he is new after all!



  • Bless you, I hope you're doing all that on the production server



  •  Who will be laughing when you accidentally highlight only the " UPDATE PaymentClaims SET Reference = AccountNumber" part?



  • UPDATE accounts SET id='ABC123'[accidental Enter press]

    WHERE oh fuck



  • <font size="2">No SQL related accidents today :-)</font>

    Developmestruction - It's not for wimps!

     



  •  UPDATE accidents SET days_since = 1; WHERE type = 'SQL';



  • I usually do these kinds of things between BEGIN TRAN and ROLLBACK. After I have verified that it's working I change the ROLLBACK into COMMIT and execute or add to the change script.

    This way it's possible to update/insert/delete even from several different tables at once with no risk involved



  •  I tend to write the WHERE clause first and then highlight the text from the bottom up before hitting F5.

    Although saying that yestereday whilst doing a rush-panic-OMFG-everythings-broken-fix-it-NOW-before-the-zombies-get-us type job I did forget a WHERE clause in an insignificant DB on an insignificant table on some insignificant data that then updated 3 rows instead of the intended 1.

    Could've been worse!



  •  I don't know about SQLServer's development stuff, but Oracle's development stuff (SQL*PLUS and SQLDeveloper) start with transactions turned on, so until you COMMIT; everything is temporary.



  • @powerlord said:

    I don't know about SQLServer's development stuff, but Oracle's development stuff (SQL*PLUS and SQLDeveloper) start with transactions turned on, so until you COMMIT; everything is temporary

    MSSQL doesn't. Probably because it's locking is very heavy-handed and keeping an open transaction with a change in it will cause ungodly amounts of problems for anyone else considering going near the database :P



  • @lolwtf said:

    UPDATE accounts SET id='ABC123'[accidental Enter press]

    WHERE oh fuck

    <dailySarcasticAsshole>
    its called rollback. Maybe you heard of it. This weird feature introduced in these things called relational databases.
    </dailySarcasticAsshole>



  • @lolwtf said:

    UPDATE accounts SET id='ABC123'[accidental Enter press]
    WHERE oh fuck
    rollback?



  • @Resistance said:

    @lolwtf said:
    UPDATE accounts SET id='ABC123'[accidental Enter press]
    WHERE oh fuck
    rollback?
    Rollbacks are all well and good, unless you've used a trigger to send 100 emails.  You can't roll those back.



  • @belgariontheking said:

    emails.  You can't roll those back.
     

    plz send back the emails I sent earlier thx



  • @dhromed said:

    plz send back the emails I sent earlier thx
    See what you did now?  Now I can't resist the urge to do this:

    [quote user="www.bash.org/?104052"]<NES> lol
    <NES> I download something from Napster
    <NES> And the same guy I downloaded it from starts downloading it from me when I'm done
    <NES> I message him and say "What are you doing? I just got that from you"
    <NES> "getting my song back fucker"[/quote]



  • @belgariontheking said:

    @Resistance said:

    @lolwtf said:
    UPDATE accounts SET id='ABC123'[accidental Enter press]
    WHERE oh fuck
    rollback?
    Rollbacks are all well and good, unless you've used a trigger to send 100 emails.  You can't roll those back.

     

    TRWTF


  • Garbage Person

    @belgariontheking said:

    @Resistance said:

    @lolwtf said:
    UPDATE accounts SET id='ABC123'[accidental Enter press]
    WHERE oh fuck
    rollback?
    Rollbacks are all well and good, unless you've used a trigger to send 100 emails.  You can't roll those back.

    You have a trigger that sends emails? WTF.

     A trigger that inserts rows into a table full of emails waiting to be sent, sure. And a cron job that queries the table every minute or so and sends out the messages. That way you can fucking roll the changes back (the cron job's queries won't return the messages until the transaction that created them commits)



  • @Weng said:

    A trigger that inserts rows into a table full of emails waiting to be sent, sure. And a cron job that queries the table every minute or so and sends out the messages. That way you can fucking roll the changes back (the cron job's queries won't return the messages until the transaction that created them commits)
    That'd be nice, but I'm constrained by my environment.  They won't let us use cron.  Not that I'm confident it would work if they did.  These are the Solaris machines I've complained about multiple times.

    But there's a further WTF here anyway.  Triggers shouldn't fire until a change is committed.



  • @belgariontheking said:

    Triggers shouldn't fire until a change is committed.
    That would lead to an inability to update secondary records that a trigger creates when you make a primary record during the same transaction you create a primary record, which could lead to inconsistent state. Triggers that perform outside-of-database actions shouldn't fire until a change is committed, unless there's a distributed transaction coordinator or some other enterprisey POS that could roll that back too. Triggers that can fire during the same transaction should, and their effects should be just as rollbackable as the rest of the transaction.



  • @TwelveBaud said:

    @belgariontheking said:
    Triggers shouldn't fire until a change is committed.
    That would lead to an inability to update secondary records that a trigger creates when you make a primary record during the same transaction you create a primary record, which could lead to inconsistent state. Triggers that perform outside-of-database actions shouldn't fire until a change is committed, unless there's a distributed transaction coordinator or some other enterprisey POS that could roll that back too. Triggers that can fire during the same transaction should, and their effects should be just as rollbackable as the rest of the transaction.
    I can agree with you, but then I can't.  At the very worst, triggering only after commit should be an option so you don't have to handle rollbacks in the outside-of-database code ("plz return my emails").  There are plenty of ways to detect a change has been made to the database, but why bother with all that shit when it's built right into Oracle, but in a completely fucked way.


Log in to reply