Why I get this strange result: [BLOB - ...]?

Question

This is my database schema:

Post:
id
title
body
date

Tag:
id
title

Post_Tag:
id
id_post
id_tag

Comment:
id
id_post
body
date

and this is my query:

SELECT
    Post.id AS post_id,
    Post.title AS post_title,
    Post.body AS post_body,
    GROUP_CONCAT(CONCAT(Tag.id, "|", Tag.title) SEPARATOR '#') AS tags,
    COUNT(Comment.id) AS comment_count
FROM Post
LEFT JOIN Comment ON Post.id = Comment.id_post
LEFT JOIN Post_Tag ON Post.id = Post_Tag.id_post
LEFT JOIN Tag ON Tag.id = Post_Tag.id_tag
GROUP BY Post.id
ORDER BY Post.date ASC

Someone can tell me why I am getting these strange results ( [BLOB - ... ] ) below in the tags column ?

id  title           body            tags            comment_count
1   hello guys  blablabla...    [BLOB - 8B]         8
2   hello all   blablabla...    [BLOB - 14B]        3
3   how to tell blablabla...    [BLOB - 8B]         5
4   hello world blablabla...    [BLOB - 5B]         7

http://dev.mysql.com/doc/refman/5.0/en/blob.html – RRikesh May 17 '13 at 09:13 — RRikesh, May 17 '13 at 09:13
ok but how can I get the correct results :) ? – user2393238 May 17 '13 at 09:15 — user2393238, May 17 '13 at 09:15

score 1 · Answer 1 · answered May 17 '13 at 09:16

This is a relatively well-known configuration issue: your group_concat_max_len is set to a large value, forcing MySql to use BLOBs instead of varchars for the results of group_concat.

To fix, set group_concat_max_len to, say, 512 in your my.ini or my.cnf file, and restart MySql.

Here is a link to a post with more information.

score 0 · Answer 2 · answered May 17 '13 at 09:16

0

Replace:

GROUP_CONCAT(CONCAT(Tag.id, "|", Tag.title) SEPARATOR '#') AS tags,

With:

CAST(GROUP_CONCAT(CONCAT(Tag.id, "|", Tag.title) SEPARATOR '#') AS CHAR) AS tags,

Documentation: CAST function

answered May 17 '13 at 09:16

Jocelyn

11,209
10
43
60

score 0 · Answer 3 · answered May 17 '13 at 10:31

0

You may also use CONVERT() and convert BLOB data to utf8

CONVERT(GROUP_CONCAT(CONCAT(Tag.id, "|", Tag.title) SEPARATOR '#') AS tags USING utf8),

answered May 17 '13 at 10:31

Harsh Gupta

4,348
2
25
30

Why I get this strange result: [BLOB - ...]?

3 Answers3