Syntactically correct, but with disastrous consequences - Tilted Forum Project Discussion Community

ratbastid · 03-14-2005, 10:27 AM

Ever written something that passed syntax (and maybe even compiled), but that did something so far off from what you intended you can't imagine such a thing passed your fingers?

This morning, half-awake, I typed:

UPDATE invoice_item SET SKU = 'harl100' AND invoice_ID = 28448;

I meant for that "AND" to be "WHERE", so I was updating the one record associated with Invoice 28448. but... I hadn't had my coffee yet. MySQL raced off and did my bidding. When it replied, "4452 records updated", I damn near shit myself. I just set ALL the SKUs in the entire database to 'harl100'!

It's perfectly valid SQL, what I wrote. The AND clause is a syntactically correct no-op.

Fortunately, I'm keeping an association between invoice_item and PO_item. I was able to recover SKUs for all orders that have been processed and turned into purchase orders. That left about 100 records that my client is going to manually restore from order notification email he receives. He's pretty pissed, but glad I called immediately and had a solution that restored most of the data anyway.

Reminds me of a similar mistake I once meant where, typing too fast, I said:

UPDATE table SET field = 'newvalue' where ID + 1;

"ID + 1" tests true for ALL values of ID. Gulp.

03-14-2005, 12:43 PM

Two words: Test Environment

RAGEAngel9 · 03-14-2005, 12:48 PM

Two more:
Redundant Backups

That's the fun of programming (we'll count sql in this). If you don't know what you're doing, look out.

cyrnel · 03-14-2005, 01:10 PM

I recall being 36hours on the job (our own company), accidentally inserting a "?" in an rm command, and blowing away hundreds of customer domains from my master nameserver. Fark if that doesn't wake you back up.

Rawb · 03-14-2005, 01:37 PM

Yah, the last place I was working we had a mishmash of tools, and so there were a number of occasions where we had to run SQL commands directly on the database. Eventually we got into a good system where we would write scripts that would generate SQL to do the things we wanted. We then saved that SQL, and ran it after peer review. But, every once in a while, you were doing something simple, and BAM! You've just screwed something up.

We were a transaction processing center, so there was a constant flow of transactions into the system. On one particularly sad moment, I tried to run a simple table upgrade with the system still live, thinking that the ALTER TABLE would only take a second. Little did I know, instead of doing the normal update thing, it decided to corrupt the tables. The good news was that I made a backup before I started the alter table, the bad news is that it was 8 minutes later when I discovered that it decided to empty out the tables and corrupt them. Oh, man, was that ever fun restoring from backups, and running through the MySQL bin-log by hand trying to pick out which things needed to be re-entered and which things somehow made it back in after the restore. (JBoss was doing some funky things with the db writes)

And that is my story of my moment of shame.

kel · 03-14-2005, 04:25 PM

I always thought rm was retarded. Unix needs an intermediate step for unimportant deletions so you don't do things permanently unless absolutely necessary.

Bring on the 1337 rebuttals UNIX are perfect!

cyrnel · 03-14-2005, 04:38 PM

Unix is awesome, logical, predictable, powerful, but even a Paul Vixie clone at my beck & call would be imperfect. Perfection is a warning label that really means: "religion ahead."

kel · 03-14-2005, 07:09 PM

FYI by intermediate step I mean something akin to the recycle bin so RM only get's used for heavy weight stuff

RAGEAngel9 · 03-14-2005, 07:17 PM

Quote:

Originally Posted by kel

FYI by intermediate step I mean something akin to the recycle bin so RM only get's used for heavy weight stuff

Dude, not to be too much of a smart ass, but
unix man rm

Read the opening paragraph.
If you don't currently need the force option (or the other exceptions), then it's the fault of your shell not rm explicitly. You can change your alias' if need be.

It's not a recycling bin, but that's not all that foolproof either.

kel · 03-14-2005, 08:03 PM

Quote:

Originally Posted by RAGEAngel9

