“When working with MySQL databases, you may encounter the “Error Code 1175” triggered when performing an UPDATE or DELETE instructions.”
This post will discuss the cause of this error and how we can resolve it using the MySQL server.
What Causes the “MySQL Error Code 1175”?
The “MySQL Error Code 1175” occurs when performing an UPDATE or DELETE operation without using the WHERE clause.
By default, MySQL uses a feature called safe_mode that prevents you from executing an UPDATE or DELETE statement without a WHERE clause. This prevents any accidental data loss on the target.
Therefore, when the safe_mode feature is activated, MySQL will return the error code 1175 on any DELETE or UPDATE operation that does not include a WHERE clause.
An example is shown below:
In this case, we are attempting to change the value of the title column without specifying which row we wish to target. This can result in us overwriting the entire table with the specified value. Hence, MySQL will prevent this and return an error as shown:
MySQL Check if Safe_Mode is Enabled
The state of the safe_mode feature is stored in the sql_safe_updates variable. Hence, we can fetch the value of this variable to determine if the safe_mode feature is enabled or not.
The query is as shown:
The query should return the state as shown:
| Variable_name | Value |
+------------------+-------+
| sql_safe_updates | ON |
+------------------+-------+
1 row in set (0.00 sec)
In this case, we can see that the safe_mode feature is enabled on the session.
How to Resolve “MySQL Error Code 1175”
The best way to resolve this type of error is using a WHERE clause. However, in some cases, we may need to perform an UPDATE or DELETE without any condition.
To do this, we can disable the safe_mode feature in the session, allowing us to execute the query. Then, we can use the SET command followed by the variable name and the value we wish to set.
For example, to disable safe_mode, we set the value of the sql_safe_updates variable to 0. The query is as shown:
To enable it, set the value to 1 as:
In MySQL Workbench, you can disable the safe_mode feature by navigating to Edit-> Preferences -> SQL Editor
Disabled the “Safe Updates” feature and restarted your session to the server.
Termination
You learned the cause of the “MySQL Error Code 1175” in this post when performing UPDATE or DELETE statements. You also learned how to resolve the error by disabling MySQL safe_mode feature.
