MySQL - how to turn off safe mode


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.

Get 50 MySQL Snippets Book for FREE 🔥

50 MySQL snippets that you can use in various scenarios

Save 1000+ hours of research and 10x your productivity