CODEWITHSUNDEEP
mysql
replay
replay

Reputation: 3809

Query fails due to `safe update mode`

I have the following table:

CREATE TABLE `number_locks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number` varchar(255) NOT NULL,
  `timeout` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `number` (`number`),
  KEY `timeout` (`timeout`)
) ENGINE=InnoDB AUTO_INCREMENT=69146 DEFAULT CHARSET=latin1

Now I would like to run this prepared delete statement in it:

> SET @number="123123";
Query OK, 0 rows affected (0.17 sec)  

> PREPARE delete_number_lock FROM 'DELETE FROM number_locks WHERE number=? OR timeout<NOW()';
Query OK, 0 rows affected (0.17 sec)
Statement prepared  

> EXECUTE delete_number_lock USING @number;
ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

I can't see why it thinks that there is no where condition using a KEY column.

Upvotes: 0

Views: 537

Answers (2)

geeksal
geeksal

Reputation: 5016

By default mysql runs in a safe update mode which helps users causing any accidental modification in the database. As you can see in the MYSQL REFERENCE MANUAL which says

It is helpful for cases when you might have issued a DELETE FROM tbl_name statement but forgotten the WHERE clause. Normally, such a statement deletes all rows from the table. With --safe-updates, you can delete rows only by specifying the key values that identify them. This helps prevent accidents.

Safe mode also reject the statements where the where clause does not use a key column or constraint as the manual further says

You are not permitted to execute an UPDATE or DELETE statement unless you specify a key constraint in the WHERE clause or provide a LIMIT clause (or both)

In your case the delete query does not uses a key constraint hence it is not executed.

So to disable safe mode try this:

SET SQL_SAFE_UPDATES=0;

MORE UPDATE

See your query DELETE FROM number_locks WHERE number=? OR timeout<NOW()

Here the column number is not a key column, hence the error. In case if you use id instead of number than such error would not occur because id is the primary key.

Even More Update

OR separates your WHERE clause into two logical parts. Let me explain you number="12345" OR timeout<NOW() becomes (number || timeout) which is not a key. But the condition using AND will works because it takes both the column collectively into the consideration. Also it is interesting to note that OR will work if you use same column name for example number="12345" OR number='564789'

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15961

I've found the quickest way to circumvent that is by adding a condition using the primary key that will always be true, like so:

DELETE 
FROM number_locks 
WHERE id > 0 AND (number=? OR timeout<NOW())
;

Upvotes: 0

Related Questions