Dude, not to be too much of a smart ass, but
unix man rm

Read the opening paragraph.
If you don't currently need the force option (or the other exceptions), then it's the fault of your shell not rm explicitly. You can change your alias' if need be.

It's not a recycling bin, but that's not all that foolproof either.

I am not sure what you are trying to point out. How does rm provide an intermediate phase between total deletion and being marked for deletion + moved to a separate place in the directory tree?

I personally use a script that moves thing to a faux recycling bin in my home directory and use clearcase to protect the rest. For some funny reason I have never had an accident with rm :-P Maybe it's because I actually have time to think about what I'm doing after I hit enter before unlinking the contents of a directory.
No system is foolproof, but rm is asking for (and giving) thousands of people trouble which should hint to the problem.

Rangsk · 03-14-2005, 08:34 PM

There's always the standard c/c++/java mistake:

Code:

void myfunc(char* p)
{
  if (p = NULL)
  {
    cout << "p is null" << endl;
  }

  // continue on assuming p isn't NULL
}

}

This is a guaranteed segmentation fault.

There was a movement a while back to write

Code:

if (NULL == p)

instead of

Code:

if (p == NULL)

since

Code:

if (NULL = p)

is NOT syntactically correct, but most (including me) just think it looks odd and hard to read.

Another common mistake:

Code:

if (...);
{
  // code
}

or

Code:

while(...);
{
  //code
}

This, of course, branches to or loops within a blank line instead of executing the code in the block.

Things like this can be disasterous.

RAGEAngel9 · 03-14-2005, 09:37 PM

Quote:

Originally Posted by kel

I am not sure what you are trying to point out. How does rm provide an intermediate phase between total deletion and being marked for deletion + moved to a separate place in the directory tree?

I personally use a script that moves thing to a faux recycling bin in my home directory and use clearcase to protect the rest. For some funny reason I have never had an accident with rm :-P Maybe it's because I actually have time to think about what I'm doing after I hit enter before unlinking the contents of a directory.
No system is foolproof, but rm is asking for (and giving) thousands of people trouble which should hint to the problem.

I was just trying to point out how the normal rm command prompts you for most types of files before deleting if you don't use the force option.

madcow · 03-15-2005, 03:30 PM

I always put "Begin tran" in front of my updates and deletes. There's just too much data to screw up where I work. It's saved my ass a couple times. If I see more records affected than I was expecting "rollback" otherwise "commit".

irseg · 03-15-2005, 03:42 PM

Reminds me of one time when I got a call from a coworker: "Uhh, there's no way to undelete rows in SQL after you delete them, is there?" "Nope, why?" "...I kind of forgot to add the WHERE clause..." That sucked.

Daily backups are nice. Also if you do a lot of updates and deletes that only affect one record, get in the habit of adding a LIMIT 1 at the end so even if you screw up, the damage is kept to a minimum.

RAGEAngel9 · 03-16-2005, 01:47 PM

Sort of on topic/ mostly an update
KDE 3.4 Released

Apparently, this version has a trashcan.

Fallon · 03-16-2005, 04:54 PM

Is it rm's fault that it causes problems or is it the user?

