Have you ever had this happen? You’re just trying to run a SQL script to update a table. Suddenly the SQL system balk at you. It reports to you: 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. Often this error comes with an Error Code 1175.
What is this SQL error all about?
In essence, SQL is trying to protect you from accidentally deleting or altering large swaths of data. If you were to type in something like Delete from MyTable where year > 1900 – the system is worried you could be causing yourself harm. So it’s trying to protect you from yourself.
Sometimes, though, you really do want to make this type of large-scale change. Maybe you have backed up a slew of data and now need to clear it out of the system. You’ve already verified the backup files are all set. And you really don’t want to go designating a key field just to do this delete operation.
To change this safe mode setting:
In MySQL Workbench, click on the top Edit menu option and then choose Preferences.
Now, under the SQL Editor area, it’s in the main area but you have to SCROLL DOWN to see it:
At the very bottom of that area is an option for:
Safe Updates (rejects UPDATEs and DELETEs with no restrictions)
Note that this says “no restrictions” but actually it will fail sometimes even if you do have a ‘where’ clause. It will fail if that where clause doesn’t involve a key field.
Uncheck that box. Click OK to save it. Then CLOSE DOWN MySQL WorkBench. You have to open it up again for this change to take place.
Leave a Reply