8

MySQL 5.5.28 on Ubuntu 12.04

If the result is longer than group_concat_max_len then the result is gracelessly truncated.

Currently I have a script that tries to check the required length ahead of time and sets group_concat_max_len to be large enough.

But the checking adds extra queries. Is there any downside to just setting group_concat_max_len to the maximum value? The upside is fewer queries.

  • The question seems to be "If I set it too high, will I run out of RAM and crash, or something?". Meanwhile, 1024 bytes is a trivial amount, and there seems to be no need to ever set it below that. – Rick James Feb 18 '18 at 18:17
  • @RickJames RAM, CPU, etc, yes. “Will the query take a long time or use up a large part of system resources?” – Buttle Butkus Feb 18 '18 at 20:26
  • 1
    But, if you say 1G, does it immediately use 1G, or does it start with a small value and work its way up if needed? – Rick James Feb 18 '18 at 20:53
  • 1
    I have a similar script. I wanted to account for 1M 32 character strings. I was at first suprised to see that they took up 329999999 bytes in the group_concat buffer. The moral of this story is don't forget to account for the commas. – MatrixManAtYrService Jul 13 '18 at 16:29
2

As per MySQL BOL Here

The maximum value for group_concat_max_len for 64-bit is 18446744073709551615

&

The maximum value for group_concat_max_len for 32-bit is 4294967295

The result is truncated to the maximum length that is given by the group_concat_max_len system variable, which has a default value of 1024. The value can be set higher, although the effective maximum length of the return value is constrained by the value of max_allowed_packet. The syntax to change the value of group_concat_max_len at runtime is as follows, where val is an unsigned integer:

SET [GLOBAL | SESSION] group_concat_max_len = val;

Note: The maximum permitted result length in bytes for the GROUP_CONCAT() function. The default is 1024.

As MySQL documented blog Here Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though).

As MySQL Blog by @Shlomi Noach Here group_concat_max_len: This parameter limits the maximum text length of a GROUP_CONCAT concatenation result. It defaults to 1024. I think this is a very low value. I have been using GROUP_CONCAT more and more, recently, to solve otherwise difficult problems. And in most cases, 1024 was just too low, resulting in silent (Argh!) truncating of the result, thus returning incorrect results. It is interesting to learn that the maximum value for this parameter is limited by max_packet_size. I would suggest, then, that this parameter should be altogether removed, and have the max_packet_size limitation as the only limitation. Otherwise, I'd like it to have a very large default value, in the order of a few MB.

For Further your ref Here & Here

  • My question is a bit more nuanced and granular. I’ve already read the manual so your copy-paste job doesn’t help me. Thanks for the effort, though. – Buttle Butkus Feb 13 '18 at 7:09
  • @Buttle Butkus,Each and every application they have some set of default environment variable parameter size, min and max.if you shall not meet there define threshold parameter limit, then that application behave abruptly way. – Md Haidar Ali Khan Feb 13 '18 at 7:14
  • Yes, I would like to know about performance impact of changing this setting. Would making it large impact ALL queries or just the ones where large concatenations occur? – Buttle Butkus Feb 13 '18 at 7:15
  • 1
    @ButtleButkus, you know that "SET GLOBAL" means the setting will affect all other MySQL connections on your server, and the setting will even persist after your current session is done, until you set it to something else? You might want to use "SET SESSION" if you only want the value to affect your current session. – Md Haidar Ali Khan Feb 13 '18 at 7:23
  • 1
    Thank you, but I already know those things and they have nothing to do with my question. – Buttle Butkus Feb 13 '18 at 7:35
1

I appreciate this question is a bit old now, but in case someone finds it and is wondering, one downside of setting the maximum (or an otherwise very large) value is that group_concat can return a blob rather than a varchar. Suggestions elsewhere say to set group_concat_max_len to 512 to make it always return a varchar rather than a blob. I tend to just cast it to char where necessary though.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.