Trouble with GROUP_CONCAT and Longtext in MySQL

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?

Christean

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_concat_max_len = <int>

It should be noted, however, that the value of group_concat_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>

Riparian

Note that due to some MySQL bug (wontfix) max_allowed_packet does not mean max allowed packet only. See bugs.mysql.com/bug.php?id=20458#c113677 and dba.stackexchange.com/a/2383/9405 – Anthropophagite

Answer 2

The title of this post is "Trouble with CONCAT and Longtext" which is misleading since he who posed the question really wanted to know about GROUP_CONCAT. I found this post in Google because I was dealing with a limitation with CONCAT in MySQL. For those of you who find this post and are looking for how to increase the max length allowed for CONCAT here is how to do it:

The problem is adjusting group_concat_max_len won't work for CONCAT it only works for GROUP_CONCAT which means that if you are running into this limitation with CONCAT you will have to rework your query to use GROUP_CONCAT.

So say you are using CONCAT in the following way:

UPDATE some_table
SET some_field=CONCAT(some_field,'super long string to append to the end of the data in some_field')
WHERE some_criteria_field = 'match on this string';

But the data you are trying to concatenate on the end of some_field's contents is getting truncated or just plane setting the field some_field to null/empty. So here is the way the query will have to look to alleviate the clear limitations of CONCAT:

SET @@session.group_concat_max_len = @@global.max_allowed_packet;
UPDATE some_table SET some_table.some_field=(
     SELECT GROUP_CONCAT( queue.append_to_end SEPARATOR '') as new_some_field
     FROM
     (
          SELECT append_to_end FROM some_table WHERE some_criteria_field = 'match on this string'
          UNION
          SELECT 'super long string to append to the end of the data in some_field' as append_to_end
     ) as queue
) WHERE some_criteria_field = 'match on this string'

For more in depth information check out the article where I found this answer at the link bellow. Source: http://boulderapps.co/mysql-concat-limitation

Pillory

Answer 3

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.

Poltergeist