We don't need a script; we can type it by hand



  • One of our databases is named tables (nobody who still works here knows why).

    We have a control table: dataBases, where we store base reference data upon which other data is based. This data is refreshed periodically with a completely new set of data. Personally, I'd ensure the table was backed up, then truncate, then do a mass insert.

    Our DBAs were doing a release around midnight last night.

    As part of it, they had to change the structure of and reload the dataBases table. They chose to drop/create/insert instead of truncate/alter table/insert. In the process of trying to do this, instead of entering:

        drop table dataBases;

    They had a brain-fart and entered:

        drop dataBase tables;

    Fortunately, it was only about 300GB and not the main DB of 40+TB.

    When queried how this happened (e.g.: wasn't the install-script tested?), they replied: we don't need a script; we can type it by hand.

    And the restore goes on...



  • Srly man, why are your DBAs not dead yet?



  • @serguey123 said:

    Srly man, why are your DBAs not dead yet?
    Because they know someone (don't know who) who won't allow them to be shot or fired.

    I got my system up and running by pointing to the data dump they did (yesterday) to our preprod environment. It's not ideal, but at least our customer facing system is still running, with the only inconvenience being to the QA testers.



  • @serguey123 said:

    Srly man, why are your DBAs not dead yet?
    Who said they were alive in the first place?  From what I hear, they're like wraiths, which also explains why they can't (or won't) be fired.  Would you fire a wraith?  I though not...



  • @C-Octothorpe said:

    Would you fire a wraith? 
    Could you even fire a wraith?


  • ♿ (Parody)

    @snoofle said:

    One of our databases is named tables (nobody who still works here knows why).

    We have a control table: dataBases, where we store base reference data upon which other data is based.

    This sounds like the start of a Who's On First routine...

    @snoofle said:

    They had a brain-fart and entered:

        drop dataBase tables;

    ...and this confirms that it was.



  • @snoofle said:

    @C-Octothorpe said:

    Would you fire a wraith? 
    Could you even fire a wraith?

    Wraith: No mortal manager can fire me!

    Snoofle: I'm not a manager!



  • @snoofle said:

    When queried how this happened (e.g.: wasn't the install-script tested?), they replied: we don't need a script; we can type it by hand.

    Ok, guys, it's official now. Snoofle is a hired gun for the DBA maffia. He was hired to invent stories that went almost absurdly, but just plausbily wrong, with a lot of blame on a bunch of nincompoop DBAs. The goal was, of course, to make the rest look better. But today he overdid it. No sane DBA would call a database database or tables. I've never tested it, but I even doubt Oracle would allow it. And then enter such commands by hand in production.

    Snoofle, I have to stop believing you...



  • @snoofle said:

    When queried how this happened (e.g.: wasn't the install-script tested?), they replied: we don't need a script; we can type it by hand.

    And the restore goes on...

     

    "we don't need to restore it - they can recover the data by manually typing INSERT statements by hand"

     



  • @boomzilla said:

    @snoofle said:

    One of our databases is named tables (nobody who still works here knows why).

    We have a control table: dataBases, where we store base reference data upon which other data is based.

    This sounds like the start of a Who's On First routine... @snoofle said:

    They had a brain-fart and entered:

        drop dataBase tables;
    ...and this confirms that it was.
    And I am still wondering who is on first.

  • Considered Harmful

    @Anketam said:

    @boomzilla said:

    @snoofle said:

    One of our databases is named tables (nobody who still works here knows why).

    We have a control table: dataBases, where we store base reference data upon which other data is based.

    This sounds like the start of a Who's On First routine... @snoofle said:

    They had a brain-fart and entered:

        drop dataBase tables;
    ...and this confirms that it was.
    And I am still wondering who is on first.

    That's what we've been trying to tell you!



  • @TGV said:

    @snoofle said:

    When queried how this happened (e.g.: wasn't the install-script tested?), they replied: we don't need a script; we can type it by hand.

    Ok, guys, it's official now. Snoofle is a hired gun for the DBA maffia. He was hired to invent stories that went almost absurdly, but just plausbily wrong, with a lot of blame on a bunch of nincompoop DBAs. The goal was, of course, to make the rest look better. But today he overdid it. No sane DBA would call a database database or tables. I've never tested it, but I even doubt Oracle would allow it. And then enter such commands by hand in production.

    Snoofle, I have to stop believing you...

    I wasn't here when this crap was created; neither was my boss.I doubt the DBAs chose the name; my boss+2 was one of three developers at the beginning, so it's likely they're the ones who did it.

    As for the prod goof up, nobody here scripts anything, except me. Why do I do it? Because a) I'm dyslexic and frequently transpose stuff (no, I didn't do this one; I am not a DBA), b) it's repeatable and testable and c) it eliminates the possibility for stuff like this to happen. This sort of thing happens here at least once a week; it's just that this time, it actually caused some significant damage that couldn't be fixed during the deployment window.

    ...Also, I do need to anonymize a little...



  • @joe.edwards said:

    @Anketam said:

    @boomzilla said:

    @snoofle said:

    One of our databases is named tables (nobody who still works here knows why).

    We have a control table: dataBases, where we store base reference data upon which other data is based.

    This sounds like the start of a Who's On First routine... @snoofle said:

    They had a brain-fart and entered:

        drop dataBase tables;
    ...and this confirms that it was.
    And I am still wondering who is on first.

    That's what we've been trying to tell you!

     

    Verily!

     


  • BINNED

    @snoofle said:

    my boss+2 was one of three developers at the beginning
    Sounds like they applied the Dilbert Principle at least two times too many.



  • @snoofle said:

    One of our databases is named tables (nobody who still works here knows why).
     

    Because that's what a database *IS*, doi. It's self-documenting!

     



  • @boomzilla said:

    @snoofle said:

    One of our databases is named tables (nobody who still works here knows why).

    We have a control table: dataBases, where we store base reference data upon which other data is based.

    This sounds like the start of a Who's On First routine...

    @snoofle said:

    They had a brain-fart and entered:

        drop dataBase tables;

    ...and this confirms that it was.

    TRWTF is using language keywords as the names of your real-world entities.

     I've often said that "Staples" is the worst possible name for a provider of office supplies.  Picture this (which has actually happened to me): your stapler runs empty, so you go to the supply closet for a fresh box of staples to reload it.  Open up the cabinet, and every box inside, regardless of size, shape or contents, has the word "Staples" plastered across it in huge red letters.

    I was stuck in there for four hours one day.

     


  • BINNED

    @da Doctah said:

    TRWTF is using language keywords as the names of your real-world entities.
    I thought this was the kind of thing that goes without saying, but apparently not.



  • @da Doctah said:

    I've often said that "Staples" is the worst possible name for a provider of office supplies.  Picture this (which has actually happened to me): your stapler runs empty, so you go to the supply closet for a fresh box of staples to reload it.  Open up the cabinet, and every box inside, regardless of size, shape or contents, has the word "Staples" plastered across it in huge red letters.
    Uhm, since the boxes apparently weren't labeled with their contents, you would have been screwed no matter what store they were bought from.

     



  • @Zylon said:

    @da Doctah said:

    I've often said that "Staples" is the worst possible name for a provider of office supplies.  Picture this (which has actually happened to me): your stapler runs empty, so you go to the supply closet for a fresh box of staples to reload it.  Open up the cabinet, and every box inside, regardless of size, shape or contents, has the word "Staples" plastered across it in huge red letters.
    Uhm, since the boxes apparently weren't labeled with their contents, you would have been screwed no matter what store they were bought from.

     

    They were labeled with the contents too, but in tiny little letters that meant I had to pick up each one and look at it closely before finding that it too was something other than what I was after.  The real problem was that blaring red "Staples" acting as a distraction the whole time.

    There's this thing called the Stroop effect.  Read up on it for an idea why having too much information is often more of an obstacle than not having enough.

     





  • @da Doctah said:

    @Zylon said:

    @da Doctah said:

    I've often said that "Staples" is the worst possible name for a provider of office supplies.  Picture this (which has actually happened to me): your stapler runs empty, so you go to the supply closet for a fresh box of staples to reload it.  Open up the cabinet, and every box inside, regardless of size, shape or contents, has the word "Staples" plastered across it in huge red letters.
    Uhm, since the boxes apparently weren't labeled with their contents, you would have been screwed no matter what store they were bought from.

     

    They were labeled with the contents too, but in tiny little letters that meant I had to pick up each one and look at it closely before finding that it too was something other than what I was after.  The real problem was that blaring red "Staples" acting as a distraction the whole time.

    There's this thing called the Stroop effect.  Read up on it for an idea why having too much information is often more of an obstacle than not having enough.

    Its even worse if you thing of "Staples" as Food (which is valid in appropriate context) and injest the content of the boxes without further thought.



  • @TheCPUWizard said:

    @da Doctah said:

    @Zylon said:

    @da Doctah said:

    I've often said that "Staples" is the worst possible name for a provider of office supplies.  Picture this (which has actually happened to me): your stapler runs empty, so you go to the supply closet for a fresh box of staples to reload it.  Open up the cabinet, and every box inside, regardless of size, shape or contents, has the word "Staples" plastered across it in huge red letters.
    Uhm, since the boxes apparently weren't labeled with their contents, you would have been screwed no matter what store they were bought from.

     

    They were labeled with the contents too, but in tiny little letters that meant I had to pick up each one and look at it closely before finding that it too was something other than what I was after.  The real problem was that blaring red "Staples" acting as a distraction the whole time.

    There's this thing called the Stroop effect.  Read up on it for an idea why having too much information is often more of an obstacle than not having enough.

    Its even worse if you thing of "Staples" as Food (which is valid in appropriate context) and injest the content of the boxes without further thought.

    If you see a box labeled "Staples" and decide to eat it, that's a whole different problem.



  • @TheCPUWizard said:

    and injest the content of the boxes
     

    But only jokingly.



  • I don't know that using a script would help. Sounds like your DBAs are too stupid to make a script that accepts the connection credentials as a parameter. So ya know, instead of a hand-jam WTF you can have a security WTF.

    Atleast they haven't figured out the SYS account can have a single character password regardless of how one configures the password requirements on an Oracle box. They haven't figured that out yet, right?



  • @MathNerdCNU said:

    I don't know that using a script would help. Sounds like your DBAs are too stupid to make a script that accepts the connection credentials as a parameter.
     

    Agreed. Snoofle's long posted about the stupidity of the DBAs easily trumping anything intelligent to emerge from that organisation. It's an inherent corrosive effect. Script or no script, cluelessness will prevail.

    @MathNerdCNU said:

    Atleast they haven't figured out the SYS account can have a single character password regardless of how one configures the password requirements on an Oracle box. They haven't figured that out yet, right?

    Eh, there's endless arguments concerning priv'd accounts complying/bypassing system policy; SYS is no different.



  • @snoofle said:

    One of our databases is named tables (nobody who still works here knows why).

    We have a control table: dataBases, where we store base reference data upon which other data is based. This data is refreshed periodically with a completely new set of data. Personally, I'd ensure the table was backed up, then truncate, then do a mass insert.

    Our DBAs were doing a release around midnight last night.

    As part of it, they had to change the structure of and reload the dataBases table. They chose to drop/create/insert instead of truncate/alter table/insert. In the process of trying to do this, instead of entering:

        drop table dataBases;

    They had a brain-fart and entered:

        drop dataBase tables;

    Fortunately, it was only about 300GB and not the main DB of 40+TB.

    When queried how this happened (e.g.: wasn't the install-script tested?), they replied: we don't need a script; we can type it by hand.

    And the restore goes on...

    I had believed all your stories, working for a financial firm myself, but this one is over the top. A table named databases and a database named tables. Really???


  • @Rick said:

    A table named databases and a database named tables. Really???
    Behind the end of this desk there's a box labeled "Cable Modem" on the front, and for the French-Canadian market, "Modem Cable" on the back.

    Tucked inside it is a smaller package labeled "Modem Cable" on the front, and for the French-Canadian market, "Cable Modem" on the back.

    The original contents of these two packages, purchased on separate occasions at two separate stores, are now attached to one another.



  • @TGV said:

    I've never tested it, but I even doubt Oracle would allow it.
     

    I had to test it. I only have Postgres available at home (I'm sure people at snoofle's place use Oracle - no I don't remember ever reading it anywhere), so I tested on it.

    Postgres gladly accepts creating  a database name either 'database' or 'table'.



  • @TheCPUWizard said:

    Its even worse if you thing of "Staples" as Food (which is valid in appropriate context) and injest the content of the boxes without further thought.
     

    We had at Brazil a famous rat poison that looked exactly like a famous candy...

    Well, several deaths later, we don't have any of them anymore.



  • There is a reason most sane people always do that kind of thing in a transaction, so they can do a rollback when they realize their fuck up.

     



  • @mt@ilovefactory.com said:

    There is a reason most sane people always do that kind of thing in a transaction, so they can do a rollback when they realize their fuck up.

     

    Maybe you should think about rereading that book on SQL. Its the one on your shelf that makes a noise when you open it. 

    I am sorry. That was just snide.

    I might start with Learning SQL by Alan Bealieu before commenting about SQL again. Now that was still snide, but also helpful. In particular, you might read about the difference between DDL and DML.

     



  • @Rick said:

    In particular, you might read about the difference between DDL and DML.

    One kills mosquitoes and the other's an airport package delivery company.


  • BINNED

    @Rick said:

    @mt@ilovefactory.com said:

    There is a reason most sane people always do that kind of thing in a transaction, so they can do a rollback when they realize their fuck up.

     

    Maybe you should think about rereading that book on SQL. Its the one on your shelf that makes a noise when you open it. 

    I am sorry. That was just snide.

    I might start with Learning SQL by Alan Bealieu before commenting about SQL again. Now that was still snide, but also helpful. In particular, you might read about the difference between DDL and DML.

     

    Not just snide, also incorrect. Rolling back a drop table command works just fine in SQL2005.



  • @PedanticCurmudgeon said:

    Not just snide, also incorrect. Rolling back a drop table command works just fine in SQL2005.

    Is there a "forum law" that says the more snidely someone offers a correction, the more likely it is they are wrong? Skitt's Law would be a special-case of it.



  • @blakeyrat said:

    @PedanticCurmudgeon said:
    Not just snide, also incorrect. Rolling back a drop table command works just fine in SQL2005.

    Is there a "forum law" that says the more snidely someone offers a correction, the more likely it is they are wrong? Skitt's Law would be a special-case of it.

     

    Lets all try this on a real server, please:

    create table x ( y int )
    /
    select * from x
    /
    begin transaction -- Sybase and others need this. Oracle, usually configured to not need it.
    /
    drop table x
    /
    rollback
    /
    select * from x
    /


  • Discourse touched me in a no-no place

    mysql> use tdwtf;
    Database changed
    
    mysql> create table x (y int);
    Query OK, 0 rows affected (0.06 sec)
    
    mysql> select * from x;
    Empty set (0.00 sec)
    
    mysql> begin;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> drop table x;
    Query OK, 0 rows affected (0.02 sec)
    
    mysql> rollback;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from x;
    ERROR 1146 (42S02): Table 'tdwtf.x' doesn't exist
    mysql>
    


  • @Rick said:

     

    Maybe you should think about rereading that book on SQL. Its the one on your shelf that makes a noise when you open it. 

    I am sorry. That was just snide.

    I might start with Learning SQL by Alan Bealieu before commenting about SQL again. Now that was still snide, but also helpful. In particular, you might read about the difference between DDL and DML.

    I do know the difference between  DDL and DML, and I did originally learn SQL from a book.

    But after reading the book you also need to read the documentation for your DBMS for all the things which are supported but not part of the sql standard. Including the interesting way different transaction isolation levels are implemented and the fact that there is support for using DDL in a transaction with full commit/rollback support. Is this not supported by other databases?



  • @mt@ilovefactory.com said:

    @Rick said:

     

    Maybe you should think about rereading that book on SQL. Its the one on your shelf that makes a noise when you open it. 

    I am sorry. That was just snide.

    I might start with Learning SQL by Alan Bealieu before commenting about SQL again. Now that was still snide, but also helpful. In particular, you might read about the difference between DDL and DML.

    I do know the difference between  DDL and DML, and I did originally learn SQL from a book.

    But after reading the book you also need to read the documentation for your DBMS for all the things which are supported but not part of the sql standard. Including the interesting way different transaction isolation levels are implemented and the fact that there is support for using DDL in a transaction with full commit/rollback support. Is this not supported by other databases?

     

    I am curious, (not being snide this time, but really curious). Which DBMS do you use that allows rollback on DDL? Do you then have to execute a commit before the drop table takes effect for other users?

     

     


  • Considered Harmful

    @Microsoft SQL Server 2008 said:

    create table x ( y int )

    Command(s) completed successfully.


    select * from x

    (0 row(s) affected)


    begin transaction

    Command(s) completed successfully.


    drop table x

    Command(s) completed successfully.


    rollback

    Command(s) completed successfully.


    select * from x

    (0 row(s) affected)



  • @joe.edwards said:

    select * from x
    (0 row(s) affected)
     

    Does it also restore the data inside the table?


  • Considered Harmful

    @dhromed said:

    @joe.edwards said:

    select * from x

    (0 row(s) affected)
     

    Does it also restore the data inside the table?


    @Microsoft SQL Server 2008 said:
    create table x ( y int )
    Command(s) completed successfully.

    insert into x select 1 union all select 2 union all select 3
    (3 row(s) affected)

    begin transaction
    Command(s) completed successfully.

    drop table x
    Command(s) completed successfully.

    rollback
    Command(s) completed successfully.

    select * from x
    (3 row(s) affected)

    That's a yes.



  • @joe.edwards said:

    @dhromed said:

    @joe.edwards said:

    select * from x
    (0 row(s) affected)
     

    Does it also restore the data inside the table?

    @Microsoft SQL Server 2008 said:
    create table x ( y int )
    Command(s) completed successfully.
    

    insert into x select 1 union all select 2 union all select 3
    (3 row(s) affected)

    begin transaction
    Command(s) completed successfully.

    drop table x
    Command(s) completed successfully.

    rollback
    Command(s) completed successfully.

    select * from x
    (3 row(s) affected)

    That's a yes.

    Now try "drop database" :-)

     

     


  • Considered Harmful

    @Rick said:

    @joe.edwards said:

    @dhromed said:

    @joe.edwards said:

    select * from x

    (0 row(s) affected)
     

    Does it also restore the data inside the table?


    @Microsoft SQL Server 2008 said:
    create table x ( y int )
    Command(s) completed successfully.

    insert into x select 1 union all select 2 union all select 3
    (3 row(s) affected)

    begin transaction
    Command(s) completed successfully.

    drop table x
    Command(s) completed successfully.

    rollback
    Command(s) completed successfully.

    select * from x
    (3 row(s) affected)

    That's a yes.

    Now try "drop database" :-)

     

     

    @Microsoft SQL Server 2008 said:


    create database tables
    Command(s) completed successfully.

    use tables
    Command(s) completed successfully.

    begin transaction
    Command(s) completed successfully.

    drop database tables
    Cannot drop database "tables" because it is currently in use.

    rollback transaction
    Command(s) completed successfully.

    use master
    Command(s) completed successfully.

    begin transaction
    Command(s) completed successfully.

    drop database tables
    Msg 574, Level 16, State 0, Line 1
    DROP DATABASE statement cannot be used inside a user transaction.

    rollback transaction
    Command(s) completed successfully.



  • Conclusion: a non-shit DB combined with non-shit DB procedures would have saved their asses.



  • @da Doctah said:

    @Rick said:

    A table named databases and a database named tables. Really???
    Behind the end of this desk there's a box labeled "Cable Modem" on the front, and for the French-Canadian market, "Modem Cable" on the back.

    Tucked inside it is a smaller package labeled "Modem Cable" on the front, and for the French-Canadian market, "Cable Modem" on the back.

    The original contents of these two packages, purchased on separate occasions at two separate stores, are now attached to one another.

    Amusing, but that's more of an adjective-noun/noun-adjective pattern and a confusion of two different kinds of "cable"; so not quite the same thing...

  • ♿ (Parody)

    @BlueKnot said:

    Amusing, but that's more of an adjective-noun/noun-adjective pattern and a confusion of two different kinds of "cable"; so not quite the same thing...

    Yeah, modem / cable and database / table are totally different parts of speech when you start putting them together.



  • @blakeyrat said:

    Conclusion: FLASHBACK DATABASE TO BEFORE DROP in Oracle would have saved their asses.

    FTFY non-Oracle types.

    (Oracle doesn't see DDL as transactions, only DCL)



  • @Cassidy said:

    @blakeyrat said:

    Conclusion: FLASHBACK DATABASE TO BEFORE DROP in Oracle would have saved their asses.

    FTFY non-Oracle types.

    (Oracle doesn't see DDL as transactions, only DCL)

    I think that it is very likely that a system that has multi-terabyte size databases has that feature disabled. @Snoofle???

     



  • @Rick said:

    I think that it is very likely that a system that has multi-terabyte size databases has that feature disabled.
     

    I'm guessing so, too (given the size that the flash recovery area must grow to) - but I just wanted to illustrate the fact that the feature to recover dropped tables exists in Oracle.


Log in to reply