MySQL – Workbench – Error – “You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.” ( Error Code: 1175 )

Background

Using MySQL’s Workbench to run a simple query.

But, ran into an easy to remediate error.

Query

SQL


start transaction;

delete from `sales`.`order`;

delete from `sales`.`product`;

delete from `sales`.`customer`;

commit
;

Error

Error Image

mySQL.workbench.SQLEditor.Error.03.20200812.0743PM

Error Text


delete from `sales`.`order`	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.

Remediate

Outline

  1. SQL
    • Use SQL_SAFE_UPDATE
      • Set SQL_SAFE_UPDATE off
      •       SQL Statements
      • SET SQL_SAFE_UPDATES on
  2. GUI
    • SQL Workbench

SQL

Use SQL_SAFE_UPDATE

Outline

  1. Use SQL_SAFE_UPDATE
    • Set SQL_SAFE_UPDATE off
    •       SQL Statements
    • SET SQL_SAFE_UPDATES on

SQL


-- Disable SQL Safe Updates Checks
SET SQL_SAFE_UPDATES=0;

start transaction;

delete from `sales`.`order`;

delete from `sales`.`product`;

delete from `sales`.`customer`;

commit;

-- Enable SQL Safe Updates Checks
SET SQL_SAFE_UPDATES=1;

GUI

SQL Workbench

Outline

 

  1. Access Menu
    • Edit/Preferences
      • Choose the option Edit/Preferences
      • Access the Page – General Editors / SQL Editors
      • Make sure the option “Safe Updates (rejects Updates and Deletes with no restrictions)” is unchecked
      • Exit
    •  Query/Reconnect to Server
      • Please access the menu options Query/Reconnect to Server
      • Reconnecting to server ensures that the current “Safe Updates” setting is effected on the server

Images

Image – Before

mySQL.workbench.SQLEditor.01.20200812.1045AM.PNG

Image – After

mySQL.workbench.SQLEditor.02.20200812.1046AM

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s