KB-1115 GROUP_CONCAT function in MySQL truncates value

Symptoms

When a view is using GROUP_CONCAT function, the value is truncated in MySQL.

Cause

Mysql truncates to the maximum length that is given by the group_concat_max_len system variable, which is a default value of 1024 bytes.

For more information, refer to MySQL Documentation - GROUP_CONCAT(expr).

Action

To resolve this issue, use one of the following options:

• For only self-managed installations, change the value of group_concat_max_len at runtime by doing the following (val is an unsigned integer):

  SET [GLOBAL | SESSION] group_concat_max_len = val; 

• For both self-managed installations and cloud sites, modify the SQL for the view using a combination of CONCATand GROUP_CONCAT.

  Original Query:

  SELECT GROUP_CONCAT(last_name) FROM employee 

  Modified Query:

  SELECT   SUBSTRING(     CONCAT(       GROUP_CONCAT(         IF(employee_id BETWEEN 1 AND 100, CONCAT(',', last_name), '')         SEPARATOR ''       ),       GROUP_CONCAT(         IF(employee_id BETWEEN 101 AND 200, CONCAT(',', last_name), '')         SEPARATOR ''       )     ),     2   ) AS result FROM employee 

Affected Versions

This article applies to all versions of Appian.