MYSQL Truncated incorrect INTEGER value error

Question

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

If TagId (tag.TagId, I assume) is INTEGER, is objectTagTagId INTEGER too? Or vice versa. And same about other pairs of values you are comparing in the WHERE clause (in the function). — Andriy M
– Andriy M, Commented Aug 25, 2011 at 19:28
@Andriy M: Yeah - all columns used in the WHERE clause are the same type. — Paolo Broccardo
– Paolo Broccardo, Commented Aug 26, 2011 at 6:12

Poodlehat · Accepted Answer · 2011-08-26 17:45:54Z

2

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))

answered Aug 26, 2011 at 17:45

Poodlehat

3601 gold badge2 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paolo Broccardo Over a year ago

Ok, so adding the cast didn't work. But then after some fiddling it turns out that the query calling this function needed a cast, i.e. CAST(fnObjectTagGetObjectTags(2, profile.profileId) AS CHAR) AS objectTags Thanks for sending me in the right direction.

Collectives™ on Stack Overflow

MYSQL Truncated incorrect INTEGER value error

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related