MySQL Rollback Query

Sometimes you may need to undo the changes made to MySQL database. You can easily do it using MySQL Rollback query. Here’s how to rollback MySQL database using MySQL Rollback command.

 

How does MySQL rollback work

MySQL Rollback statement allows you to rollback or undo one more statements that have been recently executed. For example, if you have accidentally deleted or updated rows, you can use MySQL rollback those statements and restore original databases. However, MySQL rollback works only if the database changes have NOT been committed to the database.

Bonus Read : How to Use MySQL Rollup

 

Can we rollback after commit in MySQL

No. Unfortunately you cannot undo changes that have been committed to the database.

 

How to Rollback MySQL database

Please note, by default, MySQL is configured to auto commit every SQL query. So it is important to turn off auto commit in MySQL using the following statement, if you want to be able to use MySQL Rollback.

Log into MySQL database and run the following command to disable autocommit

SET autocommit = 0

Or

SET autocommit = OFF

You use the following statement to enable the autocommit mode explicitly:

SET autocommit = 1

Or

SET autocommit = ON

 

Bonus Read : How to Check MySQL Version

 

Let’s say you have the following table sales(product,order_date,sale). We will look at how to issue MySQL rollback after delete command.

mysql> select * from sales;
+---------+------------+------+
| product | order_date | sale |
+---------+------------+------+
| A       | 2020-01-01 |   20 |
| B       | 2020-01-02 |   25 |
| B       | 2020-01-03 |   15 |
| A       | 2020-01-04 |   30 |
| A       | 2020-01-05 |   20 |
+---------+------------+------+

 

Now let us turn off autocommit as mentioned above,

mysql> set autocommit=0;

 

Next, let us delete all rows in sales table.

mysql> delete from sales;
Query OK, 5 rows affected (0.00 sec)

mysql> select count(*) from sales;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

As you can see all the 5 rows in sales table have been deleted.

 

Now, let us issue MySQL Rollback after delete command

mysql> rollback;
Query OK, 0 rows affected (0.07 sec)

mysql> select count(*) from sales;
+----------+
| count(*) |
+----------+
|        5 |
+----------+

As you can see, the 5 deleted rows have been restored since the changes were not committed. However, if you had issues COMMIT statement after deletion and before ROLLBACK command, then MySQL would not have restored your table.

 

Bonus Read : How to Disable Foreign Key Check in MySQL

 

MySQL Rollback Update command

Similarly, you can rollback update command. Let us update the sale column in above sales table.

mysql> set autocommit=0;

mysql> select * from sales;
+---------+------------+------+
| product | order_date | sale |
+---------+------------+------+
| A       | 2020-01-01 |   20 |
| B       | 2020-01-02 |   25 |
| B       | 2020-01-03 |   15 |
| A       | 2020-01-04 |   30 |
| A       | 2020-01-05 |   20 |
+---------+------------+------+
5 rows in set (0.00 sec)

mysql> update sales set sale=sale*2;
Query OK, 5 rows affected (0.05 sec)


mysql> select * from sales;
+---------+------------+------+
| product | order_date | sale |
+---------+------------+------+
| A       | 2020-01-01 |   40 |
| B       | 2020-01-02 |   50 |
| B       | 2020-01-03 |   30 |
| A       | 2020-01-04 |   60 |
| A       | 2020-01-05 |   40 |
+---------+------------+------+

As you can see we have doubled the sale column’s value. Now let us rollback update command.

mysql> rollback;

mysql> select * from sales;
+---------+------------+------+
| product | order_date | sale |
+---------+------------+------+
| A       | 2020-01-01 |   20 |
| B       | 2020-01-02 |   25 |
| B       | 2020-01-03 |   15 |
| A       | 2020-01-04 |   30 |
| A       | 2020-01-05 |   20 |
+---------+------------+------+

As you can see the sale column old values have been restored.

You can also include MySQL Rollback in stored procedures and MySQL transactions for MySQL Rollback on error. The key is to disable autocommit to execute MySQL rollback.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!