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.