Posts Tagged CLI

mysql –i-am-a-dummy

Posted by on Thursday, 9 April, 2009

I thought it was a joke when I first heard about it, but the MySQL command line client has this option where you can actually tell it you’re stupid:

 mysql --i-am-a-dummy -uroot test

Otherwise known as --safe-updates, this option prevents MySQL from performing update operations unless a key constraint in the WHERE clause and / or a LIMIT clause are provided, e.g.:

mysql> DELETE FROM bigtable;
ERROR 1175: You are using safe update mode and you
tried to update a table without a WHERE that uses a 
KEY column

This would wipe out bigtable – unless bigtable is an InnoDB table and the command is wrapped in a transaction. (If you don’t use transactions, you should have a lot more to worry about anyway).

See: http://dev.mysql.com/doc/mysql/en/safe-updates.html.

Note that the --safe-updates / --i-am-a-dummy option causes the following statement to be issued on connection:

SET SQL_SAFE_UPDATES=1,SQL_SELECT_LIMIT=1000,
    SQL_MAX_JOIN_SIZE=1000000;
From the man page
–safe-updates, –i-am-a-dummy, -U
Allow only those UPDATE and DELETE statements that specify which rows to modify by using key values. If you haveĀ  set this option in an option file, you can override it by using –safe-updates on the command line. See the section called MYSQL TIPS, for more information about this option.