After reading about this delete-happy install script, I was reminded of a lifesaving MySQL command-line option:
--i-am-a-dummy
What does this do? From the MySQL manual:
Permit only those UPDATE and DELETE statements that specify which rows to modify by using key values.
In practise, this means that statements such as these will fail:
UPDATE my_table SET some_column = 'A Lovely Value';
DELETE FROM my_table;
As soon as you tell MySQL what to update, the statements will run correctly, for example:
UPDATE my_table SET some_column = 'A Lovely Value' WHERE category_id = 15;
DELETE FROM my_table WHERE id_column = 150;
Have fun and stay safe!
Written by Tom
Are you looking for web development or just someone who will work with your business needs and not against them? Get in touch with me here, or take a look at my consultancy’s website: Moo Unlimited. I’m confident I can help you.
Nice tip!
Would be nice if this could be enabled by default, any way of doing that?
Figured it out: Add ‘i-am-a-dummy’ or ‘safe-updates’ on a seperate line to your my.cnf under de section [client]
My trick is to write UDATE and DLETE (missing the second letter) until I’m done writing out my command. That way, if I accidentally hit enter before the WHERE clause, it’s not a valid command yet.
I recommend starting with SELECT first, running the query to verify the return set, then changing it to DELETE or UPDATE. The has saved me countless headaches on some enterprise databases over the years.
This looks like it’ll come in handy. Another thing I make sure I do before doing any destructive operations in MySQL is to create a copy of the table I’m currently working on. This is good when you’re running quick experiments, fixing bugs in SQL queries or trying new things out on a set of data.