Sql epic fail

T

TheHiveMind

Banned
Joined
Jul 25, 2008
Messages
5,073
Reaction score
4
Location
The Big Wide World
So I was doing my usual DB admin stuffs..

and then all of a sudden things went horribly wrong, because I had a single character typing err..

it was like this:
update tbl1 set field1 = 'blabla' where field1 - 'blablabla'

the minus was supposed to be an equals sign :o

To my amazement sql parsed it and changed every one of those million+ records :( 3 years of routing data.. fark

Lucky for me we started backing up that table a few days ago. So it seems like it can be fixed (with some effort) and I don't have to throw myself off a building haha

I want an undo command =) Too much to ask for in this day and age?
 
eish, sorry to hear that

but good to hear that you've got a backup.

won't the rollback command help?

so easy to make a mistake (typo)
 
HumanoidTyphoon said:
So I was doing my usual DB admin stuffs..

and then all of a sudden things went horribly wrong, because I had a single character typing err..

it was like this:
update tbl1 set field1 = 'blabla' where field1 - 'blablabla'

the minus was supposed to be an equals sign :o

To my amazement sql parsed it and changed every one of those million+ records :( 3 years of routing data.. fark

Lucky for me we started backing up that table a few days ago. So it seems like it can be fixed (with some effort) and I don't have to throw myself off a building haha

I want an undo command =) Too much to ask for in this day and age?
Click to expand...

Use transactions.
 
I only use them for bulk-updates though. Also, as soon as I do a major update, I ALWAYS create a backup of the DB... Just in case, ya know... ;)
 
FarligOpptreden said:
I only use them for bulk-updates though. Also, as soon as I do a major update, I ALWAYS create a backup of the DB... Just in case, ya know... ;)
Click to expand...

you mean before you do a major update :p

afterwards it won't be of any assistance :D

I also do a backup (dbexport) before I delete old data, just in case somebody need some old data...
 
Lazy said:
Transaction will only be useful until you commit it. From there the problem would be the same ;c(
Click to expand...

Err, right but why would you commit a transaction you made a mistake in?
 
icyrus said:
Err, right but why would you commit a transaction you made a mistake in?
Click to expand...

In this case a transaction would have helped diddly squat. Since it's not an actual "error" to begin with, the transaction would have committed anyway. Even if you DO run all your scripts with a begin/rollback first [to test] and then begin/commit...it would still have happened ;).

At least the guy had a where clause, i can't remember how many times i've seen people (including myself) simply forget the where clause and:

UPDATE
SET x = y ..... oops ?

Fortunately in our environment you are NEVER allowed to run such scripts on a production/live environment BEFORE running it on a test environment [and actually testing it] . So worst case it happens on the test database.
 
diabolus said:
In this case a transaction would have helped diddly squat. Since it's not an actual "error" to begin with, the transaction would have committed anyway. Even if you DO run all your scripts with a begin/rollback first [to test] and then begin/commit...it would still have happened ;).

At least the guy had a where clause, i can't remember how many times i've seen people (including myself) simply forget the where clause and:

UPDATE
SET x = y ..... oops ?

Fortunately in our environment you are NEVER allowed to run such scripts on a production/live environment BEFORE running it on a test environment [and actually testing it] . So worst case it happens on the test database.
Click to expand...


Of course it would have helped. If he ran the transaction and saw that the results where not what he expected, he would have done a rollback and not a commit.

Who would commit a transaction that hasn't done what was expected?
 
On our live environment servers, the database is backed up (unloaded) every morning at 2. So if somebody should make a whoopsy, we got a fresh backup at least.
 
Set a trigger on your table to back it up whenever there is a change. We have a mission critical DB that has this in place.

Oh so helped me when I did a wicked update outside a transaction...

And to also help, I got in to the habit of first commenting my query before executing:

$ -- UPDATE blah_table WHERE col1='blah';
 
icyrus said:
Of course it would have helped. If he ran the transaction and saw that the results where not what he expected, he would have done a rollback and not a commit.
Click to expand...

Well, to put it this way :

It's not like you can run a SQL statement in a transaction , go and look at it for 30 minutes and then go "oh lets rollback " . Unless you're talking database restore kinda "rollback" [which to me is not the "transaction" i'm talking about].

------------------------Query start

Begin Tran
<update statement>

***this is your only chance to "test" and it has to be a pre-written statement ...

???Do you leave the transaction open? i.e. not actually executing the rollback statement?

Rollback Tran

*** you can not test your data here anymore, it's rolled back to previous state.

----------Query end
 
Last edited:
I was thinking more along the lines of:

Code: 
test=# begin;
BEGIN
test=# update test_table [...]
UPDATE 10400404
Now at this stage I would be suspecting something went wrong when my update that I expected to update 1 row as actually updated 10400404 rows.

So next would be:

Code: 
test=# rollback;
ROLLBACK

And all would be well.

Or I could do some selects before the rollback to see what exactly messed up but that seems redundant seeing as the erroneous update is a few lines up.
 
Transactions

I also had some very close encounters with "Committing suicide" ;) on our live servers.

The best part is we always "just want to do a quick update", and before you know it, you forgot that little "where" clause or something and your data is gone with the wind...

Glad you had a backup man!
 
x2 for transactions.

Do the transaction check the rows affected, if it's more than expected then rollback.

And in any case backup before doing updates of that manner, I might be deluded but I was under the impression these types of commands run directly on the DB are very rare?
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X