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.  :)

Tagged ,

24 thoughts on “Workbench called me a dummy!

  1. robsn says:

    ty. just ran into that X)

  2. Vince Kruger says:

    Hi,

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

    :)

    • Todd Farmer says:

      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!

  3. kostrivec says:

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

  4. Matt Way says:

    Grrrr

    I hate this dumbing down of software.

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

  5. tmloliv says:

    Perfect! That’s exactly what I needed! :) Thank you, thank you, thank you for sharing.

  6. HermanX says:

    It didnt allow me even when I had where clause …

  7. Haxy Waxy says:

    LOL Gota love command line dummies! I am one too!

  8. Gyamfi says:

    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.

  9. Jason says:

    Thanks a lot. I just ran into this and was getting super pissed off. This worked after I restarted the program.

  10. mandeep gill says:

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

  11. punkpette says:

    lol thank u

  12. PS says:

    That helped.
    Thank you

  13. Vamshi Kiran says:

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

  14. It agree, very amusing opinion

  15. Bosh says:

    Thanks, this is exactly what I was looking for :)

  16. Nicser says:

    Gracias.

  17. Eric says:

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

  18. Joe Caputo says:

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

    • Todd Farmer says:

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

  19. Rakesh Kumar says:

    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

    • Todd Farmer says:

      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.

Leave a Reply

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


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>