Category Archives: mysql

MYSQL drop all tables in database in linux

Taken from http://knaddison.com/technology/mysql-drop-all-tables-database-using-single-command-line-command#comment-3588.

#!/bin/sh

mysql --user=$1 --password=$2 -BNe "show tables" $3 | tr 'n' ',' | sed -e 's/,$//' | awk '{print "SET FOREIGN_KEY_CHECKS = 0;DROP TABLE IF EXISTS " $1 ";SET FOREIGN_KEY_CHECKS = 1;"}' | mysql --user=$1 --password=$2 $3

Save code above in a file, say, mysql-drop-tables. Then change the permission to excecuteable

chmod +x mysql-delete-tables

Then run it

mysql-drop-tables dbuser dbpassword dbname

Here’s fhe file if you’re too lazy to copy paste
http://dl.dropbox.com/u/112837/scripts/mysql-drop-tables

Advertisements

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

Error Code: 1175
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

Every found that kind of error when trying to update rows in mysql? It’s because you tried to update a table without a WHERE that uses a KEY column (err …).

Anyway, The quick fix is to add SET SQL_SAFE_UPDATES=0; before your update query. Here’s the example:

SET SQL_SAFE_UPDATES=0;
DELETE FROM people WHERE person_status = 'deceased';
Tagged , , ,