4 ms·

Not so much a bug, but this incident cut my lifespan by about 5 years or so: I was fixing an account balance on a customer's master database. Any change that

Not so much a bug, but this incident cut my lifespan by about 5 years or so: I was fixing an account balance on a customer's master database. Any change that happens on the master gets replicated to 30 branches, usually at 10 minute intervals. I wrote the UPDATE statement, highlighted it, and pressed "Execute". Unfortunately, I didn't select the WHERE clause, so I basically gave all their customers (85000) a 50c credit balance. The IDE I was using also had a bug that caused it to ignore the auto-commit setting (which was turned off), so it basically committed the transaction. First I tried a ROLLBACK, which failed obviously. Realizing I had to act quickly, I disconnected the network cable (I was working on the DBMS server) to stop replication. I extracted the transaction log from the current database into a textfile (a few hundred MB) and restored the database from the most recent backup. Then I basically ran the extracted transaction log as SQL scripts against the database, hoping that it wouldn't fall over. I didn't want to do a normal restore because I was afraid of it showing up in the logs. Within a very stressful 60 minutes I had everything back to normal. I never told a soul IRL about it.

cake 17y ago
I had one of these too. I had to UPDATE a line of a few customers order's logs on a production server. The database was huge (several million lines). After a few seconds I thought it was taking too much time only to realise I forgot one AND in the WHERE... I struggled with the backup but managed to get the data back just after the first call.
- kls 17y ago
  I got into the habit a long time ago of writing my where clause first and then generally writing the rest as a select clause. Then converting the select into a update or delete. I don't always do the select thing it depends on what part of the DB I am working on. If it is a pretty complex statement I tend to write the select just to make sure. I have a guy that is better than me at SQL but he still has not taken my advice and every now and then I hear cussing coming from down the hall. He is good at backing things up but his process takes a lot of time when he messes up. I prefer the ounce of prevention path.
  - jacquesm 17y ago
    I do almost the same thing: I first write out a select that will show me the records I'm about to modify, then replace the select + fields with the update. And I try to keep modifying live databases using interactive SQL to an absolute minimum. Check three times, then hit enter.
jacquesm 17y ago
Which is one of my pet gripes with SQL, that the default is to affect all records on update, that should have been: UPDATE tablename SET xxx='newvalue' WHERE ALLRECORDS; Or something to that effect.
- duskwuff 17y ago
  MySQL has --safe-updates (aka --i-am-a-dummy), which will refuse to execute UPDATE and DELETE statements without a key in the WHERE clause.
  - jacquesm 17y ago
    Would you feel the same if in unix the default for 'rm' was 'rm -rf *' ?
Calamitous 17y ago
I've done almost this exact thing. The "run only the SQL that's highlighted" feautre is handy, but it can cause serious bad juju on a production box. Unfortunately, in my case, I ended up having to rebuild all the data from week-old backups and transaction logs.

cake 17y ago

I had one of these too. I had to UPDATE a line of a few customers order's logs on a production server. The database was huge (several million lines). After a few seconds I thought it was taking too much time only to realise I forgot one AND in the WHERE... I struggled with the backup but managed to get the data back just after the first call.

kls 17y ago
I got into the habit a long time ago of writing my where clause first and then generally writing the rest as a select clause. Then converting the select into a update or delete. I don't always do the select thing it depends on what part of the DB I am working on. If it is a pretty complex statement I tend to write the select just to make sure. I have a guy that is better than me at SQL but he still has not taken my advice and every now and then I hear cussing coming from down the hall. He is good at backing things up but his process takes a lot of time when he messes up. I prefer the ounce of prevention path.
- jacquesm 17y ago
  I do almost the same thing: I first write out a select that will show me the records I'm about to modify, then replace the select + fields with the update. And I try to keep modifying live databases using interactive SQL to an absolute minimum. Check three times, then hit enter.

jacquesm 17y ago

Which is one of my pet gripes with SQL, that the default is to affect all records on update, that should have been: UPDATE tablename SET xxx='newvalue' WHERE ALLRECORDS; Or something to that effect.

duskwuff 17y ago
MySQL has --safe-updates (aka --i-am-a-dummy), which will refuse to execute UPDATE and DELETE statements without a key in the WHERE clause.
- jacquesm 17y ago
  Would you feel the same if in unix the default for 'rm' was 'rm -rf *' ?

Calamitous 17y ago

I've done almost this exact thing. The "run only the SQL that's highlighted" feautre is handy, but it can cause serious bad juju on a production box. Unfortunately, in my case, I ended up having to rebuild all the data from week-old backups and transaction logs.