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

Related
Recommended