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

Question

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 ;

Answer 1

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

Answer 2

1) Increase the limit on the number of characters from the resultant query

SET global group_concat_max_len=15000;
OR
SET session group_concat_max_len=15000;

Use the former if you want the setting to be the new global default (sticky).
Use the latter if you want to use this setting during the current session only.
(Note also that some have reported trouble when using the global option. In that case, try leaving it off, as in SET group_concat_max_len=15000;.)

2) Then add DISTINCT as first param to GROUP_CONCAT() to remove duplicates from the result query. GROUP_CONCAT(DISTINCT ..).

Your query will look more like this:

SET session group_concat_max_len=15000;
...
    ... GROUP_CONCAT(DISTINCT CONCAT(photoId, ...)
...
)      

---

Function Group Concat, from MySQL docs:

SET [GLOBAL | SESSION] group_concat_max_len = val;

In MySQL, you can get the concatenated values of expression combinations. To eliminate duplicate values, use the DISTINCT clause.

...

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

The return value is a nonbinary or binary string, depending on whether the arguments are nonbinary or binary strings. The result type is TEXT or BLOB unless group_concat_max_len is less than or equal to 512, in which case the result type is VARCHAR or VARBINARY.

Presumably not specifying an optional alternative (GLOBAL or SESSION) will default to the first listed alternative (GLOBAL in this case), though I could not find this explicitly stated in the documentation.

About syntax used in the MySQL docs:

When a syntax element consists of a number of alternatives, the alternatives are separated by vertical bars (“|”).

When one member from a set of choices may be chosen, the alternatives are listed within square brackets (“[” and “]”):

When one member from a set of choices must be chosen, the alternatives are listed within braces (“{” and “}”)