Error Code: 1175, MySQL Safe Update Mode

MySQL

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 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.

MySQLWorkbench

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. 

Safe Update Mode
Workbench Preference

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 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

Share and Enjoy !

Shares

Leave a Reply