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.
UPDATE ubupa.wp_options set option_value = replace( option_value, 'https://xxxxxx.com', 'http://yyyyyy.lan') WHERE option_value like '%xxxxxx.com%' ;
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.
Now we will see how we can solve it in all possible ways. It is not recommending to disabled 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.
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 ). Unchecked it and click ok.
Now execute a query, it should work.
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;
In this method, we do not need to disable or enable safe update mode. So we will use where clause in such a way, that will return true for all records. The only condition is we need a primary key column in 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;
If 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