0

Is it possible to get mysql query to continue after group_concat_max_len exceeded?

Or, can group_concat function be somehow set to limit size? (Note: I am already using Distinct)

Thanks!

Another Note: This is not a duplicate of all the other questions about how to change the group_concat_max_len to the needed max. I want the max len to apply and truncate the result, while still allowing the query to complete.

Ah hah! See: Continue SQL query even on errors in MySQL workbench

Community
  • 1
  • 1
Fred
  • 47
  • 6
  • 1
    Why do you think the query won't complete? It will just truncate to the maximum length. – Barmar Sep 26 '16 at 21:05
  • It stops with an error message. Perhaps because I am launching the query via MySQL Workbench? – Fred Sep 26 '16 at 21:29
  • It must be. I don't get an error when I try it from the `mysql>` CLI tool. What is the error message? – Barmar Sep 26 '16 at 21:35
  • Error Code: 1260. Row 1554 was cut by GROUP_CONCAT() – Fred Sep 26 '16 at 21:41
  • That's a warning, not an error. – Barmar Sep 26 '16 at 21:47
  • Yeah, you would think so, but the query terminates, and no records inserted to that point are kept. – Fred Sep 26 '16 at 21:50
  • I can't reproduce it. I just tried `insert into results (c) select group_concat(col) from (select col from sometable limit 10) x`. I got the warning, but it inserted the row into the `results` table. – Barmar Sep 26 '16 at 21:56
  • OK, well thank you for trying. Perhaps it is Workbench. I will try the CLI after I poke around and see if there is some setting in Workbench that may be causing this difference. – Fred Sep 26 '16 at 22:00
  • MySQL *silently* truncates the value returned by `GROUP_CONCAT` to `group_concat_max_len`. It is easy to demonstrate that MySQL server doesn't throw an error or a warning. If you are getting an error message, it must be from MySQL Workbench. I suspect that you could avoid the error from MySQL Workbench by wrapping the `GROUP_CONCAT` in a `SUBSTR` or `LEFT` function, e.g. **`LEFT(GROUP_CONCAT(foo),512)`** to avoid workbench griping. **N.B.** `group_concat_max_len` is a limit on the number of *bytes* not on *characters*. With MBCS, there's potential to return "partial" characters. – spencer7593 Sep 26 '16 at 22:00
  • I'd bet that Workbench is checking for truncation the same way I check... comparing the byte length of the returned value against `group_concat_max_len`. If the length of the returned string is *less than* the limit, no truncation has occurred. If it's equal, then truncation has occurred. (The only corner case is when the length of the value returned by GROUP_CONCAT is exactly at the limit, and the value wasn't truncated. But we can't tell if it was or not, so we have to assume that truncation has occurred.) – spencer7593 Sep 26 '16 at 22:07

0 Answers0