By default, MySQL has a safe mode that disables an
UPDATE or a
DELETE statement without a
The following statement tries to delete all data from a table called
DELETE FROM Cities;
With the safe mode on, MySQL will throw the following error response:
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
If you want to allow executing the statement in your MySQL database server, then you can turn off the safe mode using
SET sql_safe_updates statement.
The following statement turns off the safe mode:
Once you execute the statement you need, you can turn the safe mode back on by setting the value to
1 as shown below:
By default, the
SET statement should change the variable
sql_safe_updates value only for the current session.
If you wish to change the variable value permanently, you need to add the
GLOBAL keyword next to the
SET keyword as follows:
SET GLOBAL sql_safe_updates=0;
When using the
GLOBAL keyword, you need to disconnect from the current session to let the change take effect.
And that’s how you can turn off MySQL safe mode in your server.