MySQL Safe Update Mode Explained

  • Avinash
  • 0
  • Oct 09, 2011
  • Database

Recently I was performing one very simple MySQL query. But I am not getting success for that query. So after few mins of research I am found the solution for that.

Actually my query was just like below:

UPDATE table_name SET bDeleted=0;
  UPDATE table_name SET bDeleted=0;

Isn’t it so simple? yes it is. But it was not working. Once I have started to debug the process I found that MySQL is throwing the below error:

“You are using safe update mode and you tried to update a table without a WHERE clause that uses a KEY column.”

What is MySQL Safe Mode?

MySQL will refuse to run the UPDATE or DELETE query if executed without the WHERE clause or LIMIT clause. MySQL will also refuse the query which have WHERE clause but there is no condition with the KEY column.

So below query will also be refused if safe mode is enabled:

UPDATE table_name SET bDeleted=0 WHERE name='xyz';
  UPDATE table_name SET bDeleted=0 WHERE name='xyz';

How to disable MySQL Safe Mode?

There is a simple way to disable the MySQL Safe Mode. We can disable it with simple query.

Have a look at below query:

SET SQL_SAFE_UPDATES=0; UPDATE table_name SET bDeleted=0 WHERE name='xyz';
  SET SQL_SAFE_UPDATES=0;
  UPDATE table_name SET bDeleted=0 WHERE name='xyz';

Note: MySQL will also refuse the query which have WHERE clause but there is no condition with the KEY column.

Related Posts

Written by Avinash

Avinash Zala is leading various projects which deals with the various technology involved with the web. A combination of perfect technical and management skills. Avinash would like to chat with you and convert your imagination into the working system. You can get in touch with him on Facebook and Twitter.

View all posts by: