Hello everyone,
I had a horrible thing happen to me this morning and wanted to make it
known to the community.
I needed to delete a record from a very large table (yes, it was
backed up) and like the cli interface of mysql. I ran this query:
delete from tablename where id - 12345;
Notice that I accidentally hit the dash (-) instead of the equal (=).
It proved to be disasterous as it deleted all the records from that
table.
Lucky for me I had a backup from last night and not too many records
were added since then and I was able to restore.
For the record, I am aware of the "select before delete" method, but
didn't use it in this one instance and it meant a few hours restoring
data.
Just wanted to throw this out and see if others had possible solutions
for working with the mysql cli interface for maybe setting up "rules"
for it to cancel a query if it contains a certain character (like the
dash). Fat chance there is, but I thought I'd ask.
Hope this helps someone,
Kevin

Kevin schrieb:
Hello everyone,

I had a horrible thing happen to me this morning and wanted to make it
known to the community.

I needed to delete a record from a very large table (yes, it was
backed up) and like the cli interface of mysql. I ran this query:

delete from tablename where id - 12345;

Notice that I accidentally hit the dash (-) instead of the equal (=).
It proved to be disasterous as it deleted all the records from that
table.

Lucky for me I had a backup from last night and not too many records
were added since then and I was able to restore.

For the record, I am aware of the "select before delete" method, but
didn't use it in this one instance and it meant a few hours restoring
data.

Just wanted to throw this out and see if others had possible solutions
for working with the mysql cli interface for maybe setting up "rules"
for it to cancel a query if it contains a certain character (like the
dash). Fat chance there is, but I thought I'd ask.

Hope this helps someone,
Kevin

this one use LIMIT.
If you want to delete specific rows alway use LIMIT.

even if you f**k up you "just" have deleted one row.

If you are luck it is an old one and easy restoreable.

Barry

6/21/06, Barry <Barry (AT) flyerheaven (DOT) dewrote:
Kevin schrieb:
Hello everyone,

I had a horrible thing happen to me this morning and wanted to make it
known to the community.

I needed to delete a record from a very large table (yes, it was
backed up) and like the cli interface of mysql. I ran this query:

delete from tablename where id - 12345;

Notice that I accidentally hit the dash (-) instead of the equal (=).
It proved to be disasterous as it deleted all the records from that
table.

Lucky for me I had a backup from last night and not too many records
were added since then and I was able to restore.

For the record, I am aware of the "select before delete" method, but
didn't use it in this one instance and it meant a few hours restoring
data.

Just wanted to throw this out and see if others had possible solutions
for working with the mysql cli interface for maybe setting up "rules"
for it to cancel a query if it contains a certain character (like the
dash). Fat chance there is, but I thought I'd ask.

Hope this helps someone,
Kevin

this one use LIMIT.
If you want to delete specific rows alway use LIMIT.

even if you f**k up you "just" have deleted one row.

If you are luck it is an old one and easy restoreable.

Hi Barry,

So if I understand you correctly, I'd do the following:

delete from tablename where id - 12345 limit 1;

Is that correct?

Hi,

Jun 21, 2006, at 12:24 PM, Kevin wrote:

6/21/06, Barry <Barry (AT) flyerheaven (DOT) dewrote:
>Kevin schrieb:
>Hello everyone,
>>
>I had a horrible thing happen to me this morning and wanted to
>make it
>known to the community.
>>
>I needed to delete a record from a very large table (yes, it was
>backed up) and like the cli interface of mysql. I ran this query:
>>
>delete from tablename where id - 12345;
>>
>Notice that I accidentally hit the dash (-) instead of the equal
>(=).
>It proved to be disasterous as it deleted all the records from that
>table.
>>
>Lucky for me I had a backup from last night and not too many
>records
>were added since then and I was able to restore.
>>
>For the record, I am aware of the "select before delete" method,
>but
>didn't use it in this one instance and it meant a few hours
>restoring
>data.
>>
>Just wanted to throw this out and see if others had possible
>solutions
>for working with the mysql cli interface for maybe setting up
>"rules"
>for it to cancel a query if it contains a certain character
>(like the
>dash). Fat chance there is, but I thought I'd ask.
>>
>Hope this helps someone,
>Kevin
>>
>this one use LIMIT.
>If you want to delete specific rows alway use LIMIT.
>>
>even if you f**k up you "just" have deleted one row.
>>
>If you are luck it is an old one and easy restoreable.
>>
>
Hi Barry,

