CODEWITHSUNDEEP
phpmysql
DEVOPS
DEVOPS

Reputation: 18790

How to undo a query execution in phpmyadmin

How to undo a query execution in phpmyadmin.

Any rollback functionality is there? any body knows the solution kindly help me?

Upvotes: 17

Views: 89810

Answers (5)

Nanne
Nanne

Reputation: 64399

You can go to the processes page and press 'kill'

Upvotes: 2

Mark Baker
Mark Baker

Reputation: 212412

If you need the ability to restore previous data back prior to a committed update, then you could refactor your database using update and delete triggers to store the old data in an archive table with a current date/timestamp or (preferably) a transaction id value (and ensure that inserts always store a current date/timestamp or transaction id value)... effectively maintaining an entire history of every change made to your database. Beware, your database will grow at an incredible rate... this is only really a solution for mission-critical data where the history is essential, because it takes a lot of effort to implement, and a lot of expensive disk space to maintain. Even then, it can get very complex if there have been subsequent updates to the affected data; and you may need to "lose" those when reverting to a previous history.

I can only think of a very few systems that implement this type of history maintenance... eg Oracle Financials or HR.

ALternatively, there are some databases (I used to work with the old DEC RDBMS) that can maintain RUJs (Run Unit Journals) which can then be used to restore from a backup and up to a set time/transaction. However MySQL doesn't fall into this category. Again, it requires a lot of disk space, and is only practical when you do regular backups of your data, and the recovery process is more complex. MySQL (as far as I'm aware) doesn't support this feature.

For most people, the more practical approach is a simple restore from backup, possibly followed by some manual recreation. Unfortunately, most people these days don't even take backups.

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838066

If the statement is still running you can use KILL QUERY <thread_id>.

If the statement has completed but you have not yet committed the transaction you can use ROLLBACK.

If the statement has completed and the transaction is already committed (or you didn't start a transaction) then restore the data from your most recent backup.

Also here are some tips advice in order to prevent this type of situation happening in the first place:

  • When writing a DELETE or UPDATE always write the WHERE clause first so that you don't forget it.
  • Test your WHERE clause in a SELECT statement to make sure you are updating the correct rows.
  • If you know you should only be updating one row then you can add LIMIT 1 to your UPDATE statement. Then if despite using the above techniques you still have an error at least only one row will be affected, not the entire database.

Upvotes: 33

ayush
ayush

Reputation: 14568

if you deleted something then i dont think its going to come back unless you had a backup.I know in sql you can sometimes get away will the ROLLBACK call before you commit to a series of SQL commands but Only in transactions, and you have to have them started first (which phpMyAdmin doesn't use).

Upvotes: 1

Rich
Rich

Reputation: 216

If you've already committed the transaction, there is no way of “undoing” it, I'm afraid. That's one of the core principles of ACID. If you haven't committed it: just do a rollback, and you're fine.

You'll need to restore you data from a backup – or if the query is running, try what Mark Byers suggested, using kill query.

Upvotes: 0

Related Questions