MySql Safe Mode
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 get the 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, switch the option in Preferences β SQL Editor and rejoin. 0.000 s
I don't think disabling secure update is a good idea. Disable save mode answers here Maybe there is another solution. Any ideas?
Added dummy condition! = '' And removal 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") don't work
Thanks @ tim-biegeleisen for the help!
source to share
Perhaps you could try adding a dummy condition to the clause WHERE
that uses the key, like
DELETE FROM session
WHERE Id <> '' AND
Id IN (SELECT * FROM (SELECT Id FROM session WHERE CreatedAt > NOW()) AS temp);
Assuming the primary key column is Id
always not an empty string, adding this condition will not affect your delete logic.
As you mentioned, you can also disable Safe Mode in your session with:
SET SQL_SAFE_UPDATES = 0;
If you are paranoid / careful, you can re-enable secure updates after making a delete request:
SET SQL_SAFE_UPDATES = 1;
source to share