Cheeky Cheeky - 4 months ago 137
SQL Question

MySQL "Row 30153 was cut by GROUP_CONCAT()" error

I have a function listed below. When I call it with the LIMIT set at 0,60 as seen below, it works fine. However, whenever I increase that LIMIT to 70 or higher, or even remove the LIMIT, MySQL errors when I call the function with the error: "Row 30153 was cut by GROUP_CONCAT()".

I have tried increasing the varchar values to 10 000 but that does not help.
As far as I can understand from the error, their doesn't seem to be enough space i nthe variable for the contents. But like I mentioned, I have tried increasing the size but it doesn't help. Any ideas?? Thanks

DELIMITER $$

DROP FUNCTION IF EXISTS `fnAlbumGetPhotoList` $$
CREATE DEFINER=`root`@`%` FUNCTION `fnAlbumGetPhotoList`(_albumId int) RETURNS varchar(2048) CHARSET utf8
BEGIN

DECLARE _outPhotoList VARCHAR(2048);

SET _outPhotoList = (

SELECT (CAST(GROUP_CONCAT(CONCAT(photoId, '|', photoFileName) separator '~') AS CHAR(10000) CHARACTER SET utf8)) AS recentPhotoList
FROM
(
SELECT photoId, photoFileName
FROM photo
WHERE photoAlbumId = _albumId
AND photoIsDisabled = 0
AND photoIsActive = 1
ORDER BY photoId DESC
LIMIT 0,60
) as subQuery
);


RETURN _outPhotoList;

END $$

DELIMITER ;

ain ain
Answer

You could set the group_concat_max_len variable to bigger value. Or perhaps use GROUP_CONCAT(DISTINCT ...) to shorthen the result.