Table of Contents
Today I was restoring the live WordPress site on the local machine for some development work. During the process, I execute some queries and got the following error, I am using MySQLWorkbench 8.0 and MySQL 8.0.23.
Query
UPDATE ubupa.wp_options
set option_value = replace( option_value, 'https://xxxxxx.com', 'http://yyyyyy.lan')
WHERE
option_value like '%xxxxxx.com%' ;Error
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. What is Safe Update Mode?
If you are a beginner then the Safe update mode is very useful for you. It is a client-side setting and default enabled mode in MySQL Workbench. As you understand by name, Safe Update Mode will prevent your UPDATE or DELETE query when you forgot the WHERE clause for a key column or LIMIT clause.
Solution
Now we will see how we can solve it in all possible ways. It is not recommended to disable Safe update mode permanently. It is a security option and it must be turned ON but sometimes we need to delete all records and it will not allow us to do so. We will check how to disable it manually and in the query.
MySQL Workbench
We can turn it on and off safe mode in MySql Workbench as well. Let’s check, Go MySQLWorkbench >> Preferences >> SQL Editor, you will find —Safe Updates ( rejects UPATEs and DELETEs with no restrictions ). I unchecked it and click ok.
Now execute a query, it should work.
Query
In this way, we will disable Safe Update Mode before our Query execution start but it is a security feature so we will enable it again after execution is completed.
Set sql_safe_updates = 0;
-- Our Update OR Delete Query --
Set sql_safe_updates = 1;
Where clause
In this method, we do not need to disable or enable safe update mode. So we will use the where clause in such a way, that will return true for all records. The only condition is we need a primary key column in the where clause.
UPDATE ubupa.wp_options
set option_value = replace( option_value, 'https://xxxxxx.com', 'http://xxxxxx.lan')
WHERE
option_value like '%xxxxxx.com%' AND option_id <> 0;
As you see we used the primary key column, option_id. Our where clause will return true as we do not have any records for id 0.
Download MySQL Workbench