Seriously, it did. Sorta.
I use Workbench for my daily work, and it’s a great tool. If you haven’t tried the 5.2 release yet, you should. While performing some maintenance, I happened to issue a DELETE statement against a table which had no indexes (it was 10 rows), and Workbench complained:
Error Code: 1175
You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column
It turns out this is a new feature in 5.2.26 (and is still there in 5.2.27) – Workbench now uses the equivalent of –safe-updates mode for the mysql command-line client (also known as the –i-am-a-dummy option – seriously). This wasn’t exactly convenient for me, especially since the DELETE was part of a larger script which I then had to revise and step through manually after it failed, but there’s an easy way to change this behavior. If you’re like me, you might consider disabling this:
- Go to Edit -> Preferences
- Select the SQL Editor tab
- Uncheck “Forbid UPDATE and DELETE statements without a WHERE clause (safe updates)”
Despite the text, the –safe-updates mode affects more than UPDATE and DELETE statements without WHERE clauses – it requires such statements to explicitly use indexes.
I’m changing this behavior on my installation before I run into other problems.
I’m no dummy. 🙂
ty. just ran into that X)
Hi,
just a note on this, remember to restart the application after making this change…
🙂
My change took effect without a restart, but it’s a good tip to keep in mind if your experience differs. Thanks Vince and kostrivec!
That’s what i was looking for, thank!
By the way, don’t forget to RESTART workbench after change of this setting to make it work.-]
Grrrr
I hate this dumbing down of software.
Thanks for the article on how to disable “safe-mode”
Perfect! That’s exactly what I needed! 🙂 Thank you, thank you, thank you for sharing.
It didnt allow me even when I had where clause …
LOL Gota love command line dummies! I am one too!
Thanks, I just run into this myself… and it took me a while to realize I had to restart. Was it too hard to have a prompt about restarting app for changes that need restart?
Great tool, but a lot of strange behavior. Worth it though.
Thanks a lot. I just ran into this and was getting super pissed off. This worked after I restarted the program.
thanx .. i just updated from workbench 5.1 to 5.2. and it is giving me lot of troubles. thanx for the help.
lol thank u
That helped.
Thank you
Thanks a lot, its working after unchecking the option which you have mentioned in the article. Thanks once again… 🙂
It agree, very amusing opinion
Thanks, this is exactly what I was looking for 🙂
Gracias.
De nada!
I know this post was a while ago, but for the record looks like the setting is in “SQL Queries”-tab now.
Thanks for helping keep this advice current, Eric!
FYI, you don’t need to restart the whole application… just close your current DB connection tab(s) and re-connect.
Great tip, Joe – thanks! I think that behavior might be more recent than my original post, so thanks for keeping the advice current!
My sql workbench having “Safe Update” checked and I am using below query
UPDATE User
set StatusReason = 0
WHERE
UserID = ‘9379d480-f245-11e4-a1e3-22000b4d0d43’;
where UserID varchar(36) is primary key for the table User, But I am still getting the error.
Can anyone help me with what is wrong in the query
Hi Rakesh,
From your description alone, that sounds like a bug. It would be best for you to report it at bugs.mysql.com and provide more detail (actual table definition will be helpful).
What I can say is that Workbench only sets a server-side session variable, and the actual error comes from the MySQL Server itself (which also does the actual parsing of the query and makes the decision whether it violates the –safe-updates mode or not). That means that if there actually is a bug, it’s most likely caused by MySQL Server (not Workbench). Including in the bug report the version of MySQL Server being used will therefore also be helpful.
From Latest Version(MySql Workbeanch)
Go to Edit -> Preferences
Select the SQL Queries tab
Uncheck “Forbid UPDATE and DELETE statements without a WHERE clause (safe updates)”