Transaction Rollback on DROP DATABASE

Question

Does MySQL use transactions for DROP DATABASE... statement (all tables use InnoDB engine)?

Basically I would like to know if any data will be lost if I kill DROP DATABASE... query while it's running?

I've tested this with tables and it works but I don't have big enough database to test it:

mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (8.57 sec)

mysql> DELETE FROM t1;

Another MySQL session:

mysql> SHOW PROCESSLIST;
+-----+------+-----------+------+---------+------+----------+-------------------+-----------+---------------+
| Id  | User | Host      | db   | Command | Time | State    | Info              | Rows_sent | Rows_examined |
+-----+------+-----------+------+---------+------+----------+-------------------+-----------+---------------+
| 211 | root | localhost | test | Query   |    0 | init     | SHOW PROCESSLIST  |         0 |             0 |
| 242 | root | localhost | test | Query   |    4 | updating | DELETE FROM t1    |         0 |        879266 |
+-----+------+-----------+------+---------+------+----------+-------------------+-----------+---------------+
2 rows in set (0.00 sec)

mysql> KILL 242;
Query OK, 0 rows affected (0.00 sec)

back to first session

mysql> SELECT COUNT(*) FROM t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    283
Current database: test

+----------+
| COUNT(*) |
+----------+
| 10000000 |
+----------+
1 row in set (6.91 sec)

Answer 1

In the above example you gave, DELETE is part of DML (Data Manipulation Language). For the default transaction isolation level of REPEATABLE READ, all the rows in the t1 table are made available in the rollback segments (See the Rollback Segments and Undo Space) so that the count query can have point-in-time snapshot of the rows to count.

DROP DATABASE is DDL (Data Definition Language) not DML. There are no MySQL mechanisms in place for rolling back DROP DATABASE;. In fact, the mechanisms are centered around the OS.

During a DROP DATABASE operation, you will see Checking permissions... in the processlist to verify file permissions on each table before deleting files (.frm header files + other storage-engine related files (such as .ibd,.MYD,.MYI,etc)). The database will experience a lot of MUTEX calls on every table in the database. This involves closing open file handles to each table within the database bring dropped prior to deleting the table. During that period, none of the tables are available.

ANSWER TO YOUR ACTUAL QUESTION

Once you issue DROP DATABASE;, please don't kill it. Even if you did, it is an irreversible atomic operation. For InnoDB, this includes disavowing any knowledge of that database within the system tablespace (ibdata1) and the tables inside that database.

SUGGESTION

You should configure backups (XtraBackup, mysqldump, binary logs, LVM snapshots) as a preventative measure against DROP DATABASE;. You should also schedule regular restores of the backups on Staging Servers to make sure your data is consistent.

Answer 2

There are several SQL statements you cannot use ROLLBACK as MySQL Implicitly Commits those Statements such as:

CREATE / ALTER / DROP DATABASE
CREATE /ALTER / DROP / RENAME / TRUNCATE TABLE
CREATE / DROP INDEX
CREATE / DROP EVENT
CREATE / DROP FUNCTION
CREATE / DROP PROCEDURE

For Such Statements it doesn't matter weather you are using explicit Commit or not the statement will Commit implicitly ass soon as you execute it.

Answer 3

• The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed. No DML triggers will be fired. The operation cannot be rolled back.

• You can use binary log point-in-time recovery, this can restore data before deleterious SQL had executed.