Workbench called me a dummy!

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.  🙂

25 thoughts on “Workbench called me a dummy!

  1. Hi,

    just a note on this, remember to restart the application after making this change…

    🙂

    1. 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!

  2. 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.-]

  3. Grrrr

    I hate this dumbing down of software.

    Thanks for the article on how to disable “safe-mode”

  4. 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.

  5. thanx .. i just updated from workbench 5.1 to 5.2. and it is giving me lot of troubles. thanx for the help.

  6. Thanks a lot, its working after unchecking the option which you have mentioned in the article. Thanks once again… 🙂

  7. I know this post was a while ago, but for the record looks like the setting is in “SQL Queries”-tab now.

  8. FYI, you don’t need to restart the whole application… just close your current DB connection tab(s) and re-connect.

    1. Great tip, Joe – thanks! I think that behavior might be more recent than my original post, so thanks for keeping the advice current!

  9. 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

    1. 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.

  10. 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)”

Leave a Reply to mahendran Cancel reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.