Some of my problems have been improperly initializing for loops that never ended. The program was supposed to ping another server(the teacher basically wanted us to write a hacking tool that'd ping another server for however many times we wanted to see if we could bring it down just thru that). Well, the for loop never ended, and I couldn't exit out. By the time I could remotely connect to my box in another terminal and kill the app, over 3000 pings had been sent out to a non-existant system and the host unreachable message constantly popped up on the terminal that ran the app. Ya that sucked.

With SQL, luckily I haven't deleted my player database...I have accidentally deleted tables, but they were unused or in the design phase so that wasn't that bad I suppose.

Ya, I'm boring. Nothing to serious but luckily I haven't started working with computers in a job capacity. When I do, I'll give you guys those stories.

cyrnel · 03-16-2005, 05:12 PM

Quote:

Is it rm's fault that it causes problems or is it the user?

May as well blame free(). It did what I asked of it. Problem was me, too many systems, too many cooks, and growing pains.

Were rm still without a safety net or alternative then it would be the fault of the release.

Pragma · 03-17-2005, 07:07 AM

The best way to come up with a "safe" version of rm:

alias del="rm -i"

Use del when you're not very sure you want to kill things.

skaven · 03-18-2005, 12:59 AM

This isn't exactly programming, but it was something that had unexpected disasterous results. I was working on a server during a system failure, so there were two or three of us admins all on the system at the same time, trying to figure out why the system had puked on itself. We were all logged in as root, reading logfiles, etc. Suddenly all the files begin vanishing off the hard drive. Within a few seconds the whole system had toasted itself, due to /usr/lib and /lib being blown away.

In the post-mortem we finally tracked down what had happened. Apparently one of us was in a root shell and had created some temporary files in /tmp/blah. When we were finished with the temp files, the admin did a standard 'cd /tmp/blah' and 'rm -rf *'. This was harmless. However, this admin then immediately did a ^D and logged out that shell. This then wrote out the .bash_history file in /root. Meanwhile, one of the other admins was doing something repeatedly, and using '!!' to repeat a long command. This works quite well when you're the only one on the system, but once .bash_history got updated, the next '!!' that he typed ran "rm -rf *" instead of the long command he was expecting. It was just fate that his pwd happened to be / at the time.

Needless to say, after this mishap we all are much more careful about using shell shortcuts as root (as well as ganging up on broken servers). Generally we all just gather in one cube with a couple systems in it and let a couple people type so that everybody is on the same page while we're working.

asshopo · 03-21-2005, 11:55 AM

I've done things like that... the rush of "omg, wtf did I just do?". I've had co-wokers come to me and say "ya, I did update tblTable set field = 1, then accidently hit the execute button". I write my update/delete statements "update tblTable where 1 = 2" and then do the rest. It has saved my ass so many times

.

Then I have done things like:

Code:

 AND SystemGenerated = CASE WHEN @SystemGenerated = 1 THEN 1 ELSE 0 END,

It works, but its totally unnessessary. I'm sure I had something else in mind, but ya, this would have worked just fine:

Code:

 AND SystemGenerated = @SystemGenerated

theFez · 03-21-2005, 08:10 PM

My personal favorite, while deleting a record from a MySQL database i got something like 4000 rows affected instead of the 1 i expected. i took a close look at my query and realized i had run

Code:

delete from table where nodeid - 1234;

turned out i only had one row left in my db. Apparently, using minus deletes everything except the specified row. Now if you look at where minus and equal keys are on the keyboard and how similar they look, you would think this would be better documented behavior.

fortunately this was on the catalog of an ecommerce store, we were able to restore from backup in about 15 minutes and only lost a half day work for the product development team. if it had been the customer or orders table it would have been a much bigger deal.

we wrote scripts to handle most of our common database functions we had been doing from the shell after that.

Rekna · 03-21-2005, 08:46 PM

one of my old professors was cleaning up all the .o files in his home directory (needed space) so he typed this command

rm -rf *.o looks pretty good until he realized he didn't release shift in time after typing * and ended up with rm -rf>o

Catmandu · 03-29-2005, 07:58 AM

I support a software package which controls our company's store replenishment. It contains a pretty robust high-level pseudo-SQL which the clients can run. I got a call the day AFTER they ran a set of selects with "or <>" instead of "and <>". They ended up setting the store-level presentation stock for every item in our system (2 million +) to 10. Thankfully the warehouse people were wondering why we were trying to send 10 pairs of size 14 hiking boots to a store across the country. It only took me about 8 hours to repair the database.

joemc91 · 04-02-2005, 02:28 PM

This is exactly why I love having daily backups of my databases. If something happens, just untar the backup file and restore the old data. Anyways, my favorite one I did was working in the root www directory on my webserver, which hosts about 9 websites. I went and typed rm -Rf * for some reason and ended up deleting all my websites. Oops, thanks to the daily backups, it wasn't that big a deal.

The other good one I've done was when I started a net admin job at a Boys and Girls Club. I was trying to identify which servers were which so I could shut one down. Conveniently they were all labeled, although much to my chagrin, incorrectly. I shut down the server I thought was the Exchange server (in the middle of upgrading to 5.5 to 2k) but accidentally shut down the main server, which was also the firewall/router. These sorts of accidents were fairly common over my time there but since their whole infrastructure was rebuilt in 2 months, they didn't mind too much.

03-14-2005, 10:27 AM	#1 (permalink)
ratbastid Darth Papa Location: Yonder	Syntactically correct, but with disastrous consequences Ever written something that passed syntax (and maybe even compiled), but that did something so far off from what you intended you can't imagine such a thing passed your fingers? This morning, half-awake, I typed: UPDATE invoice_item SET SKU = 'harl100' AND invoice_ID = 28448; I meant for that "AND" to be "WHERE", so I was updating the one record associated with Invoice 28448. but... I hadn't had my coffee yet. MySQL raced off and did my bidding. When it replied, "4452 records updated", I damn near shit myself. I just set ALL the SKUs in the entire database to 'harl100'! It's perfectly valid SQL, what I wrote. The AND clause is a syntactically correct no-op. Fortunately, I'm keeping an association between invoice_item and PO_item. I was able to recover SKUs for all orders that have been processed and turned into purchase orders. That left about 100 records that my client is going to manually restore from order notification email he receives. He's pretty pissed, but glad I called immediately and had a solution that restored most of the data anyway. Reminds me of a similar mistake I once meant where, typing too fast, I said: UPDATE table SET field = 'newvalue' where ID + 1; "ID + 1" tests true for ALL values of ID. Gulp.

03-14-2005, 04:25 PM	#6 (permalink)
kel WARNING: FLAMMABLE Location: Ask Acetylene	I always thought rm was retarded. Unix needs an intermediate step for unimportant deletions so you don't do things permanently unless absolutely necessary. Bring on the 1337 rebuttals UNIX are perfect! __________________ "It better be funny"

03-14-2005, 07:09 PM	#8 (permalink)
kel WARNING: FLAMMABLE Location: Ask Acetylene	FYI by intermediate step I mean something akin to the recycle bin so RM only get's used for heavy weight stuff __________________ "It better be funny"

03-14-2005, 08:34 PM	#11 (permalink)
Rangsk Crazy Location: San Diego, CA	There's always the standard c/c++/java mistake: Code: void myfunc(char* p) { if (p = NULL) { cout << "p is null" << endl; } // continue on assuming p isn't NULL } } This is a guaranteed segmentation fault. There was a movement a while back to write Code: if (NULL == p) instead of Code: if (p == NULL) since Code: if (NULL = p) is NOT syntactically correct, but most (including me) just think it looks odd and hard to read. Another common mistake: Code: if (...); { // code } or Code: while(...); { //code } This, of course, branches to or loops within a blank line instead of executing the code in the block. Things like this can be disasterous. __________________ "Don't believe everything you read on the internet. Except this. Well, including this, I suppose." -- Douglas Adams

03-15-2005, 03:30 PM	#13 (permalink)
madcow Crazy Location: The state of denial	I always put "Begin tran" in front of my updates and deletes. There's just too much data to screw up where I work. It's saved my ass a couple times. If I see more records affected than I was expecting "rollback" otherwise "commit". __________________ Smoke me a kipper, I'll be back for breakfast.

03-14-2005, 12:43 PM	#2 (permalink)
zen_tom Guest	Two words: Test Environment

03-14-2005, 12:48 PM	#3 (permalink)
RAGEAngel9 Insane Location: Vermont	Two more: Redundant Backups That's the fun of programming (we'll count sql in this). If you don't know what you're doing, look out.

