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 on-premise 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 on-premise 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.

Last Reviewed: April 2016