This is another translation of a post written originally on my blog, i hope it will and fun to read!

I had to drop all the tables in a MySQL database because the structure and the content were updated. I couldn't use ALTER and UPDATE statements because there were millions of records and a lot of tables i was unaware of.

An easy solution could be importing a dump which previously executes a full database DROP and create it from scratch. The problem? My user didn't have permissions to execute DROP DATABASE queries.

The only possible solution is to eliminate every table one by one.

Easy, isn't it? NO! 

Do you remember about referential integrity constraints? (Spoiler: i didn't, until i tried to execute the first delete query)

In my case the foreign keys were defined without the DELETE ON CASCADE policy, which it was a no sense choice. But, coming back to the original problem, when you try to delete a table that is referenced by one or more tables, the query fails due to integrity constraints.

To work around the problem it is possible to launch the DROP queries multiple times, until no more errors rise. But... there must be a more elegant solution!

I searched a bit on the documentation and i discovered it is possible to disable integrity constraints with the command SET foreign_key_checks = 0;.

Fortunately, a pious soul has also put together a beautiful ready-to-use snippet to be launched with Bash. You can find it on GitHub:

#!/bin/bash

#usage: mysql-drop-all-tables -d database -u dbuser -p dbpass

TEMP_FILE_PATH='./drop_all_tables.sql'

while getopts d:u:p: option
do
    case "${option}"
    in
    d) DBNAME=${OPTARG};;
    u) DBUSER=${OPTARG};;
    p) DBPASS=${OPTARG};;
    esac
done

echo "SET FOREIGN_KEY_CHECKS = 0;" > $TEMP_FILE_PATH
( mysqldump --add-drop-table --no-data -u$DBUSER -p$DBPASS $DBNAME | grep 'DROP TABLE' ) >> $TEMP_FILE_PATH
echo "SET FOREIGN_KEY_CHECKS = 1;" >> $TEMP_FILE_PATH
mysql -u$DBUSER -p$DBPASS $DBNAME < $TEMP_FILE_PATH
rm -f $TEMP_FILE_PATH

This script worked out of the box for me, however you can tweak it in order to:

• passing an host argument if the database is not local
• avoid passing the password to the command since it will be stored into the history (and you should really avoid it, every time you do this a DBA dies)

If this script was useful to you don't forget to star it and leave a comment to its creator!

PS: the cover pic shows me before dropping a production database