03-14-2005, 01:10 PM	#4 (permalink)
cyrnel Adequate Location: In my angry-dome.	I recall being 36hours on the job (our own company), accidentally inserting a "?" in an rm command, and blowing away hundreds of customer domains from my master nameserver. Fark if that doesn't wake you back up.

03-14-2005, 01:37 PM	#5 (permalink)
Rawb Crazy Location: Salt Town, UT	Yah, the last place I was working we had a mishmash of tools, and so there were a number of occasions where we had to run SQL commands directly on the database. Eventually we got into a good system where we would write scripts that would generate SQL to do the things we wanted. We then saved that SQL, and ran it after peer review. But, every once in a while, you were doing something simple, and BAM! You've just screwed something up. We were a transaction processing center, so there was a constant flow of transactions into the system. On one particularly sad moment, I tried to run a simple table upgrade with the system still live, thinking that the ALTER TABLE would only take a second. Little did I know, instead of doing the normal update thing, it decided to corrupt the tables. The good news was that I made a backup before I started the alter table, the bad news is that it was 8 minutes later when I discovered that it decided to empty out the tables and corrupt them. Oh, man, was that ever fun restoring from backups, and running through the MySQL bin-log by hand trying to pick out which things needed to be re-entered and which things somehow made it back in after the restore. (JBoss was doing some funky things with the db writes) And that is my story of my moment of shame.

