GROUP_CONCAT() mysql returns limited characters

Question

I have to two tables

tblColorLibrary

id   name
1     test color

tblColors

id  libraryId  colorCode  name
1     1           #fff     Prime Color
2     1           #ddd     Secondry Color
3     1           #E2CFC7  Favorite Color

Below is my query:

$stmt ="SELECT a.id, a.isActive as isActive, a.name as title, GROUP_CONCAT(b.colorCode ) as colors, GROUP_CONCAT(b.name) as name FROM ".$this->tblLibrary." as a JOIN tblcolors as b ON a.id = b.libraryId GROUP BY a.id ORDER BY b.id ASC";

This query will return result like this

Array
(
    [0] => Array
        (
            [id] => 1
            [isActive] => Y
            [title] => test
            [colors] => #fff,#ddd,#E2CFC7
            [name] => Prime Color, Secondry Color, Favorite Color
        )

)

All goes fine till I have limited records. When I have above 150 records in tblColors, name key gives only limited number of characters. Not getting full records.

I guess there will be limitation in group concat.

`GROUP_CONCAT` has a max length of about 1024. You can change this through server variables. See here: http://stackoverflow.com/questions/2567000/mysql-and-group-concat-maximum-length — pala_, Apr 07 '15 at 08:23
Check `group_concat_max_len` https://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_group_concat_max_len — Abhik Chakraborty, Apr 07 '15 at 08:24
@pala_ yes you are right i checked strlen($name), it says 1024 — Anto S, Apr 07 '15 at 08:25

score 2 · Accepted Answer · answered Apr 07 '15 at 08:26

2

Increase your group_concat_max_len at mysql database. by default it set to 1024. you can update it by using query

SET GLOBAL group_concat_max_len=100000

and

SET SESSION group_concat_max_len = 1000000;

answered Apr 07 '15 at 08:26

Saty

22,443
7
33
51

After adding your answer it is working on my local server and not in live server, hope we will not have privilege to set this on live server, any idea about this? – Anto S Apr 07 '15 at 08:49
run this query $sql = "SET SESSION group_concat_max_len = 1000000;" before your mysql query – Saty Apr 07 '15 at 08:53
Yes I set so, and it started working on local wamp, but when I moved this to production server it is not working :-( – Anto S Apr 07 '15 at 08:54
I came to know that since it is shared server I can do this any other idea to modify the length other than this? – Anto S Apr 07 '15 at 09:08

score 1 · Answer 2 · answered Apr 07 '15 at 08:27

1

Check the value of group_concat_max_len and increase it as needed.

show variables like 'group_concat_max_len';

answered Apr 07 '15 at 08:27

Dobromir Velev

530
2
15

GROUP_CONCAT() mysql returns limited characters

2 Answers2