Is there any way to rollback after commit in MySQL?

mysql sql-update rollback autocommit
Johnny Lim picture Johnny Lim · Dec 24, 2012 · Viewed 43.2k times · Source

I did a big mistake that I updated a table without 'where' clause in MySQL :'(

It is auto-committed.

Is there any way to rollback from it?

Answer

Bohemian picture Bohemian · Dec 24, 2012

No, there's no query that will "undo" a committed data-modifying query.

If you have a backup of the database, you can restore the backup and use DBA tools (in MySQL's case, it's mysqlbinlog) to "replay" all data-modifying queries from the logs since the backup back to the database, but skip over the problem query.

If you don't have a backup and all logs since the that backup, there's nothing you can do to recover the data.

Related questions

MySQL error code: 1175 during UPDATE in MySQL Workbench

I'm trying to update the column visited to give it the value 1. I use MySQL workbench, and I'm writing the statement in the SQL editor from inside the workbench. I'm writing the following command: UPDATE tablename SET columnname=1; It gives …

Read More
MySQL - UPDATE query based on SELECT Query

I need to check (from the same table) if there is an association between two events based on date-time. One set of data will contain the ending date-time of certain events and the other set of data will contain the …

Read More
How to update column with null value

I am using mysql and need to update a column with a null value. I have tried this many different ways and the best I have gotten is an empty string. Is there a special syntax to do this?

Read More