14 Replies - 2532 Views - Last Post: 11 July 2013 - 03:47 AM

#1 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 495
  • View blog
  • Posts: 3,300
  • Joined: 12-January 10

That moment in time where you just slam your head on the desk

Post icon  Posted 03 July 2013 - 11:38 AM

So today i made a little diddy of an sql update query.

Ran the test run on a few cases and it ran beautiful.

So then I went and did a full run.

What should have been 2500 rows affected turned into 78000 rows affected.-- at this point I slammed my head in Disgust.

Now my question is -- have you ever run a query with a test and it worked and then f'ed up the entire dataset because of the query?

this also can be on any programming f up....

Is This A Good Question/Topic? 0
  • +

Replies To: That moment in time where you just slam your head on the desk

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9500
  • View blog
  • Posts: 35,848
  • Joined: 12-June 08

Re: That moment in time where you just slam your head on the desk

Posted 03 July 2013 - 11:42 AM

I've done that once or twice before.. but quickly realized my final test will be ran against a copy of said entire dataset. Especially with giant ass queries that have hoodoo-voodoo at the heart and may or may not take on mysterious epsilon transitions and ruin my state process through a worm hole made of out unicorn farts.
Was This Post Helpful? 1
  • +
  • -

#3 no2pencil  Icon User is offline

  • Admiral Fancy Pants
  • member icon

Reputation: 5382
  • View blog
  • Posts: 27,350
  • Joined: 10-May 07

Re: That moment in time where you just slam your head on the desk

Posted 03 July 2013 - 11:46 AM

Working on the software creativecoding & I wrote that I use at my shop, I create a mysql dump of the customer list before working on code. There is development & production code, but only one mysql server/table. One time I forgot & something happened & I lost six customers data.

The reason I started doing the backups is because something happened & everyone wound up with the same phone number. I was able to go back & get them (from the original system), & I really really don't want to do that again.
Was This Post Helpful? 0
  • +
  • -

#4 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5633
  • View blog
  • Posts: 12,086
  • Joined: 02-June 10

Re: That moment in time where you just slam your head on the desk

Posted 03 July 2013 - 11:51 AM

Quote

Now my question is -- have you ever run a query with a test and it worked and then f'ed up the entire dataset because of the query?
this also can be on any programming f up....


Nope. Nobody has ever made a mistake before. Must just be you.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5908
  • View blog
  • Posts: 12,814
  • Joined: 16-October 07

Re: That moment in time where you just slam your head on the desk

Posted 03 July 2013 - 11:56 AM

In most Oracle tools, commit is turned off by default. In SQL Server, not so much...

For updates, I usually test a select version of the update.

You check, then check again, then again. Sometimes, what you were checking wasn't what you though you were checking.

We've all been there. Don't do it again.
Was This Post Helpful? 0
  • +
  • -

#6 jon.kiparsky  Icon User is online

  • Pancakes!
  • member icon


Reputation: 7964
  • View blog
  • Posts: 13,587
  • Joined: 19-March 11

Re: That moment in time where you just slam your head on the desk

Posted 03 July 2013 - 11:58 AM

At least you're not a surgeon.
Was This Post Helpful? 1
  • +
  • -

#7 Michael26  Icon User is offline

  • DIC-head, major DIC-head
  • member icon

Reputation: 362
  • View blog
  • Posts: 1,537
  • Joined: 08-April 09

Re: That moment in time where you just slam your head on the desk

Posted 03 July 2013 - 12:01 PM

Amen to that, one of the reasons i choose to be programmer is because you can make much more mistakes on computer and learn from them than a real human being :)
Was This Post Helpful? 0
  • +
  • -

#8 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 495
  • View blog
  • Posts: 3,300
  • Joined: 12-January 10

Re: That moment in time where you just slam your head on the desk

Posted 03 July 2013 - 01:08 PM

we do have a back up but unfortunately the "man" with the rights to do the back up is on vacation
Was This Post Helpful? 0
  • +
  • -

#9 BBeck  Icon User is offline

  • Here to help.
  • member icon


Reputation: 586
  • View blog
  • Posts: 1,306
  • Joined: 24-April 12

Re: That moment in time where you just slam your head on the desk

Posted 03 July 2013 - 02:15 PM

