MySQL GROUP_CONCAT chopping off half the data (sometimes)

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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.

>Solution :

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/