How to rollback the effect of last executed mysql query

Question

I just ran a command

update sometable set col = '1';

by mistake without specifying the where condition. Is it possible to recover the previous version of the table?

Answer 1

Unless you...

1. Started a transaction before running the query, and...
2. Didn't already commit the transaction

...then no, you're out of luck, barring any backups of previous versions of the database you might have made yourself.

(If you don't use transactions when manually entering queries, you might want to in the future to prevent headaches like the one you probably have now. They're invaluable for mitigating the realized-5-seconds-later kind of mistake.)

Answer 2

No. MySQL does have transaction support for some table types, but because you're asking this question, I'll bet you're not using it.

Everybody does this once. It's when you do it twice you have to worry :)

Answer 3

Consider enabling sql_safe_updates in future if you are worried about doing this kind of thing again.

SET SESSION sql_safe_updates = 1