CODEWITHSUNDEEP
mysqlrollback
Hari kanna
Hari kanna

Reputation: 2521

How can I undo a mysql statement that I just executed?

How can I undo the most recently executed mysql query?

Upvotes: 72

Views: 213252

Answers (6)

Vimard
Vimard

Reputation: 1259

If you define table type as InnoDB, you can use transactions. You will need set AUTOCOMMIT=0, and after you can issue COMMIT or ROLLBACK at the end of query or session to submit or cancel a transaction. 

ROLLBACK -- will undo the changes that you have made

Upvotes: 56

meistermuh
meistermuh

Reputation: 522

in case you do not only need to undo your last query (although your question actually only points on that, I know) and therefore if a transaction might not help you out, you need to implement a workaround for this:

copy the original data before commiting your query and write it back on demand based on the unique id that must be the same in both tables; your rollback-table (with the copies of the unchanged data) and your actual table (containing the data that should be "undone" than). for databases having many tables, one single "rollback-table" containing structured dumps/copies of the original data would be better to use then one for each actual table. it would contain the name of the actual table, the unique id of the row, and in a third field the content in any desired format that represents the data structure and values clearly (e.g. XML). based on the first two fields this third one would be parsed and written back to the actual table. a fourth field with a timestamp would help cleaning up this rollback-table.

since there is no real undo in SQL-dialects despite "rollback" in a transaction (please correct me if I'm wrong - maybe there now is one), this is the only way, I guess, and you have to write the code for it on your own.

Upvotes: 1

Palantir
Palantir

Reputation: 24182

You can only do so during a transaction.

BEGIN;
INSERT INTO xxx ...;
DELETE FROM ...;

Then you can either:

COMMIT; -- will confirm your changes

Or

ROLLBACK -- will undo your previous changes

Upvotes: 43

adelarsq
adelarsq

Reputation: 3738

For some instrutions, like ALTER TABLE, this is not possible with MySQL, even with transactions (1 and 2).

Upvotes: 8

RINSON KE
RINSON KE

Reputation: 292

You can stop a query which is being processed by this

Find the Id of the query process by => show processlist;

Then => kill id;

Upvotes: 5

halfdan
halfdan

Reputation: 34204

Basically: If you're doing a transaction just do a rollback. Otherwise, you can't "undo" a MySQL query.

Upvotes: 26

Related Questions