Error Code: 1175. You are using safe update mode

MySQL – Fix Error Code: 1175 (Safe Update Mode)

When running an UPDATE or DELETE statement in MySQL Workbench, you may encounter the error: Error Code: 1175. You are using safe update mode. This error occurs because MySQL Workbench has a setting called “Safe Update Mode,” which prevents updates and deletions without a WHERE clause or with conditions that modify large portions of the table.

In this tutorial, we’ll cover the reasons for this error, how to disable safe update mode temporarily, and how to turn it off permanently.

Why Safe Update Mode Causes Error 1175

Safe update mode in MySQL Workbench is intended to prevent accidental data loss by requiring a WHERE clause or primary key-based condition in UPDATE and DELETE statements. When safe update mode is enabled, the following actions are restricted:

• Running an UPDATE statement without a WHERE clause.
• Running a DELETE statement without a WHERE clause.
• Running UPDATE or DELETE statements that affect a large part of the table, which could potentially lead to unintentional data changes.

The following is the typical response when the the safe update mode is on.

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

Solution 1: Temporarily Disable Safe Update Mode

If you only want to disable safe update mode for a single session, you can use the following command:

SET SQL_SAFE_UPDATES = 0;

This command disables safe update mode for the current session. After running this, you should be able to execute your UPDATE or DELETE statements without the 1175 error. For example:

SET SQL_SAFE_UPDATES = 0;

UPDATE students
SET age = 21
WHERE rollno = 14;

Once the session ends, safe update mode will be enabled again if it’s set as the default in MySQL Workbench settings.

Solution 2: Permanently Disable Safe Update Mode in MySQL Workbench

If you want to permanently disable safe update mode in MySQL Workbench, follow these steps:

1. Open MySQL Workbench and go to Edit > Preferences.
2. In the Preferences window, select SQL Editor from the left sidebar.
3. Under the SQL Execution section, uncheck the box for Safe Updates (also known as safe update mode).
4. Click Apply to save the changes and restart MySQL Workbench for the changes to take effect.

With safe update mode disabled, you’ll no longer receive Error Code 1175, and you can perform UPDATE and DELETE operations without specifying a WHERE clause.

Alternative Solution: Modify Statements to Work with Safe Update Mode

If you prefer to keep safe update mode enabled, you can modify your queries to meet its requirements. Here are some adjustments:

• Add a WHERE clause: Specify a WHERE condition to limit the rows affected.
• Use a primary key: Include a primary key in the WHERE clause to ensure the update is specific to a single row.

For example, instead of using:

UPDATE students
SET age = 20;

Rewrite it with a WHERE clause to specify which rows to update:

UPDATE students
SET age = 20
WHERE rollno = 14;

Conclusion

Error Code 1175 in MySQL is a safeguard to prevent unintentional data updates or deletions. You can disable safe update mode temporarily, turn it off permanently in MySQL Workbench, or adjust your queries to comply with safe update requirements. Choosing the best option depends on your workflow and how you manage your MySQL data.