MySql safe mode delete

Question

I am using MySql 5.7

DROP TABLE IF EXISTS `session`;
CREATE TABLE `session` (
`Id` varchar(128) NOT NULL,
`Browser` varchar(128) NOT NULL,
`IpAddress` varchar(128) NOT NULL,
`UserId` varchar(128) NOT NULL,
`CreatedAt` datetime DEFAULT NULL,
`ModifiedAt` datetime DEFAULT NULL,
`CreatedBy` varchar(256) DEFAULT NULL,
`ModifiedBy` varchar(256) DEFAULT NULL,
PRIMARY KEY (`Id`)
);


DELETE FROM session 
WHERE Id IN (SELECT * FROM (SELECT Id FROM session WHERE CreatedAt > NOW()) AS temp);

I am getting an error: 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. 0.000 sec

I dont think disabling safe update is a good idea. Turn off save mode answers here Maybe there is other solution. Any ideas ?

Added dummy condition !='' and delete works

DELETE FROM session 
WHERE Id !='' AND Id IN (SELECT * FROM (SELECT Id FROM session WHERE Browser = "Mozilla") AS temp);

select * from session;

Dummy conditions like Id IS NOT NULL or Id IN ("some_dummy_id_here") doesnt works

Thanks to @tim-biegeleisen for help !

Tim Biegeleisen · Accepted Answer · 2017-06-27T12:02:23.017

6

Perhaps you could try adding a dummy condition to the WHERE clause which uses the key, e.g.

DELETE FROM session 
WHERE Id <> '' AND
      Id IN (SELECT * FROM (SELECT Id FROM session WHERE CreatedAt > NOW()) AS temp);

Assuming that the primary key column Id is always not empty string, then adding this condition won't affect your delete logic.

As you mentioned, you could also disable safe mode in your session via:

SET SQL_SAFE_UPDATES = 0;

If you are paranoid/cautious, you could even turn safe updates back on after executing your delete query:

SET SQL_SAFE_UPDATES = 1;

edited Jun 27 '17 at 12:02

answered Jun 27 '17 at 11:36

Tim Biegeleisen

502,043
27
286
360

Id is PK. I tried "is not null" and "Id IN ("dummy_Id_here")" doesnt work – Oleh Jun 27 '17 at 11:38
Please post the table structure for `session`. – Tim Biegeleisen Jun 27 '17 at 11:39
updated question with table. We use GUID for Id , that is why it is varchar(128) – Oleh Jun 27 '17 at 11:42
I'm surprised my answer didn't work. If you read the answers [here](https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench), you will see a few answers suggesting the trick which I suggested above. – Tim Biegeleisen Jun 27 '17 at 11:45
You think its ok to have SET SQL_SAFE_UPDATES = 0; than delete query and SET SQL_SAFE_UPDATES = 1 ? – Oleh Jun 27 '17 at 11:50

MySql safe mode delete

1 Answers1