MySQL Workbench is a useful visualization tool for database architects, which provides similar features to PostgreSQL pgAdmin. Even though, that I am not a big fan of MySQL (I am a Postgres zombie 😉 ) , I cannot escape from it since it is widely used anywhere in any project.
MySQL Workbench by default has a safety feature on which disallows you to mass update. For instance, if you run a query similar to following,
UPDATE information SET name = 'ABC';
MySQL Workbench throws an error and stops your transaction with below message.
Error Code: 1175. 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.
The reason for such a behavior is because where
clause is missing from the query which affects all rows of the table. This makes much sense in production environments and reduce chances of making terrible mistakes.
Though, in testing environments such a feature might be little bit annoying especially for a person like me. Therefore, I have found a workaround for such a problem and does not require you to turn off the feature as program recommends as well as me. The solution is fairly simple, every time you want to run a query like above, you need to add two additional queries to turn off the safety feature before query and turn it back on after the execution. Look at the new query in below,
SET SQL_SAFE_UPDATES=0;
UPDATE information SET name = 'ABC';
SET SQL_SAFE_UPDATES=1;
Keep note that I recommend neither turning off safety feature nor mass updating without where
clause. Apparently, the best approach is to always have where
in update queries.