MySQL Bugs: #3313: Dangerous update/delete protection

Bug #3313	Dangerous update/delete protection
Submitted:	27 Mar 2004 16:38	Modified:	29 Mar 2004 13:00
Reporter:	James Sleeman	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:
Assigned to:	Peter Gulutzan	CPU Architecture:	Any

Description:
One of the, if not the most dangerous and, IMHO, poorly designed aspects of SQL is the fact that an update or delete without a where clause will work on all rows of a table.

Particularly, updating all rows of a table is a very rare operation, less so deleting but that is even more dangerous. I have to admit to making this mistake yesterday, completely accidentally, on a table with 30000 rows of important unique human entered data, it took a few seconds to destroy the data, and 12 hours to restore from backups.

But there is a very simple solution that would save the people who have that itchy enter key syndrome...

How to repeat:
mysql> use very_big_database;
mysql> update important_table_with_30000_rows set very_important_field = NULL;

spend 12 hours restoring from backups + binary logs

Suggested fix:
Have an option for mysql..
refuse_unqualified_update_or_delete

This simply checks any incoming update or delete statement, if there is no where clause, then return an error.

If the client actually does want to do such a thing they can simply use a null where clause 'where 1 = 1' which mysql could silently ignore and run the unqualified query.

As an option it would not affect people who have systems that really do want to do that often, but when it's switched on then it provides a huge safety feature that could save many people a lot of time.

I can't imagine it would be a significant performance penalty, but even in that case it could be made a compile time option and thus have zero penalty if you didn't want it.

Already exists an option for the mysql client, for to be safe with delete
and update.

  -U, --safe-updates  Only allow UPDATE and DELETE that uses keys.

However you are asking at the server side and I am assigning PeterG for
a more complete answer for you about.

Yes, although I wasn't aware of that client option, a similar server option would be invaluable as it would allow protection for clients and other uses of the server that do not support such a restriction natively.  

Adding the option to my client section of my.cnf now though ;-)

Anyone who wishes may say "SET SQL_SAFE_UPDATES = TRUE" when starting.