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.

Listening to Paralyzer by Finger Eleven on Them vs. You vs. Me.

No related posts.