MySQL GROUP_CONCAT returns [BLOB – 14 B]

MySQL GROUP_CONCAT returns [BLOB – 14 B]

A tech post today, I really donโ€™t do enough of these. I bumped into an issue recently where I was building a form guide for the cricket league website that I run. The idea was to present the team form on one line based off results in my scorecard table, like so: WWWLLWWNWWLWW

I previously had a nice little PHP script which handled Wins versus Losses quite well but I needed to add Tie, Draw and No Result to the mix. I had a bit of a play with PHP but decided to have a crack in MySQL instead.

SELECT 
  SeasonName, 
  TeamID, 
  TeamName, 
  GROUP_CONCAT(Result SEPARATOR '') AS Form 
FROM ...

Which works great but the ordering wasnโ€™t as I wanted, for a proper form guide it needs to be in order of games played. Hence I added the ORDER BY:

SELECT 
  SeasonName, 
  TeamID, 
  TeamName,
  GROUP_CONCAT(Result ORDER BY game_date SEPARATOR '') AS Form
 FROM โ€ฆ

The problem is that in my environment the value being returned for the Form field was [BLOB – 14 B]. It turns out the result value depends on the system variable group_concat_max_len. The default values for this is 1024 (1kb) and mine was obviously coming out larger. I verified my group_concat_max_len was set to 1024 with the following query:

-- Check length 
SHOW VARIABLES LIKE 'group%';

If you want to change the the length of that system variable use can use the below query:

-- Update length
SET @@group_concat_max_len =524288;

-- Check length
SHOW VARIABLES LIKE 'group%';

If you have access to my.ini or my.cnf you can also do the following:

  • Open your my.ini or my.cnf file
  • Change the value of the group_concat_max_len system variable to 512 (no โ€˜kโ€™ suffix)
  • Restart the MySQL Service

I also found a solution that works, and is working well for me. I converted the GROUP CONCAT to UTF8 with the following SQL:

SELECT 
  SeasonName, 
  TeamID, 
  TeamName,
  CONVERT(
    GROUP_CONCAT(Result ORDER BY game_date SEPARATOR '') 
    USING utf8
  ) AS Form
FROM ...

Hopefully helps someone else having the same issue.

Mike250

Australian. Sport. Passionate Cricket Fan. Go Pro. Abseiling. Snorkeling. Travel. Golf R. SQL Server Developer. Three sons. One daughter. Last Trip: New York.

Related Posts
10 Comments
    • Troy R
    • On: May 27, 2010

    Keep up your tech posts, enjoy the read ๐Ÿ™‚

    Reply
  1. Haha, cheers Troy! This is probably all piddly stuff to you though!

    Reply
    • mansih
    • On: January 24, 2011

    Hello MIke,

    Its usfully page, but can you explain why required CONVERT in UTF 8.

    Reply
  2. My understanding is because my BLOB is stored as UTF 8.

    Reply
    • Chintan Patel
    • On: December 9, 2012

    Thank’s For this Solution…
    I have tryed google but fail….

    Reply
    • Janne
    • On: January 11, 2013

    Thanks for the CONVERT tip, helped me out!

    Reply
  3. Very glad folks!

    Reply
    • Guillermo Malagรณn
    • On: March 27, 2014

    Hey man, NICE!
    this works if you dont receive special chars, but what happens if i use “รฑ, ร ,รก”
    if you convert to utf8 the words get cut when they find those chars, what else can we use????

    Reply
    • Guillermo Malagรณn
    • On: March 27, 2014

    ok got it!!!! (latin1)

    SPANISH : USING latin1

    Reply
    • Nice Guillermo, thanks for your input!

      Reply
Leave a comment

Your email address will not be published. Required fields are marked *