MySQL - how to turn off safe mode

Posted on Dec 17, 2021

Learn how to turn off safe mode in MySQL

By default, MySQL has a safe mode that disables an UPDATE or a DELETE statement without a WHERE clause.

The following statement tries to delete all data from a table called Cities:

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:

SET sql_safe_updates=0;

Once you execute the statement you need, you can turn the safe mode back on by setting the value to 1 as shown below:

SET sql_safe_updates=1;

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.

Level up your programming skills

I'm sending out an occasional email with the latest programming tutorials. Drop your email in the box below and I'll send new stuff straight into your inbox!

No spam. Unsubscribe anytime.