29

I am using a group_concat to concatenate a lot of rows into one.

I set group concat to 10000 using:

SET group_concat_max_len = 10000;

But even then, my output cells remain incomplete and end with ...

I tried setting group_concat_max_len = 20000 and even that didn't help.

I also tried setting group_concat_max_len to 99999999. It still doesn't complete my output text. And I checked one of the group concat stops at Length = 230 characters and then gives ...

Is there any other way?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
user3422637
  • 3,967
  • 17
  • 49
  • 72
  • 1
    http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_group_concat_max_len 18446744073709551615 – Mihai Oct 24 '14 at 18:14
  • 1
    @Mihai, why not post that as answer? – Rahul Oct 24 '14 at 18:16
  • @Rahul I think because Bill Karwin posted it first ;) (+/- 60 seconds) – Barranka Oct 24 '14 at 18:18
  • 2
    @Rahul Eh,just a quick google search,I`m more interested in learning something rather than break the point bank. – Mihai Oct 24 '14 at 18:19
  • @Barranka, NO; Bill Karwin posted that much after Mihai's comment. – Rahul Oct 24 '14 at 18:19
  • 2
    USe `SET SESSION group_concat_max_len =..` before any query. – Mihai Oct 24 '14 at 18:20
  • 2
    Yeah, I posted my answer about 1 minute after Mihai commented. If I had wanted to post just a link with no descriptive text, I probably would have been simultaneous. I don't care that much about the points either, but if questions are answered in comments alone, they always remain in the "unanswered" queue. People who don't post real answers are breaking StackOverflow's intended usage. – Bill Karwin Oct 24 '14 at 18:22
  • @Mihai, May be .. it's your personal view but if you post those comment as answer then a future reader may get helped. I am just thinking that. – Rahul Oct 24 '14 at 18:22

2 Answers2

44

Check out this link: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_group_concat_max_len

All the MySQL configuration variables are documented on that page, with details like minimum, maximum, default value, whether you can set them globally or per-session, whether you can change them on a running instance or does it require a restart, and other description of usage.

The maximum value for group_concat_max_len is 18446744073709551615.

The group-concat string does not end with "..." If you try to group too much text, it just gets truncated. So I wonder if the problem is not with MySQL's settings, but with the display of your cells.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • I set it to max now (18446744073709551615) My string still gets cut off at the same place where it was earlier getting cut off. Hence, the change isn't getting effective clearly. I closed my MySQL workbench and restarted it. But I am probably required to restart the MySQL instance. – user3422637 Oct 24 '14 at 18:19
  • 1
    Like I said, this is probably a limitation of the display, not the MySQL server. – Bill Karwin Oct 24 '14 at 18:22
  • So, when you say "I wonder if the problem is not with MySQL's settings, but with the display of your cells." --> You mean that the display could be truncated on the client side (MySQL Workbench in this case) ? – user3422637 Oct 24 '14 at 19:44
  • 1
    Yes, that's exactly it. You can try a query like `select repeat('a', 1024*1024)` and see the "..." there too. It's an issue of MySQL Workbench showing only a limited prefix of the string, even though the server has sent back a long string. – Bill Karwin Oct 24 '14 at 20:33
  • Ok. I will try programatically running my query saving the results in a file and get back. – user3422637 Oct 24 '14 at 20:37
  • 2
    Yes. It worked programatically. So, the truncation was occuring on client side not on the server side. – user3422637 Oct 24 '14 at 21:13
  • 1
    if youre using workbench, you can right click the column and select `Open value in viewer' to see the whole string – Rafael Herscovici Feb 10 '15 at 13:32
  • @user3422637 How to change group_concat_max_len value? – SureshKumar Vegesna Jun 22 '16 at 10:23
  • I wonder if MySQL allocates that much bytes to that session upfront. How is memory managed for the parameter group_concat_max_len? – Abhijit Buchake Sep 14 '22 at 13:07
20

For 32bit systems, the maximum value is 4294967295

For 64 bit systems, the maximum value is 18446744073709551615.

You can set the variable for your current session using 

SET SESSION group_concat_max_len=4294967295;

To set the variable forever use

SET GLOBAL group_concat_max_len=4294967295;

(see http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_group_concat_max_len)

Justin Levene
  • 1,630
  • 19
  • 17