So, you’ve just got MySQL Workbench installed and you’ve tried to run some of the handy commands that you’ve picked up from W3Schools (seriously, if you haven’t been through this before, you should!), specifically you’re trying to run a simple UPDATE command in MySQL against a table. Great. But… you’ve noticed that this doesn’t quite work out of the box with MySQL Workbench and it’s likely telling you that you cannot update due to MySQL Workbench being in SAFE MODE. But what is this?
Safe Mode in MySQL Workbench is designed to save you from yourself. Basically it stops you running update commands against anything but a Primary Key. So when you run an update command, you need to specify a Primary Key against the table to ensure you are only updating a single row, rather than wiping out your entire database. While this is great for newbies, it is a little annoying for those more advanced users. When Safe Mode is enabled in MySQL is makes sure rookie mistakes can’t be made, i.e. `UPDATE my_table SET column_name = 123` (whoops…. No ‘WHERE’ command….). #DataGone.
So… It’s time to…. DISABLE SAFE MODE! (at your own peril…aka. You break it….. you fix it!). But thankfully, you’re a little more experienced than the average rookie. You are READY to DISABLE SAFE MODE!
So, now we’ve got past the #BeCareful section, let’s look at how you exactly disable Safe Updates in MySQL Workbench so you can run all the updates you like and as you see fit.
Disable Safe Updates in MySQL Workbench
It’s actually really simple. Follow the steps below within MySQL Workbench;
- Select from the main menu: Home > Edit > Preferences
- Click the ‘SQL Editor’ menu item
- Uncheck the option for ‘Safe Updates (rejects UPDATEs and DELETEs with no restrictions)’
- Click ‘OK’
- Now ‘Disconnect’ and ‘Reconnect’ to the server and you’re good to go! Simple!
And just for re-iteration, disabling this can really break things – so be careful! If you aren’t sure what you are doing you can easily overwrite data for column(s) in your entire database by missing a simple command. Triple check every command before running it while Safe Updates are disabled.
Michael Cropper