Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question
add comment

3 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment
  • 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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.