One of my top 10 WTF days



  •  First time posting one of my experiences here..

     i was working in a call center company with around 500 operators, it was around 05pm when my co-worker John asked me:

     "Hey.. is there any recent backup ?.. i just run a delete and forgot the where clause".. 

    Guess what table it was ? it was the NegotiationRules table.. the one that our behemoth system used for everything.. all managers rushed to our room because they had 500 operators doing nothing..

    So i went to Matt our infrastructure guy that did the backup managment and such.. 

    "Hey i need the backup of the database X, where is it? " - 

    Matt "Sorry.. we have no backups for each.. not enough space"..

    Sooo great.. we had no backups or anything to recover.. then after some googling i found a tool to recover from the log files.. downloaded the trial version, and after some tests it looked like it could work.. however since it was trial.. it could only recover one record at time (click first record, get the insert  to rollback, go to next).. obviously the company wont buy the full version (over 1000$).. so i download keytext (macro tool) and made a macro to click an item, get the sql to recover, copy in notepad, move next..

     so now we headed to the server room to make a new backup and run this tool on the database to recover.. the server's HD was almost full.. the database was around 800gb, Matt was going to put  a new hd in the server to make a new backup.. he connected the sata cable.. but there was no energy cable left to use.. he then opened another server plugged the energy cable from another server.. plugged the sata cable in the main server and started with the HD hanging between both servers..

     and then finally the backup begins.. after a couple of hours the backup was done.. and then i run the tool to recover the log files.. took like 4 hours for the tool to find all lost entries, and then installed keytext on the server.. made it run to get all queries and finally managed to recover all entries after around 7 hours



  • And thereinafter, you either took Delete privileges away from John, or you stressed the importance of BEGIN TRAN/COMMIT to him, especially when working with live data (saved my butt once. Stupid semicolons)


  • 🚽 Regular

    @Nexzus said:

    And thereinafter, you either took Delete privileges away from John, or you stressed the importance of BEGIN TRAN/COMMIT to him, especially when working with live data (saved my butt once. Stupid semicolons)
     

    My rule of thumb is whenever you run a delete command, run a select first with the where clause you are using to verify you are indeed deleting what you intend to. Regardless of whether you have a backup, it saves you time, frustration, embarrassment, and that awful feeling in your stomach as, after you hit the enter key and notice it's taking an extra several seconds to delete what you think is a small set of records, you realize what you've done.



  • TRWTF is that your bosses would rather have 500 people idle for an hour (I'm supposing people stop working around 6 PM there) than spend $1000 in a license. 500 lost man-hours are more costly than that.



  • [quote user="Renan "C#" Sousa"]TRWTF is that your bosses would rather have 500 people idle for an hour (I'm supposing people stop working around 6 PM there) than spend $1000 in a license. 500 lost man-hours are more costly than that.[/quote]

    Not if they're indians working for $1.99/h...



  •  I did something similar, once.  Fortunately, they had good backups, so it wasn't quite as hard to restore, and it didn't idle anybody. 

     TRWTF is a language that makes it so you can accidentally delete a whole table just by omitting a where clause.  I bet that every day somebody does this somewhere.

     

     


  • 🚽 Regular

    @tharpa said:

     TRWTF is a language that makes it so you can accidentally delete a whole table just by omitting a where clause.  I bet that every day somebody does this somewhere.
     

    Agreed, especially seeing, at least in most SQL constructs they have a TRUNCATE clause. I might be mistaken, but I do recall some SQL application having an option to disable a lack of a where clause in DELETE and UPDATE statements.



  • @dtech said:

    [quote user="Renan "C#" Sousa"]TRWTF is that your bosses would rather have 500 people idle for an hour (I'm supposing people stop working around 6 PM there) than spend $1000 in a license. 500 lost man-hours are more costly than that.

    Not if they're indians working for $1.99/h...

    [/quote]Lost labour is not the only cost unless the call centre was idle anyway. There's either a big cost in lost business - enough business to justify 500 telephonists - or they're working for third parties and will have breached SLAs.

    I'm guessing that whoever was immediately in charge of resolving this didn't have authorisation to spend that amount, and couldn't get authorisation quickly enough. Considering the costs of downtime, if that's so, that's YAWTF right there.


  • ♿ (Parody)

    @RHuckster said:

    @tharpa said:
    TRWTF is a language that makes it so you can accidentally delete a whole table just by omitting a where clause.

    Agreed, especially seeing, at least in most SQL constructs they have a TRUNCATE clause. I might be mistaken, but I do recall some SQL application having an option to disable a lack of a where clause in DELETE and UPDATE statements.

    Yes, but a TRUNCATE usually doesn't work with cascading FKs, AFAIK. Plus, I think it works differently with logs and such, at least with some RDBMSes.



  • @RHuckster said:

    @Nexzus said:

    And thereinafter, you either took Delete privileges away from John, or you stressed the importance of BEGIN TRAN/COMMIT to him, especially when working with live data (saved my butt once. Stupid semicolons)
     

    My rule of thumb is whenever you run a delete command, run a select first with the where clause you are using to verify you are indeed deleting what you intend to. Regardless of whether you have a backup, it saves you time, frustration, embarrassment, and that awful feeling in your stomach as, after you hit the enter key and notice it's taking an extra several seconds to delete what you think is a small set of records, you realize what you've done.

    In addition to that, always type the command backwards.

    WHERE clause first, then DELETE FROM.

    And always, always, write the final product like this:

    select * from importantTable
    -- delete from importantTable
    where coworkerIsAnIdiot = 0

    That prevents a stray F5 from accidentally running the delete; you have to be conscious enough to actually select the correct portion of the query, or it's a harmless SELECT.



  • Only 7 hours? You lucky guy :) A full oracle rebuild from backup after someone inavertently moved away one of the database live folder took our company 3 days to fully recover. And ther is no begin/rollback on filesystem :D



  • Today is the day I enable "SET IMPLICIT_TRANSACTIONS" in SQL Server Management Studio. Though I can't recall ever making that mistake, having made a habit of typing the where first.



  • @blakeyrat said:

    In addition to that, always type the command backwards.

    Among the uncountably-infinite set of reasons I hate SQL, I am also bothered by the order of its syntax. Can anyone provide some insight as to why the order is VERB-FROM-WHERE instead of something more sensible like FROM-WHERE-VERB? It would still make just as much sense in English. I guess the difference between SELECT and DELETE is pretty significant, so you'd might want to make it the first word you read, but considering how significant the rest of the command has to be (see above), it hardly seems a strong argument.

    And when I'm writing SQL, the first thing I think of isn't "what columns do I want?", but "where does this data come from?". I usually end up writing "select * from thetable" hitting the Go key to see what happens, and then filling in the rest. In fact, now that I think about it, without fail, the specific columns I want are [i]always[/i] the [i]last[/i] thing I write down, as * is good enough until I feel like whittling down the amount of data returned.

    I hate using SQL so much, but this isn't one of its quirks that fans the flames of my hate; this is just something makes me pause and wonder if the creators made it stupid on purpose or if it was all by accident.



  • @Xyro said:

    Can anyone provide some insight as to why the order is VERB-FROM-WHERE instead of something more sensible like FROM-WHERE-VERB?

    Because it was invented in 1974? And nothing better's come along since?



  • INSERT INTO importantTable
    SELECT * from importantTable
    AS OF TIMESTAMP SYSDATE - 1/48;
    COMMIT;
    

    If you really need, you can get the SCN of the last delete transaction and use that one in the flashback query. But usually a statement like above saved enough data I accidentally deleted (but until now on test and qa systems only).

    Lesson learned: Enable flashback memory (at least a few hours)



  • @Xyro said:

    Can anyone provide some insight as to why the order is VERB-FROM-WHERE instead of something more sensible like FROM-WHERE-VERB? It would still make just as much sense in English.
     

    Because it won't make as much sense in English.  If you think so please give an example.

    "Run to Johnny." Makes sense. (Select * from Table)

    "Johnny run to" Does not. (Table Select * From)

    "Johnny to run." Makes sense but changes meaning. (Table from Select *)

    "To Johnny, run." Makes sense and is proper but the syntax is usually made for English majors. (From Table Select *)

    For the English speaker the first is a full sentence, tell someone to run to Johnny.  The second sentence is a sentence fragment, where should Johnny run to?  The third sentence, tells us Johnny will run somewhere eventually.  The fourthsentence is what I think you are referring to, and while making sense in both SQL and English, the sytax is awkward for most people and actually requires punctuation to be proper. Do you expect programmers to also be English majors?  Granted, with how most write, we could stand a bit more training in how to communicate.

    This is why SQL is structured the way it is, it is built from simple English.  Other languages have a different way of structuring their sentences and SQL could have been structured that way if it was developed by non-english speakers, but then it would not make as much sense to those of us that speak English.

    BTW, this argument is easily reveresed if, let's say, you were a French speaker and are familiar with French sentence structure.



  • @KattMan said:

    Johnny

    But clause-subject-verb makes perfect sense in English. In fact, the first sentence of post I wrote before is structured that way: "Among the uncountably-infinite set of reasons I hate SQL, I am also bothered by the order of its syntax." To simplify and put into active voice, consider this one: "Among the set of other reasons, the order of syntax bothers me." It is also perfectly fine to write the inverse: "The order of syntax bothers me, among the set of other reasons," or even, "The order of syntax, among the set of other reasons, bothers me." The difference is entirely stylistic and which is chosen is generally determined by clarity (primarily from clause length) or impact. In the case of programming, I would argue that the best determiner would be specificity funneling, i.e., writing things from most general (table) to most specific (columns).

    Also, apparently, I have nothing better to do than post about grammar. Go me!



  • Well, now with "SET IMPLICIT_TRANSACTIONS", it warns me when I close the query window that I have uncommitted transactions, asking if it should commit them, even when the only query I ran was a select statement. Who commits a select?



  • @blakeyrat said:

    always type the command backwards.

    WHERE clause first, then DELETE FROM.

    And always, always, write the final product like this:

    select * from []
    -- delete from []
    where []

    >.>



  • @Sutherlands said:

    @blakeyrat said:

    always type the command backwards.

    WHERE clause first, then DELETE FROM.

    And always, always, write the final product like this:

    select * from [
    -- delete from [
    where [

    >.>

    I have absolutely no idea what message your post is supposed to relay. So I'll assume you said I'm a genius and I deserve a raise.

    Thanks!


  • ♿ (Parody)

    @dtfinch said:

    Well, now with "SET IMPLICIT_TRANSACTIONS", it warns me when I close the query window that I have uncommitted transactions, asking if it should commit them, even when the only query I ran was a select statement. Who commits a select?

    Well, there's always SELECT....FOR UPDATEs.



  • @boomzilla said:

    @dtfinch said:
    Well, now with "SET IMPLICIT_TRANSACTIONS", it warns me when I close the query window that I have uncommitted transactions, asking if it should commit them, even when the only query I ran was a select statement. Who commits a select?

    Well, there's always SELECT....FOR UPDATEs.

    Or SELECT INTO



  • @blakeyrat said:

    @Sutherlands said:

    >.>

    I have absolutely no idea what message your post is supposed to relay. So I'll assume you said I'm a genius and I deserve a raise.

    Thanks!

    I was gonna say Madonna pointey-boobs and belly-button... Still doesn't make much sense though.



  • Meh... I did this on my first day of my new job after college. You only do it once, and it's probably better to actually do it and get burned once than to be told not to do it. Kinda like the day a guy let me bang my head into an overhead pipe at a factory I worked, and then he said "now you'll never forget to wear your helmet on the floor"



  • @blakeyrat said:

    @Sutherlands said:

    @blakeyrat said:

    always type the command backwards.

    WHERE clause first, then DELETE FROM.

    And always, always, write the final product like this:

    select * from [
    -- delete from [
    where [

    >.>

    I have absolutely no idea what message your post is supposed to relay. So I'll assume you said I'm a genius and I deserve a raise.

    Thanks!

    Blakey's post:
    Always do X

    *Example not doing X*



  • @eguilherme said:

     so now we headed to the server room to make a new backup and run this tool on the database to recover.. the server's HD was almost full.. the database was around 800gb, Matt was going to put  a new hd in the server to make a new backup.. he connected the sata cable.. but there was no energy cable left to use.. he then opened another server plugged the energy cable from another server.. plugged the sata cable in the main server and started with the HD hanging between both servers..

    Oh my. That would have been fun if you had some potential difference between the grounds in those two machines. Kind of reminds me of soft-modding an original XBox, except you also then disconnect the IDE cable from the hard drive WHILE IT'S RUNNING and plug it into your PC. Good times. Can't believe I didn't ruin some equipment with that one.



  • @Sutherlands said:

    Blakey's post:
    Always do X

    *Example not doing X*

    Sutherlands post:

    "I don't know what the word 'final' means."



  • The final product has the delete commented out?  Or you misspelled "blakeyrat" as "Sutherlands"?



  • Rule of thumb when it comes to any environment I work with: If you give a dev the right to execute on production shit will happen. Sometimes he just had 3 hrs of sleep the night before, opse.

    The env is always split as:

    all devs have a view-only account. If you have a DML that must run on production give it to a DBA or to someone with the keys, who is disciplined to only run in read-only mode, have auto-commit off, and always ask for a second set of eyes when executing production updates.

    Basically 4 eyes > 2 when it comes to dml on production.

    TRWTF = no backup. Just wait till a major unforeseen power surge demolishes the computer. Or a fire. Or the disgruntled IT dude decides to throw in a bolt into the server thus destroying the company.

    I've been saved by backups quite a few times. Sometimes you just deleted the wrong thing. Better a backup than a complete data loss even if its a day old.



  • @Sutherlands said:

    The final product has the delete commented out?  Or you misspelled "blakeyrat" as "Sutherlands"?

    Yes. That way you have to fucking explicitly select the fucking DELETE line before running the query. As I said in the first fucking place.

    Also if you or a co-worker has a habit of opening saved queries and running them without reading them first, you could protect against accidental deletion.

    READING IS FUNDAMENTAL!

    Edit: Added more swear words, because in retrospect I'm angry.


  • ♿ (Parody)

    @blakeyrat said:

    Also if you or a co-worker has a habit of opening saved queries and running them without reading them first, you could protect against accidental deletion fetch your clue by four.

    FTFY



  • So ALWAYS type the where first, except in the final product, where you have the delete commented out.  Alright, I understand now.



  • @Sutherlands said:

    So ALWAYS type the where first, except in the final product, where you have the delete commented out.  Alright, I understand now.

    Jesus fucking shit christ in a handbag, what the fuck people. Is this really that hard?

    Type the query's FROM and WHERE clause, then shove a SELECT * on top. Hit Execute. Check to see if you get the rows you expected.

    Now, write the DELETE line underneath the SELECT, commenting it out. YOU ARE NOW DONE WRITING THE QUERY, save it in this form.

    To execute the query, select everything from DELETE to the end of the WHERE clause, and hit F5 (or whatever). You've now performed the DELETE.

    People who open your saved query and don't carefully select the correct part of the query either get a syntax error, or a harmless SELECT query.

    Now please die in a fire, thank you.


  • 🚽 Regular

    Hey,
    eguilherme, welcome to the forums, btw.



  • @Xyro said:

    Can anyone provide some insight as to why the order is VERB-FROM-WHERE instead of something more sensible like FROM-WHERE-VERB?
     

    Because IMHO SQL was not designed to be written by humans; it was designed to be written by computers. It is very good at being readable by humans, but the syntax is way too crabby to be easily human-writable.

    For example, a SELECT statement consists of clauses {SELECT ...} {FROM ...} {WHERE ...} {GROUP BY ...} and {ORDER BY ...}. A human writable syntax would allow the clauses in any order; SQL does not.

     



  • @db2 said:

    @eguilherme said:

     so now we headed to the server room to make a new backup and run this tool on the database to recover.. the server's HD was almost full.. the database was around 800gb, Matt was going to put  a new hd in the server to make a new backup.. he connected the sata cable.. but there was no energy cable left to use.. he then opened another server plugged the energy cable from another server.. plugged the sata cable in the main server and started with the HD hanging between both servers..

    Oh my. That would have been fun if you had some potential difference between the grounds in those two machines. Kind of reminds me of soft-modding an original XBox, except you also then disconnect the IDE cable from the hard drive WHILE IT'S RUNNING and plug it into your PC. Good times. Can't believe I didn't ruin some equipment with that one.

    Let's just say it's a quick and easy way of testing that all your earths are properly commoned...

     



  • @DaveK said:

    all your earths

    Are belong to us?

    What do you British people call it if the ground is on, say, the International Space Station? Is it still an "earth?"



  • @blakeyrat said:

    And always, always, write the final product like this:

    select * from importantTable
    -- delete from importantTable
    where coworkerIsAnIdiot = 0

     

    I like this idea.

     

    I always use a transaction when I am modifying data e.g.

    begin transaction
    delete from importantTable where coworkerIsAnIdiot = 0
    -- commit
    -- rollback

     ...as it is much easier to roll back a transaction than restore a database.

     



  • @blakeyrat said:

    @DaveK said:
    all your earths

    Are belong to us?

    What do you British people call it if the ground is on, say, the International Space Station? Is it still an "earth?"

    Surely "ground" is equally inapplicable on the ISS. However, whether British, Russian, American or anything else, I would have thought one would use whatever term was designated in the ISS Electrical Systems Standards (or whatever), to ensure everyone was on common ground (sorry!).



  • @blakeyrat said:

    What do you British people call it if the ground is on, say, the International Space Station?
     

    A disaster. Should the ISS deorbit we'd rather it burn up in the atmosphere than crash to ground.



  • @blakeyrat said:

    What do you British people call it if the ground is on, say, the International Space Station?
     

    Floor? Deck?



  • @eguilherme said:

    Matt "Sorry.. we have no backups for each.. not enough space"

    I would rq there and then.

    "We can't spend a few kilodollars on a sufficient storage and backup system, we'd rather lose $50kdollars in downtime and lost sales, not to mention flushing our reputation down the toilet. Oh, and now we can afford backups even less; anyway, now that we sternly warned the devs not to do this, what's the probability of it happening twice?" (Hint: One.) If it wasn't so common with the executive idiots, this kind of false economy would be hilarious.



  • @tharpa said:

    TRWTF is a language that makes it so you can accidentally delete a whole table just by omitting a where clause.  I bet that every day somebody does this somewhere.

    There are languages which are even more creative in accidently deleting data. My favorite example is ABAP. There, you can do something like this:

    DELETE FROM TABLE important_table FOR ALL ENTRIES IN lt_stuff_to_delete WHERE id = lt_stuff_to_delete-id.

    lt_stuff_to_delete is an internal table(1) which contains some data used to identify the data to delete. ABAP will convert this into a query which takes each line from the internal table and adds a part to the WHERE clause using the data on this line. Now to the tricky part: What happens if the internal table is empty?

    99% (my estimate) of junior developers will say: "Nothing will happen". (The remaining 1% look at the documentation first). Fact is, it will instead omit the WHERE clause completely, running this statement on all entries in the database table. Oops...

    (1) An internal table in ABAP is a table which does not exist on the database, but is stored completely in memory.


  • Trolleybus Mechanic

    I hate to say it, but this is a feature I actually enjoyed in Access: Whenever you run an Insert, Update or Delete statement, it automatiaclly puts it in a transaction, and gives you a popup. "You are about to modify [X] records. You sure, buddy?"

    It's instantly obvious when X=1 or X=HOLY SHIT ALL OF THEM?

     

    @db2 said:

    Oh my. That would have been fun if you had some potential difference between the grounds in those two machines. Kind of reminds me of soft-modding an original XBox, except you also then disconnect the IDE cable from the hard drive WHILE IT'S RUNNING and plug it into your PC. Good times. Can't believe I didn't ruin some equipment with that one.
     

    Funny, I was just reading up on this. I want to mod an original XBox to act as a media server for my wife's office. She has a DVD player that can read media files in most any format from a DVD, but the player is dying. Since I can't find a replacement that has all the same features, it's easier and cheaper to just set up XMBC so she can stream stuff from the house's media server.

    I couldn't believe the instructions were actually "Start playing a MP3 on the XBox, pause it, then unplug the hard drive and hot-swap it with the new hard drive in your PC."  Mainly because someone figured that out through trial and error.



  • @Lorne Kates said:

    I couldn't believe the instructions were actually "Start playing a MP3 on the XBox, pause it, then unplug the hard drive and hot-swap it with the new hard drive in your PC."  Mainly because someone figured that out through trial and error.

    Yeah, it's completely insane, but I guess it's obvious enough if you know that the original XBox uses a simple ATA password for security, and you understand how ATA passwords work. It was pretty easy - I think it only took me one or two tries to make it work. End result: 160 GB XBox stuffed full of "ISOz and ROMz".

    And yes, Access isn't totally without merit. I use it quite frequently for making simple internal-use data maintenance tools. No point spending half the day mucking with C# and data binding just to let a few people update data in a handful of tables. I think it gets a bad rap by being part of Office - too many people think it's just "Excel Plus", and not a full-blown development environment. And it holds up fine with multiple users if SQL Server is your back-end.



  • @piskvorr said:

    We can't spend a few kilodollars on a sufficient storage and backup system,

    Amazon S3. You can do it for the cost of the coffee service.

    [quote user=""]And yes, Access isn't totally without merit. I use it quite frequently for making simple internal-use data maintenance tools. No point spending half the day mucking with C# and data binding just to let a few people update data in a handful of tables. I think it gets a bad rap by being part of Office - too many people think it's just "Excel Plus", and not a full-blown development environment. And it holds up fine with multiple users if SQL Server is your back-end.[/quote]

    Access is the world's most misunderstood software product. The funny thing is that if you take someone who hates Access and show them Filemaker, suddenly they're gushing over it-- even though Filemaker is the exact same program!

    If nothing else, Access contains the best data import tool shipping in any Microsoft product, and that includes SQL Server Management Studio. If you have a complex .csv to import, you're usually better off running it through Access first, then having SSMS slurp the data from the Access tables.



  • @db2 said:

    And it holds up fine with multiple users if SQL Server is your back-end.

    Interesting. When I tried to do that, I had to give up when I found that it scanned every table you used in a query instead of using the indices in the back-end database. Is there a way around this, or did you only use this approach where the tables were small enough to make this quite fast?

    It is a shame, as Access does have some useful features compared to entering SQL commands directly, such as the ease with which you can change an update query into a select one. I've never really looked into the forms and reports, but I appreciate how they could be useful.



  • @AndyCanfield said:

    Because IMHO SQL was not designed to be written by humans
    Yes it was, and most of us have no problem writing it.@AndyCanfield said:
    it was designed to be written by computers. It is very good at being readable by humans, but the syntax is way too crabby to be easily human-writable.

    For example, a SELECT statement consists of clauses {SELECT ...} {FROM ...} {WHERE ...} {GROUP BY ...} and {ORDER BY ...}. A human writable syntax would allow the clauses in any order

    Eh?

     

    I disagree.  I think it's fine as long as you know the language.  Just like any language.

     



  • @SenTree said:

    @blakeyrat said:
    @DaveK said:
    all your earths

    Are belong to us?

    What do you British people call it if the ground is on, say, the International Space Station? Is it still an "earth?"

    Surely "ground" is equally inapplicable on the ISS. However, whether British, Russian, American or anything else, I would have thought one would use whatever term was designated in the ISS Electrical Systems Standards (or whatever), to ensure everyone was on common ground (sorry!).

    I would have expected that on the ISS they have a negative and a positive wire and don't bother with a common.  What, exactly, would you connect it to?

    [ Ten minutes quick research later ... ] 

    Ah, there's the answer.  The three pins on a plug on the ISS are live, neutral and ... plasma!

     

    [Edit: Since the ISS runs on DC, I should really have said "positive, negative and plasma", but that wouldn't have made the joke flow quite as well.] 


  • Garbage Person

    @DaveK said:

     

    Ah, there's the answer.  The three pins on a plug on the ISS are live, neutral and ... plasma!

    .... Huh. That's pretty cool.

Log in to reply