DROP DATABASE [IF EXISTS] db_name
DROP DATABASE drops all tables in the
database and deletes the database. Be very
careful with this statement! To use DROP
DATABASE, you need the DROP
privilege on the database.
Important: When a database is
dropped, user privileges on the database are
not automatically dropped. See
Section 13.5.1.2, “GRANT Syntax”.
In MySQL 3.22 or later, you can use the keywords IF
EXISTS to prevent an error from occurring if the
database does not exist.
If you use DROP DATABASE on a symbolically
linked database, both the link and the original database are
deleted.
As of MySQL 4.1.2, DROP DATABASE returns the
number of tables that were removed. This corresponds to the
number of .frm files removed.
The DROP DATABASE statement removes from the
given database directory those files and directories that MySQL
itself may create during normal operation:
-
All files with these extensions:
.BAK.DAT.HSH.ISD.ISM.MRG.MYD.MYI.db.frm.ibd.ndb All subdirectories with names that consist of two hex digits
00-ff. These are subdirectories used forRAIDtables. (These directories are not removed in versions of MySQL after 4.1, where support forRAIDtables is removed. You should convert any existingRAIDtables and remove these directories manually before upgrading to later MySQL versions.)The
db.optfile, if it exists.
If other files or directories remain in the database directory
after MySQL removes those just listed, the database directory
cannot be removed. In this case, you must remove any remaining
files or directories manually and issue the DROP
DATABASE statement again.
You can also drop databases with mysqladmin. See Section 8.9, “mysqladmin — Client for Administering a MySQL Server”.
User Comments
Please note that when dropping databases containing InnoDB tables in version 4.0.18 and beyond, you might get a FK violation.
It sounds bizarre, but DROP DATABASE seemingly drops each table, first, in arbitrary order. Since the ordering is arbitrary, it may drop them in the 'wrong' order. In this case, you'll get the message:
error: 'Cannot delete or update a parent row: a foreign key constraint fails'
... and half of your tables will be gone, the other half remaining. You can repeatedly execute "DROP DATABASE" commands and each time (at least for me), MySQL gets closer to an empty database, at which point, the database itself is dropped.
Here's a mailing list thread covering the phenomenon and a few responses from someone who sounds like they're in a position to know about these things.
http://lists.mysql.com/bugs/15255
I marked this comment as an "Explanation" because I cannot mark it as a bug :(. I feel that there needs to be a way to drop a database without manually dropping all the tables inside it, first. MySQL should at least drop the tables in the reverse order in which they were added to the database, to avoid such behavior.
-chris
Adding
SET FOREIGN_KEY_CHECKS = 0;
before dropping a database fixes the foreign key constraint problem (as introduced in 4.0.18) for me
Do note that when a db is dropped, the corresponding db structure is deleted. However, the data itself, which resides in a common file 'ibdata', is not deleted. In some cases, this might constitute a security hazard.
This is not a bug but i think that it would be a good idea that 'drop database' actually removes the data from ibdata.
Add your own comment.