How to Turn Off Safe Update Mode in MySQL
November 9, 2023
When working with MySQL, you might encounter a situation where you're restricted by the safe update mode. This guide explains how to disable this mode, which is often encountered in MySQL Workbench or other client interfaces, particularly when trying to execute update or delete operations without a WHERE clause or with a non-key column in WHERE.
Understanding Safe Update Mode
Safe update mode in MySQL is designed to prevent accidental updates or deletions of data. It requires that any UPDATE or DELETE operations include a WHERE clause that uses a key column or limits the number of rows affected.
How to Disable Safe Update Mode via SQL Command
To temporarily disable safe update mode for your current session, you can use the following SQL command:
This command sets the
SQL_SAFE_UPDATES variable to 0, effectively turning off the safe update mode. Remember, this change is temporary and only applies to the current session.
How to Disable Safe Update Mode in MySQL Workbench
If you're using MySQL Workbench and want to disable the safe update mode:
- Open MySQL Workbench and connect to your database.
- Navigate to the 'Edit' menu and select 'Preferences'.
- In the Preferences window, click on 'SQL Editor'.
- Under the SQL Editor section, uncheck the option that says 'Safe Updates (rejects UPDATEs and DELETEs with no WHERE clause)'.
- Click 'OK' to save your changes.
After completing these steps, MySQL Workbench will no longer enforce safe update mode for any new sessions.
Modifying Configuration File for Persistent Change
For a more permanent solution, you can modify the MySQL configuration file:
Locate your MySQL configuration file, typically named
Open the file in a text editor.
[mysqld]section, add the following line:
Save the file and restart the MySQL server for the changes to take effect.
This change will disable safe update mode for all connections to the MySQL server.
Using Command-Line Options
If you prefer using the command line, you can start the MySQL server with the
This approach is useful for temporary changes or for testing purposes.
Impact of Disabling Safe Update Mode
Disabling safe update mode removes a layer of protection against accidental data modification. It's important to be cautious with your UPDATE and DELETE statements, especially when working without a WHERE clause.
Not Equal in MySQL
How to Drop a User in MySQL
Duplicate Column Name in MySQL
Backticks in MySQL: An Overview
How to Set a Timer in MySQL
How to Fix the Illegal Mix of Collations Error in MySQL