In this blog post, we’ll look at how “operator error” can cause serious problems (like the one we saw last week with AWS), and how to avoid them in MySQL using
--i-am-a-dummy
.
Recently, AWS had some serious downtime in their East region, which they explained as the consequence of a bad deployment. It seems like most of the Internet was affected in one way or another. Some on Twitter dubbed it “S3 Dependency Awareness Day.”
Since the outage, many companies (especially Amazon!) are reviewing their production access and deployment procedures. It would be a lie if I claimed I’ve never made a mistake in production. In fact, I would be afraid of working with someone who claims to have never made a mistake in a production environment.
Making a mistake or two is how you learn to have a full sense of fear when you start typing:
UPDATE t1 SET c1='x' ...
I think many of us have experienced forehead sweats and hand shaking in these cases – they save us from major mistakes!
The good news is that MySQL can help you with this. All you have to do is admit that you are human, and use the following command (you can also set this in your user directory .my.cnf):
mysql --i-am-a-dummy
Using this command (also known as safe-updates) sets the following SQL mode when logging into the server:
SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;
The safe-updates and iam-a-dummy flags were introduced together in MySQL 3.23.11, and according to some sites from around the time of release, it’s “for users that once may have done a DELETE FROM table_name but forgot the WHERE clause.”
What this does is ensure you can’t perform an UPDATE or DELETE without a WHERE clause. This is great because it forces you to think through what you are doing. If you still want to update the whole table, you need to do something like WHERE ID > 0. Interestingly, safe-updates also blocks the use of WHERE 1, which means “where true” (or basically everything).
The other safety you get with this option is that SELECT is automatically limited to 1000 rows, and JOIN is limited to examining 1 million rows. You can override these latter limits with extra flags, such as:
--select_limit=500 --max_join_size=10000
I have added this to the .my.cnf on my own servers, and definitely use this with my clients.