CODEWITHSUNDEEP
mysql
Paolo Broccardo
Paolo Broccardo

Reputation: 1744

MYSQL Truncated incorrect INTEGER value error

I am running a query and getting the mysql error 1292: "Truncated incorrect INTEGER value" It is a warning and my select works fine, but I would still like to clear up the warnings nonetheless.

I can confirm that the warning occurs everytime the function finds tags linked to the object. When no tags are found, the warning does not occur. So if 50 out of 1000 objects have tags, I will get 50 warnings, like this:

Truncated incorrect INTEGER value: '1|Blondes'

The database function used is:

DELIMITER $$

DROP FUNCTION IF EXISTS `fnObjectTagGetObjectTags` $$
CREATE DEFINER=`root`@`%` FUNCTION `fnObjectTagGetObjectTags`(_objectType int, _objectId bigint) RETURNS varchar(2048) CHARSET utf8
BEGIN

  DECLARE _outObjectTags VARCHAR(2048);

  SET _outObjectTags =
    (
      SELECT (CAST(GROUP_CONCAT(CONCAT(tagId, '|', tagName) separator '~') AS CHAR(10000) CHARACTER SET utf8)) AS objectTagList
      FROM
      (

      SELECT tagId, tagName
        FROM objectTag
        INNER JOIN tag
          ON tagId = objectTagTagId
        WHERE objectTagObjectType = _objectType
          AND objectTagObjectId = _objectId
          AND objectTagIsDisabled = 0
          AND objectTagIsActive = 1
          AND tagIsDisabled = 0
          AND tagIsActive = 1
      ) as subQuery
    );

  RETURN _outObjectTags;

END $$

DELIMITER ;

And the calling query is simply: 

SELECT fnObjectTagGetObjectTags(3, album.albumId)
FROM album
WHERE fnObjectTagGetObjectTags(3, album.albumId) IS NOT NULL
AND albumIsDisabled = 0
AND albumIsActive = 1

I just can't figure out why it is doing this. Anyone see anything odd? I am running 5.5.13 Thanks

Upvotes: 2

Views: 12480

Answers (1)

Poodlehat
Poodlehat

Reputation: 360

Try an explicit cast of just the tagId as a character before the concatenation, since you may be mixing binary and non-binary strings. Like

SELECT (CAST(GROUP_CONCAT(CONCAT(CAST(tagId AS CHAR), '|', tagName) separator '~') AS CHAR(10000) CHARACTER SET utf8))

Upvotes: 2

Related Questions