Last Updated: February 25, 2016
·
809
· trinitronx

Protect yourself from trashing the production database

Here's a useful flag I found in the mysql manpage: mysql --i-am-a-dummy

· --safe-updates, --i-am-a-dummy, -U

Permit 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.

This should prevent you from making a mistake like this:
mysql> UPDATE tusers SET Fname = 'Oops', Lname = 'this-would-update-all-rows-in-tusers';
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

\(◠‿◠)/

3 Responses
Add your response

Great tip, never heard of it before. =)

over 1 year ago ·

Great find, thanks!

over 1 year ago ·

You can even turn this on for all connections by setting the sql_safe_updates variable to 1. ( source )

over 1 year ago ·