Why GROUP_CONCAT returns BLOB?

Last week, when using the GROUP_CONCAT() function on a MySQL database, I got an unexpected result. 🙁

Indeed, instead of getting my result as VARCHAR types, I got it as BLOB types! For information, a BLOB is a binary large object that can hold a variable amount of data:
http://dev.mysql.com/doc/refman/5.0/en/blob.html
Because BLOB values are treated as binary strings, it is not easy to use. This is why we would prefer to have VARCHAR values.

So the question is how to get around this frustrating problem?

The answer is, for once, very simple! 😀
You simply need to:

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

To verify if the value has been successfully updated, execute the following command in your mysql client:

mysql> show variables like "%concat%";
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| group_concat_max_len | 512   |
+----------------------+-------+
1 row in set (0.00 sec)

Note that you cannot set the value of group_concat_max_len to less than 1Kb using the MySQL Administrator GUI. Which means that the only way to set this system variable to 512 (which is less than 1Kb) is to edit your MySQL configuration file as described above.

blob, group_concat(), group_concat_max_len, MySQL, varchar

This entry was posted on 11 Feb 2010, 19:11 and is filed under MySQL, Tricks. You can follow any responses to this entry through RSS 2.0. You can skip to the end and leave a response. Pinging is currently not allowed.

#1 by Gerald Mengisen on 15 Sep 2010 - 20:34

The solution above doesn’t quite work for shared hosting. However, the solution in the link below worked for me (I’m just adding this here because your entry was one of the first ones in Google about this problem):
http://stackoverflow.com/questions/2133936/using-group-concat-in-phpmyadmin-will-show-the-result-as-blob-3b

Quote

#2 by smoreau on 15 Sep 2010 - 22:52

Thank you very much for sharing this link.
Indeed, it wouldn’t be possible to change the value of a system variable on a shared hosting.
It is actually a nice trick to convert the column value to a string ! 🙂

#3 by mart on 13 Apr 2011 - 11:25

Had the same problem, found answer
SELECT rec_id, GROUP_CONCAT(CAST(user_id AS CHAR))
FROM t1
GROUP BY rec_id

@ http://stackoverflow.com/questions/2133936/using-group-concat-in-phpmyadmin-will-show-the-result-as-blob-3b

#4 by RakonDark on 13 Jul 2012 - 17:46

Dear mart
I found a problem with your CAST on MySQL

when i have INT and i Gdo this
SELECT rec_id, GROUP_CONCAT(CAST(user_id AS CHAR))
FROM t1
GROUP BY rec_id

then i have always only 1 char space for the int value , this may work for 0-9 but not when your INT grow 🙂

for me it was fine to CAST as CHAR(7) to hold the
biggest value 9999999 value from my INT:)

SELECT rec_id, GROUP_CONCAT(CAST(user_id AS CHAR(7)))
FROM t1
GROUP BY rec_id

#5 by borate on 12 Dec 2012 - 03:48

thx you save my day

LogikDevelopment