Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rollback not working in MySQL

Tags:

mysql

I have a user table, and I have 5 records.

I deleted two records, then executed the rollback command, it executed successfully.

But that deleted two records not recovered.

The user table engine is InnoDB.

like image 901
Bharanikumar Avatar asked May 18 '10 03:05

Bharanikumar


People also ask

Why rollback is not working in MySQL?

ROLLBACK; and make sure that you are not using COMMIT after the Query which you need to rollback. Refer Table Engines and Transaction. And When a DB connection is created, it is in auto-commit mode by default.

Does rollback work in MySQL?

A COMMIT or ROLLBACK statement ends the current transaction and a new one starts. If a session that has autocommit disabled ends without explicitly committing the final transaction, MySQL rolls back that transaction.

How do I roll back data in MySQL?

To roll back the current transaction and cancel its changes, you use the ROLLBACK statement. To disable or enable the auto-commit mode for the current transaction, you use the SET autocommit statement.

What happens when rollback fails?

If a rollback fails, then you would have a serious problem. The reliability of the database cannot be guaranteed. In other words; you probably have some sort of corruption in your transaction log and will end up with an inconsistent database.


1 Answers

You should be able to rollback your transaction as the table engine is InnoDB. enter image description here

Anyways here is the correct way to do transactions,

SET autocommit=0;
START TRANSACTION; 
Your Query here.
ROLLBACK;

and make sure that you are not using COMMIT after the Query which you need to rollback. Refer Table Engines and Transaction. And When a DB connection is created, it is in auto-commit mode by default. This means that each individual SQL statement is treated as a transaction and is automatically committed right after it is executed. So if you need to do transactions yourself, you must turn off the autocommit mode by AUTOCOMMIT = 0. Refer this link for more info.

like image 67
Ayyappan Sekar Avatar answered Oct 15 '22 12:10

Ayyappan Sekar