03-14-2005, 04:38 PM	#7 (permalink)
cyrnel Adequate Location: In my angry-dome.	Unix is awesome, logical, predictable, powerful, but even a Paul Vixie clone at my beck & call would be imperfect. Perfection is a warning label that really means: "religion ahead."

03-15-2005, 03:42 PM	#14 (permalink)
irseg Junkie Location: Florida	Reminds me of one time when I got a call from a coworker: "Uhh, there's no way to undelete rows in SQL after you delete them, is there?" "Nope, why?" "...I kind of forgot to add the WHERE clause..." That sucked. Daily backups are nice. Also if you do a lot of updates and deletes that only affect one record, get in the habit of adding a LIMIT 1 at the end so even if you screw up, the damage is kept to a minimum.

03-16-2005, 01:47 PM	#15 (permalink)
RAGEAngel9 Insane Location: Vermont	Sort of on topic/ mostly an update KDE 3.4 Released Apparently, this version has a trashcan.

03-16-2005, 04:54 PM	#16 (permalink)
Fallon Junkie Location: RI	Is it rm's fault that it causes problems or is it the user? Some of my problems have been improperly initializing for loops that never ended. The program was supposed to ping another server(the teacher basically wanted us to write a hacking tool that'd ping another server for however many times we wanted to see if we could bring it down just thru that). Well, the for loop never ended, and I couldn't exit out. By the time I could remotely connect to my box in another terminal and kill the app, over 3000 pings had been sent out to a non-existant system and the host unreachable message constantly popped up on the terminal that ran the app. Ya that sucked. With SQL, luckily I haven't deleted my player database...I have accidentally deleted tables, but they were unused or in the design phase so that wasn't that bad I suppose. Ya, I'm boring. Nothing to serious but luckily I haven't started working with computers in a job capacity. When I do, I'll give you guys those stories.

03-17-2005, 07:07 AM	#18 (permalink)
Pragma I am Winter Born Location: Alexandria, VA	The best way to come up with a "safe" version of rm: alias del="rm -i" Use del when you're not very sure you want to kill things.

