MySQL

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.

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:

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:

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

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:

Hopefully helps someone else having the same issue.

Share on Google Plus

Mike250

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

10 Comments

    • Troy R
    • May 27, 2010
    • Reply

    Keep up your tech posts, enjoy the read 🙂

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

    • mansih
    • January 24, 2011
    • Reply

    Hello MIke,

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

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

    • Chintan Patel
    • December 9, 2012
    • Reply

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

  3. Thanks for the CONVERT tip, helped me out!

  4. Very glad folks!

    • Guillermo Malagón
    • March 27, 2014
    • Reply

    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????

    • Guillermo Malagón
    • March 27, 2014
    • Reply

    ok got it!!!! (latin1)

    SPANISH : USING latin1

    • Nice Guillermo, thanks for your input!

Leave a comment

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