MySQL Error Code: 1175. How to turn off safe update mode in workbench

In this article we will look at the meaning of following MySQL error, its reason and solution:

Scenario
Above error may arise
1. When deleting all records from a table with a DELETE FROM query that does not use a WHERE clause, or
2. When fetching a large data set using SELECT query that selects more than 1000 rows, or
3. Updating records with an UPDATE statement that does not use a WHERE clause.
Reason
MySQL server on which the query is being executed has --safe-updates option enabled.
This option is enabled by default to prevent accidental deletion or updation of records. A DELETE and UPDATE statement without a WHERE clause deletes or updates all the records in a table.
To prevent this --safe-updates option is enabled.
With this option enabled, MySQL will allow only those DELETE or UPDATE statements that provide a condition using WHERE clause and this condition should have the primary key of the table, so that all the records are not affected.
For --safe-updates mode, MySQL docs state

If this option is enabled, UPDATE and DELETE statements that do not use a key in the WHERE clause or a LIMIT clause produce an error.

Solution
Though --safe-updates is a security option and it should be turned on but many times you want to remove all the records from a table to start afresh and for this, a DELETE FROM <table> statement needs to be issued.
With --safe-updates option enabled, MySQL will not allow this. So, we need to disable it using any of the following ways.
1. Using query
Execute below query to turn off --safe-updates mode.

set sql_safe_updates = 0;

Since it is a security feature, it is recommended to turn it on after the required query is executed.
So, it is better to wrap your SQL query between the two statements as shown below.

set sql_safe_updates = 0;
-- your query
set sql_safe_updates = 1;

2. Using not equal to
With this method, you are not required to disable --safe-updates mode, you can create a query with WHERE clause which returns true for all records.
But remember, this WHERE clause must contain the primary key for the table.
So, for deleting all the records of a table, use below query

DELETE FROM <table name> where id <> 0;

<> is not equal to operator and it will be true for all records since each will have an id other than 0.
3. From MySQL workbench
--sql-updates option can be turned off from MySQL workbench as well.
Click MySQLWorkbench menu item at the top(on Mac) and Preferences as shown below.

On Windows, it would be Edit -> Preferences.

Click on SQL Editor menu option in the window that appears. You will find Safe Updates as the last option on the right.
Look at the image below.

Uncheck and click OK.
Now execute the query, it should work.

Hope the article was useful.