03-18-2005, 12:59 AM	#19 (permalink)
skaven Insane Location: Austin, TX	This isn't exactly programming, but it was something that had unexpected disasterous results. I was working on a server during a system failure, so there were two or three of us admins all on the system at the same time, trying to figure out why the system had puked on itself. We were all logged in as root, reading logfiles, etc. Suddenly all the files begin vanishing off the hard drive. Within a few seconds the whole system had toasted itself, due to /usr/lib and /lib being blown away. In the post-mortem we finally tracked down what had happened. Apparently one of us was in a root shell and had created some temporary files in /tmp/blah. When we were finished with the temp files, the admin did a standard 'cd /tmp/blah' and 'rm -rf '. This was harmless. However, this admin then immediately did a ^D and logged out that shell. This then wrote out the .bash_history file in /root. Meanwhile, one of the other admins was doing something repeatedly, and using '!!' to repeat a long command. This works quite well when you're the only one on the system, but once .bash_history got updated, the next '!!' that he typed ran "rm -rf " instead of the long command he was expecting. It was just fate that his pwd happened to be / at the time. Needless to say, after this mishap we all are much more careful about using shell shortcuts as root (as well as ganging up on broken servers). Generally we all just gather in one cube with a couple systems in it and let a couple people type so that everybody is on the same page while we're working.

03-21-2005, 11:55 AM	#20 (permalink)
asshopo Insane Location: Michigan	I've done things like that... the rush of "omg, wtf did I just do?". I've had co-wokers come to me and say "ya, I did update tblTable set field = 1, then accidently hit the execute button". I write my update/delete statements "update tblTable where 1 = 2" and then do the rest. It has saved my ass so many times . Then I have done things like: Code: AND SystemGenerated = CASE WHEN @SystemGenerated = 1 THEN 1 ELSE 0 END, It works, but its totally unnessessary. I'm sure I had something else in mind, but ya, this would have worked just fine: Code: AND SystemGenerated = @SystemGenerated __________________ Patterns have a habit of repeating themselves.

03-21-2005, 08:10 PM	#21 (permalink)
theFez Crazy Location: here and there	My personal favorite, while deleting a record from a MySQL database i got something like 4000 rows affected instead of the 1 i expected. i took a close look at my query and realized i had run Code: delete from table where nodeid - 1234; turned out i only had one row left in my db. Apparently, using minus deletes everything except the specified row. Now if you look at where minus and equal keys are on the keyboard and how similar they look, you would think this would be better documented behavior. fortunately this was on the catalog of an ecommerce store, we were able to restore from backup in about 15 minutes and only lost a half day work for the product development team. if it had been the customer or orders table it would have been a much bigger deal. we wrote scripts to handle most of our common database functions we had been doing from the shell after that. __________________ # chmod 111 /bin/Laden

03-21-2005, 08:46 PM	#22 (permalink)
Rekna Junkie	one of my old professors was cleaning up all the .o files in his home directory (needed space) so he typed this command rm -rf .o looks pretty good until he realized he didn't release shift in time after typing and ended up with rm -rf>o

03-29-2005, 07:58 AM	#23 (permalink)
Catmandu Insane Location: Seattle	I support a software package which controls our company's store replenishment. It contains a pretty robust high-level pseudo-SQL which the clients can run. I got a call the day AFTER they ran a set of selects with "or <>" instead of "and <>". They ended up setting the store-level presentation stock for every item in our system (2 million +) to 10. Thankfully the warehouse people were wondering why we were trying to send 10 pairs of size 14 hiking boots to a store across the country. It only took me about 8 hours to repair the database.

04-02-2005, 02:28 PM	#24 (permalink)
joemc91 Insane Location: Chicago	This is exactly why I love having daily backups of my databases. If something happens, just untar the backup file and restore the old data. Anyways, my favorite one I did was working in the root www directory on my webserver, which hosts about 9 websites. I went and typed rm -Rf * for some reason and ended up deleting all my websites. Oops, thanks to the daily backups, it wasn't that big a deal. The other good one I've done was when I started a net admin job at a Boys and Girls Club. I was trying to identify which servers were which so I could shut one down. Conveniently they were all labeled, although much to my chagrin, incorrectly. I shut down the server I thought was the Exchange server (in the middle of upgrading to 5.5 to 2k) but accidentally shut down the main server, which was also the firewall/router. These sorts of accidents were fairly common over my time there but since their whole infrastructure was rebuilt in 2 months, they didn't mind too much.