Mysql Disable safe mode Command For Update & Delete Query
MySQL Safe Mode is a safeguard for databases to prevent unintentional updates to table records while running update or delete queries without specifying where conditions.
We are going to learn about system variables sql_safe_updates to enable/disable safe mode.
There might be some scenarios where we need to update table records without conditions such as data fix.
It is strongly recommended to make Safe mode Turn ON.
In this current article about MySQL disable safe mode We are going to learn commonly asked questions regarding MySQL safe mode with answers. Let's dive into the following steps:
Scroll for More Useful Information and Relevant FAQs
How to check if MySQL safe mode is ON/OFF?
mysql> SHOW VARIABLES LIKE "sql_safe_updates";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_safe_updates | ON |
+------------------+-------+
MySQL server maintains various system variables used to be configured for different MySQL database operations. We can find a list of system variable names with their default set values by running
mysql> SHOW VARIABLES ;
As we are interested only in the sql_safe_updates variable, we used like clause to filter specific to safe mode param only.
How to Turn OFF safe mode update in Mysql?
Just run the following command to turn OFF safe mode
SET SQL_SAFE_UPDATES = 0 ;
Following code demonstrate execution:
mysql> SET SQL_SAFE_UPDATES = 0 ;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW VARIABLES LIKE "sql_safe_updates";
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| sql_safe_updates | OFF |
+------------------+-------+
1 row in set (0.00 sec)
Let's see the below command together with the update query:
-- Disable safe update mode
SET SQL_SAFE_UPDATES=0;
-- Run update Query (Below is a sample update Query)
UPDATE tbl_employee set email = "test@gmail.com";
-- Enable safe mode to avoid any accidental update without where the condition
SET SQL_SAFE_UPDATES = 1 ;
How to MySQL Turn OFF safe mode delete query?
To disable safe mode for delete query we need to exercise the same way as done for update query above.
-- Disable safe update mode
SET SQL_SAFE_UPDATES=0;
-- Run Delete Query
delete from tbl_employee;
-- Enable safe mode to avoid any accidental delete without where the condition
SET SQL_SAFE_UPDATES = 1 ;
Why & When Mysql database raise ERROR 1175?
Error code 1175 explains that the Update/Delete query trying to perform an operation (without where clause) that is not permissive due to the safe mode being ON.
Let's understand with the below example for update query :
SET SQL_SAFE_UPDATES = 1 ;
UPDATE tbl_employee set email = "test@gmail.com";
Result:
Error Code: 1175
An error message like: Being in safe update mode you are trying to update a table without a WHERE clause that uses a KEY column
As we see once the safe mode is ON by setting system variable SQL_SAFE_UPDATES to 1 it refuses to run update query without where condition.
Same for delete query :
SET SQL_SAFE_UPDATES = 1 ;
Delete from tbl_employee ;
Result:
- Error Msg: 1175 - You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
- It refuses to run Delete query without where condition once setting safe mode system variable ON.
