Mysql Truncated incorrect DOUBLE value error for string checking

Question

I have a stored procedure

CREATE PROCEDURE `DeleteAttachmentsByIDs`(IN p_ids nvarchar(1024), IN p_modifiedBy varchar(100), IN p_modifiedDate datetime)
BEGIN
    update attachments set
    `IsDeleted` = 1
    , `ModifiedBy` = p_modifiedBy
    , `ModifiedDate` = p_modifiedDate
    where 
    id IN (p_ids);
END

Now I called with

CALL `DeleteAttachmentsByIDs`('12479,12480,12481', 'admin', '2019-04-02 15:32:30.558178')

And it comes the error

Error Code: 1292. Truncated incorrect DOUBLE value: '12479,12480,12481'

The create table command for attachments table is

CREATE TABLE `attachments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ObjectID` int(11) DEFAULT NULL,
  `ObjectName` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `Category` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `ContentType` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `ContentLength` bigint(20) DEFAULT NULL,
  `FileName` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `Data` longblob,
  `Description` varchar(500) CHARACTER SET utf8 DEFAULT NULL,
  `CreatedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `CreatedDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ModifiedBy` varchar(100) CHARACTER SET utf8 DEFAULT NULL,
  `ModifiedDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `IsDeleted` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Index1` (`id`,`ObjectID`,`ObjectName`,`IsDeleted`)
) ENGINE=InnoDB AUTO_INCREMENT=12482 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I think this may be caused by the 'in' condition which need array, however string is passed in. I think I need to split the string into array to make the 'in' works. Is that correct and how I can do it?

Sebastian Brosch · Answer 1 · 2019-04-02T10:04:41.670

2

You can use FIND_IN_SET in this case:

CREATE PROCEDURE `DeleteAttachmentsByIDs` (IN p_ids NVARCHAR(1024), IN p_modifiedBy VARCHAR(100), IN p_modifiedDate DATETIME)
BEGIN
    UPDATE attachments SET
        `IsDeleted` = 1,
        `ModifiedBy` = p_modifiedBy,
        `ModifiedDate` = p_modifiedDate
    WHERE FIND_IN_SET(id, p_ids) > 0 AND id <> 0; -- AND id <> 0 to support safe-mode.
END

Another solution can be the following, using a string comparison with LIKE:

CREATE PROCEDURE `DeleteAttachmentsByIDs` (IN p_ids NVARCHAR(1024), IN p_modifiedBy VARCHAR(100), IN p_modifiedDate DATETIME)
BEGIN
    UPDATE attachments SET
        `IsDeleted` = 1,
        `ModifiedBy` = p_modifiedBy,
        `ModifiedDate` = p_modifiedDate
    WHERE CONCAT(',', p_ids, ',') LIKE CONCAT('%,', id, ',%') AND id <> 0; -- AND id <> 0 to support safe-mode.
END

The MySQL Workbench is using safe-mode by default. So you can add AND id <> 0 to suppress the error thrown by MySQL Workbench. Read more about the safe-mode here: MySQL error code: 1175 during UPDATE in MySQL Workbench

edited Apr 02 '19 at 10:04

answered Apr 02 '19 at 08:30

Sebastian Brosch

42,106
15
72
87

Thanks. Both method returns 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. ``` – want_to_be_calm Apr 02 '19 at 09:36
is `id` a key column? – Sebastian Brosch Apr 02 '19 at 09:40
can you update your question and add the `CREATE TABLE` command for the `attachments` table? The query should work, but MySQL Workbench is using the safe-mode by default and can't detect the key column. – Sebastian Brosch Apr 02 '19 at 09:52
@want_to_be_calm - see the update. Looks like MySQL Workbench doen't detect the key column inside functions (`FIND_IN_SET`, `CONCAT`). – Sebastian Brosch Apr 02 '19 at 10:09
Thanks. But this time the query run so long and finally timeout expired. – want_to_be_calm Apr 03 '19 at 01:55

Mysql Truncated incorrect DOUBLE value error for string checking

1 Answers1