Trouble with CONCAT and Longtext

Question

The SQL...

UPDATE Threads t 
SET t.Content = (
    SELECT GROUP_CONCAT(a.Content ORDER BY a.PageID SEPARATOR '<!-- pagebreak -->') 
    FROM MSarticlepages a
    WHERE a.ArticleID = t.MSthreadID GROUP BY a.ArticleID
)

As you can see it takes all of an article's pages (which are each stored as longtext in separate rows) and GROUP_CONCATs them into a single longtext row. The problem is the results are only so many characters and then it gets completely truncated, losing about 90% of the contents. Does CONCAT not handle longtext very well or is there something else I am doing wrong?

Answer 1

According to the MySQL manual, the maximum length of GROUP_CONCAT is defined by the group_concat_max_len system variable, which defaults to 1024.

This value can be increased, by using the following command:

SET group_contact_max_len = <int>

It should be noted, however, that the value of group_contact_max_len is itself limited by the value of another system variable, max_allowed_packet, which defaults to 1,048,576.

This value can be increased to a maximum of 1,073,741,824, using the same syntax:

SET max_allowed_packet = <int>

Answer 2

GROUP_CONCAT at mysql docs:

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;

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

Hope this helps.