*
POPULAR

Surprisingly, I've never had that happen. Not that specifically anyway. I always write my predicates (WHERE clauses) first before writing SET and think about it two or three times before I hit Execute.


HOWEVER, back when I was a Jr. Jr. DBA I was left in charge of a major production database that one of the largest companies in the world lost approximately a million dollars for every hour it was down. No pressure, right? I did an update and somehow managed not to include the WHERE clause on the primary table, which was several million rows.

It didn't take long before I realized I had made the mistake. But the transaction was so huge that I wasn't getting any response to stop/rollback the transaction. In retrospect, I was probably okay just by stopping the transaction, if I had of waited long enough. But I was about half paniced, and I didn't know how to fire up another session and kill a process. I knew if the server crashed it would roll back the transaction. So, I ran into the server room and pulled the plug. (I so should have been fired. But there was no one who knew more than me at the time, believe it or not.) Fortunate for me, it did roll back the transaction. And even more fortunate for me, it didn't put a read/write head through one of the platters on the disk or cause massive data corruption, and so it all came back up like nothing happened. The whole system was back up in about 15 minutes and no one was the wiser.

However, that was the last time I wrote an UPDATE statement without starting with the WHERE clause.
Was This Post Helpful? 5
  • +
  • -

#10 trevster344  Icon User is offline

  • The Peasant
  • member icon

Reputation: 224
  • View blog
  • Posts: 1,507
  • Joined: 16-March 11

Re: That moment in time where you just slam your head on the desk

Posted 03 July 2013 - 05:40 PM

View PostMichael26, on 03 July 2013 - 01:01 PM, said:

Amen to that, one of the reasons i choose to be programmer is because you can make much more mistakes on computer and learn from them than a real human being :)/>/>


I became a programmer cause people wouldn't sue me for screwing up.... oh wait.. never mind they still do. Guess I don't know why I'm a programmer.

Back before I learned to use my brain I tested a query on a live database and ended up wiping out a good 80,000 customer records. Thank the gods the database system I use doesn't commit to the changes until I use a tiny command. ;)

This post has been edited by trevster344: 03 July 2013 - 05:42 PM

Was This Post Helpful? 1
  • +
  • -

#11 Ahmedn1  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 23
  • View blog
  • Posts: 557
  • Joined: 04-August 09

Re: That moment in time where you just slam your head on the desk

Posted 04 July 2013 - 12:04 AM

Always test the code against a backup database friends :D
Was This Post Helpful? 1
  • +
  • -

#12 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 285
  • View blog
  • Posts: 1,503
  • Joined: 07-April 08

Re: That moment in time where you just slam your head on the desk

Posted 05 July 2013 - 08:22 AM

I always put my update/delete/insert statements into a transaction and then based on the number of effected rows I will either commit or rollback the transaction. This way I have never directly effected production data. I have, however, had my fare share of critical failures. I once left a test connection string in code that was rolled out to production so for about a day all transactions on the live site were being placed in our testing database.
Was This Post Helpful? 0
  • +
  • -

#13 no2pencil  Icon User is offline

  • Admiral Fancy Pants
  • member icon

Reputation: 5382
  • View blog
  • Posts: 27,350
  • Joined: 10-May 07

Re: That moment in time where you just slam your head on the desk

Posted 09 July 2013 - 06:23 AM

Blocked my own ip address on my firewall last night. Took me 20 minutes to figure out why everything was down on the inside interface, but still worked from outside.
Was This Post Helpful? 0
  • +
  • -

#14 trevster344  Icon User is offline

  • The Peasant
  • member icon

Reputation: 224
  • View blog
  • Posts: 1,507
  • Joined: 16-March 11

Re: That moment in time where you just slam your head on the desk

Posted 09 July 2013 - 08:17 AM

Haha it's times like that you wish you would write down every thing you do in steps.
Was This Post Helpful? 0
  • +
  • -

#15 moopet  Icon User is offline

  • binary decision maker
  • member icon

Reputation: 339
  • View blog
  • Posts: 1,185
  • Joined: 02-April 09

Re: That moment in time where you just slam your head on the desk

Posted 11 July 2013 - 03:47 AM

My best effort was probably the following:

UPDATE `users` SET first_name='Martin'; WHERE id=1234;

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1