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.