Safe Updates in MySQL to Prevent Accidental Queries

You're working in a database and accidentally update every row in a table instead of a single row. Yikes! We've all been there. And there's no level of caffeine that can get your heart pumping faster than destroying a production database.

Luckily, MySQL makes it easy to prevent these types of data changes with a built-in safe updates feature, a toggle you can turn on and off to prevent UPDATE and DELETE query executions that don't contain a WHERE clause.

You'll likely see an error message like this one if you attempt to update a table's data without a WHERE clause:

You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

Let's see what we can do to protect ourselves from accidentally overwriting our data.

Detecting if Safe Updates Are Enabled

There are two ways you can check to see if safe updates are enabled in MySQL. Here is the first command and its output:

SHOW VARIABLES LIKE "sql_safe_updates";
// sql_safe_updates: ON

And here is another command:

SELECT @@sql_safe_updates;
// 1

Both will return whether or not the feature is enabled or disabled for the active schema. If you wanted to show the global settings for all schemas within the environment, you could do so with this command:

SHOW GLOBAL VARIABLES LIKE "sql_safe_updates";

Toggling Safe Updates Using A Query

To enable safe updates in MySQL, all you have to do is run the following command:

SET sql_safe_updates = 1;

Where the value of 1 means that safe updates are turned on. This will block any UPDATE or DELETE queries from executing that don't contain a WHERE clause.

Alternatively, you can turn off safe updates:

SET sql_safe_updates = 0;

And then run any UPDATE or DELETE query you want. As long as the logged in user has permissions to do so, the query will execute without a WHERE clause.

Again, this will only set the safe updates flag for the active schema. If you wanted to update all schema preferences, you could do so with the following command:

SET GLOBAL sql_safe_updates = 1;

Take extra care when disabling safe updates. This is a great feature to prevent data loss, especially in production databases, and should be enabled unless you're experienced and know what you're updating. It's extremely easy to update or delete data by accident, so be careful when disabling this feature.

Toggling Safe Updates Using MySQL Workbench

You can automatically enable or disable safe updates for all schemas using MySQL Workbench.

To do so, follow these steps:

• Go to Edit: Preferences
• Click the SQL Editor tab
• Check the "Safe Updates" checkbox to enable the feature, or uncheck it to disable the feature

You will have to restart MySQL Workbench for the updated preference to take effect.

Conclusion

Safe updates in MySQL help prevent accidental data loss and should be kept enabled, especially in production environments. Only disable this feature if you're experienced with MySQL and SQL queries, and are taking great care when manipulating data.