So if I understand you correctly, I'd do the following:

delete from tablename where id - 12345 limit 1;

Is that correct?

That still will delete one row, so you still might need a backup to
get back that row.

Another option you might want to look into is using the
updates option to the command line client. This will prevent you
from doing DELETEs and UPDATEs that don't use an index properly. For
example, in your case deleting the entire table would have been
prevented, whereas the correct id = 12345 would be allowed (assuming
id is the PK or index). I generally always use that option on a
production machine.

It does a few other things as well (LIMIT 1000, max_join_size), so
make sure you check it out before using it. It used to be called
am-a-dummy mode (that option works too), so you might see it referred
to as that in some places.

Regards,

Harrison

Wed, 21 Jun 2006 11:12:40 -0400, Kevin wrote:

Hello everyone,

I had a horrible thing happen to me this morning and wanted to make it
known to the community.

I needed to delete a record from a very large table (yes, it was
backed up) and like the cli interface of mysql. I ran this query:

delete from tablename where id - 12345;

Notice that I accidentally hit the dash (-) instead of the equal (=).
It proved to be disasterous as it deleted all the records from that
table.

Lucky for me I had a backup from last night and not too many records
were added since then and I was able to restore.

For the record, I am aware of the "select before delete" method, but
didn't use it in this one instance and it meant a few hours restoring
data.

Just wanted to throw this out and see if others had possible solutions
for working with the mysql cli interface for maybe setting up "rules"
for it to cancel a query if it contains a certain character (like the
dash). Fat chance there is, but I thought I'd ask.

Hope this helps someone,
Kevin

This sounds awfully like doing an rm -rf somefile. * (with an accidental space
in between the . and the *).

Most unix/linux geeks I know (including myself) only ever do this once.

Humans like to learn the hard way, it seems :-)
-- boof

Thursday 22 June 2006 03:24, Harrison Fisk wrote:
Hi,

Jun 21, 2006, at 12:24 PM, Kevin wrote:
6/21/06, Barry <Barry (AT) flyerheaven (DOT) dewrote:
>Kevin schrieb:
>Hello everyone,
>>
>I had a horrible thing happen to me this morning and wanted to
>>
>make it
>>
>known to the community.
>>
>I needed to delete a record from a very large table (yes, it was
>backed up) and like the cli interface of mysql. I ran this query:
>>
>delete from tablename where id - 12345;
>>
>Notice that I accidentally hit the dash (-) instead of the equal
>>
>(=).
>>
>It proved to be disasterous as it deleted all the records from that
>table.
>>
>Lucky for me I had a backup from last night and not too many
>>
>records
>>
>were added since then and I was able to restore.
>>
>For the record, I am aware of the "select before delete" method,
>>
>but
>>
>didn't use it in this one instance and it meant a few hours
>>
>restoring
>>
>data.
>>
>Just wanted to throw this out and see if others had possible
>>
>solutions
>>
>for working with the mysql cli interface for maybe setting up
>>
>"rules"
>>
>for it to cancel a query if it contains a certain character
>>
>(like the
>>
>dash). Fat chance there is, but I thought I'd ask.
>>
>Hope this helps someone,
>Kevin
>>
>this one use LIMIT.
>If you want to delete specific rows alway use LIMIT.
>>
>even if you f**k up you "just" have deleted one row.
>>
>If you are luck it is an old one and easy restoreable.
>
Hi Barry,

So if I understand you correctly, I'd do the following:

delete from tablename where id - 12345 limit 1;

Is that correct?

That still will delete one row, so you still might need a backup to
get back that row.

Another option you might want to look into is using the
updates option to the command line client. This will prevent you
from doing DELETEs and UPDATEs that don't use an index properly. For
example, in your case deleting the entire table would have been
prevented, whereas the correct id = 12345 would be allowed (assuming
id is the PK or index). I generally always use that option on a
production machine.

It does a few other things as well (LIMIT 1000, max_join_size), so
make sure you check it out before using it. It used to be called
am-a-dummy mode (that option works too), so you might see it referred
to as that in some places.

Regards,

Harrison