MySQL GROUP_CONCAT chopping off half the data (sometimes)-CodePudding

I seem to have a very strange issue... the following query chops off some of the result of the GROUP_CONCAT

select
    booking_id,
    group_concat(booking_resource_info_grouped_bla)
from (
    SELECT
            b.id AS booking_id,
            GROUP_CONCAT(DISTINCT booking_resource_info SEPARATOR '{{hr}}') AS `booking_resource_info_grouped_bla`
    FROM
    (
        SELECT 492185 AS id, '25ft Climbing 1' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, '25ft Climbing 2' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, '3 G Swing' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Archery 1' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Body Boarding' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Canadian Canoes' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Catering 1' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Crate Stack 1' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Kayak 1' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Kayak 2' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Orange Lodge' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Lake' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Low Ropes' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Marquee 1 - Site 3' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Parker Lodge' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Raft Building 1' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Raft Building 2' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Raft Building 3' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'School Package 1' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Apple Lodge' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Team Building 1' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Tomahawks' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Banana Lodge' as booking_resource_info
        UNION ALL
        SELECT 492185 AS id, 'Wobble Pole' as booking_resource_info
    ) b
    GROUP BY b.id
    UNION ALL
    SELECT
        123 AS booking_id,
        '' AS `booking_resource_info_grouped_bla`
) test
GROUP BY booking_id
;

This produces:

25ft Climbing 1{{hr}}25ft Climbing 2{{hr}}3 G Swing{{hr}}Apple Lodge{{hr}}Archery 1{{hr}}Banana Lodge{{hr}}Body Boarding{{hr}}Canadian Canoes{{hr}}Catering 1{{hr}}Crate Stack 1{{hr}}Kayak 1{{hr}}Kayak 2{{hr}}Kiwi Lodge{{hr}}Lake{{hr}}Low Ropes{{hr}}Marquee 1 - Site 3{{hr}}Orange Lodge{{hr}}Raft Building 1{{hr}}Raft Building 2{{hr}}Raft Bui

As you can see, the tail part Raft Bui is incomplete.

On the other hand, I have had this query work correctly. I was having it work on a different server, so I restarted one of them and then that one started working as well.

I've tried on both MySQL 5.6 and 5.7 extensively - both versions have produced both a expected and a not expected result. I've tried on MySQL 8.0 on db-fiddle.com and this was the only version that produced an expected result.

Is there a setting I'm missing / memory limit / known issue ?

Worth noting, that this query always works when the UNION ALL part of the query is removed, hence a dummy union all is in there so the issue is recreatable.

CodePudding user response：

SET SESSION group_concat_max_len = 1000000;

This will solve your problem. Default Group concat has a max length of 1024

Some more info: https://www.namasteui.com/mysql-group_concat-maximum-length/